locked
Geography Data - How to add a new SRID RRS feed

  • Question

  • Hi all Geo Experts,

    I am a newbie to SQL Server spatial data processing and I have 2 side by side questions:

    I would like to add a new SRID/EPSG definition to SQL Server. I have the EPSG definition which look like this:

    PROJCS["DHDN / Gauss-Kruger zone 3", GEOGCS["DHDN", DATUM["Deutsches_Hauptdreiecksnetz", SPHEROID["Bessel 1841",6377397.155,299.1528128, AUTHORITY["EPSG","7004"]], AUTHORITY["EPSG","6314"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4314"]], UNIT["metre",1, AUTHORITY["EPSG","9001"]], PROJECTION["Transverse_Mercator"], PARAMETER["latitude_of_origin",0], PARAMETER["central_meridian",9], PARAMETER["scale_factor",1], PARAMETER["false_easting",3500000], PARAMETER["false_northing",0], AUTHORITY["EPSG","31467"], AXIS["Y",EAST], AXIS["X",NORTH]]

    My questions:

    1. Is it possible to add a EPSG definition as a new SRID to SQL Server?

    2. Is there a function which I could use to convert geographical objects from one SRID to another?

    TIA so much.


    Regards Jörg



    • Moved by Olaf HelperMVP Wednesday, October 19, 2016 1:55 PM Moved from "Database Engine" to a more related forum for a better response
    • Edited by Jörg Debus Saturday, November 26, 2016 1:46 PM
    Wednesday, October 19, 2016 1:42 PM

Answers

All replies

  • Hello Jörg,

    in SQL Server spatial data is implemented as a CLR assembly, therefore you can not add customer defined SRID.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 19, 2016 4:36 PM
  • SQL Server doesn't have a built-in table of SRIDs in the base product because it doesn't support reprojection on-the-fly (no built-in function). There is no way to add an SRID to sys.spatial_reference_systems used by geography type, geometry type can use any SRID (including 0). Because the geometry type uses Euclidian geometry, the only limitation is that spatial methods like STIntersects return NULL if the SRIDs don't match.  Some of the vendors may have use their own table of SRIDs to use in their product.

    You can do reprojection at load time using a commercial product like SAFE or ESRI, or the open-source utilities like ogr2ogr.


    Wednesday, October 19, 2016 6:26 PM
  • Thanks. Good Point.

    I looked for something like a SRID definition table in SQL Server. I have found sys.spatial_reference_systems. There is a column which resembles closely to what I have posted. It starts with some kind of command named GEOGCS and has same paramters but less than the ESPG Original.

    This is the entry for SRID 4236. There are many of these SRID definitions in that table.

    4236;EPSG;4236;"GEOGCS[""Hu Tzu Shan"", DATUM[""Hu Tzu Shan"", ELLIPSOID[""International 1924"",
    6378388, 297]], PRIMEM[""Greenwich"", 0], UNIT[""Degree"", 0.0174532925199433]]";metre;1

    Because I'm using SQL 2014, it is possible that MS has changed from some kind of hard-coded reference system to a more flexible one? BTW: There are many websites with koordinate-conversions. But they all process only one corrdinate per ENTER.

    I'm a newbie with ESRI ARCGIS software and can just open ArcMap :-). I have an EXCEL Workbook with GEOspatial coordinates (Gauss Krüger) and some other data that I want to store in a database and display display it on a map. Must be an easy task for experts.

    TIA!


    Regards Jörg

    Friday, October 21, 2016 3:14 PM
  • AFAIK, they didn't add on-the-fly reprojections (SRID conversions) or support for Gauss-Kruger in SQL Server 2016. You should be able to do this within the ARCGIS product and Gauss-Kruger is supported directly within ARCGIS (http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Gauss-Kruger.)

    Friday, October 21, 2016 4:46 PM
  • Thanks Bob,

    I'll try to add the EPSG definition above to the SRID definition table in SQL Server. I'll report the results here. Because all the other SRIDs all have different reference systems SQL Server obviously can handle these.

    Until now, it seems that there is no possibility to convert projections. Simply because there is no method to handle that. I have no idea why MS does not supply these functionality. The math for it seems to be quite simple because all parameters should be defined in the EPSG specs.

    BTW: Do you know a source where I could get an EPSG based formula for reprojection?

    Thanks again for your assistance and your patience.


    Regards Jörg

    Monday, October 24, 2016 7:30 AM