26

SQL Server takes an SRID when creating spatial data, but is it possible to retrieve with a different SRID translating the coordinates?

For example, let's say I have a bunch of spatial polygons using SRID 4258, but I'd like to use alongside some pre-existing data that has an SRID of 4326 -- are there built in conversions, or do I have to handle this conversion myself?

The SQL-MM method, which PostGIS implements is ST_Transform. How do I do that in SQL Server?

1

4 Answers 4

20

No.

Transform - ability to transform from one spatial ref to another: No - need 3rd-party tools, Geometry can use any SRID between 0 and 999999. Spatial Tools free CLR add-on does provide limited transform support.

Source: http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare

10

Not by default, but check the SQL Server Spatial Tools developed by MSDN on GitHub. Specifically, the affine transformation functions.

1
  • +1 I find these tools to be an essential part of Sql Server. Oct 20, 2010 at 13:23
7

Example for reprojection from EPSG:2193 to EPSG:3857

c:\OSGeo4W64\bin\ogr2ogr.exe ^
 -f "MSSQLSpatial"^
 "MSSQL:server=DestServerName;database=DestDbName;trusted_connection=yes"^
 "MSSQL:server=SourceServerName;database=SourceDbName;trusted_connection=yes"^
 -sql "SELECT [Id], [Shape].STAsText() Shape FROM [SourceDbName].[dbo].[SourceTableName]"^
 -nln "DestTableName"^
 -overwrite^
 -s_srs EPSG:2193^
 -t_srs EPSG:3857

After this execute SQL Query

update  [DestDbName].[dbo].[DestTableName]
set     [ogr_geometry] =  geometry::STGeomFromText([shape], 3857)
2

I had a similar problem and solved in by .Net. I developed a dll in C# and then defined it as a CLR function in MSSQL. Whenever I call this function, this does the conversion for me. Please see this approach at Stack Overflow. , and this https://stackoverflow.com/questions/52618277/how-do-spatial-reprojection-in-sql-server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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