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.

Do you have the Sql Server Spatial Tools installed? – Kirk Kuykendall May 19 '11 at 15:53 — Kirk Kuykendall, May 19 '11 at 15:53

score 3 · Answer 1 · answered May 19 '11 at 15:57

3

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

answered May 19 '11 at 15:57

Kirk Kuykendall

25,787
8
65
153

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

score 1 · Answer 2 · answered May 20 '11 at 02:30

1

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

answered May 20 '11 at 02:30

user890

5,733
1
42
71

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

score 1 · Answer 3 · answered May 19 '11 at 21:39

1

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

answered May 19 '11 at 21:39

Mapperz

49,701
9
73
132

Transform Data with Microsoft Spatial SQL

3 Answers3

Linked