Reproject Spatial Data – How to Reproject Spatial Data Using SQL Server

coordinate systemsql serversrid

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?

Best Answer

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

Related Solutions

SQL Server Spatial Types – Using SQL Server’s Spatial Types in a .Net Application

Is this a desktop application or, say a Silverlight application? If it is web-based you have to jump through some hoops. You could create a view that exposes WKT and then parse the WKT client side into WPF / Silverlight geometries.

If it is a desktop application you've got it quite a bit easier. There is a good example at the Code Project of an SQL Geometry viewer that will help for both desktop or web.

You need to reference Microsoft.SqlServer.Types.dll, found at SQL Server Install/100/SDK/Assemblies to use SQLGeometry or SQLGeography directly.

Technologies like RIA don't understand these types but there is a work-around. Essentially you create a view that casts the geometry as a varbinary(max), use it in your OR mapper, RIA, LINQ, etc. and then convert it back on the client. There is a good tutorial here

Very late edit: Silverlight won't accept them either because the previously mentioned DLL's aren't compiled against Silverlight. Getting Silverlight compatible versions is high on many peoples wish list!

[GIS] Transform Data with Microsoft Spatial SQL

Here's some sample Sql that does projection if you have the Sql Server Tools installed:

-- Project point and linestring using Albers Equal Area projection
declare @albers Projection
set @albers = Projection::AlbersEqualArea(0, 0, 0, 60)

select @albers.Project('POINT (45 30)').ToString()
select @albers.Unproject(@albers.Project('LINESTRING (10 0, 10 10)')).ToString()
select @albers.ToString()

Best Answer

Related Solutions

SQL Server Spatial Types – Using SQL Server’s Spatial Types in a .Net Application

[GIS] Transform Data with Microsoft Spatial SQL

Related Question