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?

Evan Carroll
  • 7,071
  • 2
  • 32
  • 58
Rowland Shaw
  • 798
  • 2
  • 9
  • 19
  • Please have a look at my answer here: https://stackoverflow.com/questions/52618277/how-do-spatial-reprojection-in-mssql/52618526#52618526 – Mohsen Sichani Oct 03 '18 at 01:35

4 Answers4

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

underdark
  • 84,148
  • 21
  • 231
  • 413
10

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

Mike T
  • 42,095
  • 10
  • 126
  • 187
user890
  • 5,733
  • 1
  • 42
  • 71
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)
Maxim Mikhisor
  • 171
  • 1
  • 1
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

Mohsen Sichani
  • 321
  • 3
  • 18