Transform Data with Microsoft Spatial SQL

Question

Is there a way to transform geographic data in SQL Server 2008 R2?

Specifically, I want to take Lat/Long data and convert it to a State Plane coordinate system.

Answer 1

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()

Answer 2

Have used this method

SELECT * FROM sys.spatial_reference_systems WHERE spatial_reference_id IN(4269,4326);

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

Answer 3

Have you considered SQLCLR? SQLCLR allows managed code to be hosted by, and run in the Microsoft SQL Server environment. In simplest terms, you can create your own user defined function, stored procedure...etc in .NET, register it with the database, and call like any other SQL function.

http://msdn.microsoft.com/en-us/library/w2kae45k%28v=vs.80%29.aspx