3

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

My end goal is to get this dataset into MS SQL Server, with the Geography Markup Language (GML) elements are stored in MS SQL Server's geography data type. After this, I want to make a connection to the MS SQL Server from QGIS, so I can do some nice data analyses.

My idea was to parse the GML elements (that are contained in an XML) using the GEOMFROMGML parser from MS SQL Server. I tried:

SELECT geography::GeomFromGml (
'<Polygon xmlns="http://www.opengis.net/gml">
<exterior>
<LinearRing>
<posList>78988.952 384548.144 78992.462 384549.74 78998.712 384555.12</posList>
</LinearRing>
</exterior>
</Polygon>'
, 28992 )

However, the above code gives the following error: "The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view."

So, I tried to add the SRID to sys.spatial_reference_systems catalog view, using:

INSERT INTO [DataAnalyse].[sys].[spatial_reference_systems] ([spatial_reference_id]
      ,[authority_name]
      ,[authorized_spatial_reference_id]
      ,[well_known_text]
      ,[unit_of_measure]
      ,[unit_conversion_factor])
VALUES(
    28992,
    'EPSG',
    28992,
    'GEOGCS["Amersfoort", DATUM["Amersfoort", SPHEROID["Bessel 1841", 6377397.155,299.1528128]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]',
    'metre',
    1
)

This gave another error: "Msg 259, Level 16, State 1, Line 13 Ad hoc updates to system catalogs are not allowed."

So, I looked how I could update a system catalog. It was explained on DBA how to do this. But it was stated there that there is a severe risk of breaking your MS SQL Server installation, and that warranty would cease. So, it seems that updating the system catalog directly is not really an option.

I first asked this question on Microsoft Docs but I was told to ask my question here.

Moreover, people seem to have asked similar questions:

If you have an answer to my question, or have a different idea to get me to my end goal (stated above) please let me know.

1
  • 1
    sys.spatial_reference_systems is just a list of SRIDs supported by SQL Server – NikitaSerbskiy May 9 '20 at 15:51
1

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

As you've mentioned, there are ways to modify system catalogs manually but in general this is not supported or recommended. As such, I consider this a non-option.

The documentation specifies:

SQL Server supports SRIDs based on the EPSG standards. A SQL Server-supported SRID for geography instances must be used when performing calculations or using methods with geography spatial data. The SRID must match one of the SRIDs displayed in the sys.spatial_reference_systems catalog view.

Emphasis mine.

So it seems to be clear that SQL Server does not support this particular SRID (as of SQL Server 2019 - db<>fiddle).


That being said, you should

  1. Create a feedback item for the MSSQL team about adding this functionality.
  2. Look into PostGIS which is widely considered the best option for handling GIS data in a relational database (and free, to boot).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.