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.


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()
  • That definitely looks helpful but it doesn't look like the supported projections listed will provide similar results to the state plane system. – Britt Wescott May 19 '11 at 17:23
  • What's the SRID of the state plane system you're using? – Kirk Kuykendall May 19 '11 at 17:44
  • The SRID is 2228 – Britt Wescott May 19 '11 at 18:08
  • 1
    I've never used it, but the readme with the sql tools says it supports lambert, and this page indicates 2228 is based on lambert, and lists the parameters. So I think you'd be able to do a lambert projection and plug in those parameters. – Kirk Kuykendall May 19 '11 at 18:45

Have used this method

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



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.


  • Essentially that is what the sql spatial tools are. – Kirk Kuykendall May 20 '11 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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