locked
Projecting Point with sql server spatial tools RRS feed

  • Question

  • I'm trying to project a geographic point to Albers using the new sql spatial tools. The result is not what I expected. Can someone look at my code and help me out?
    --sql code

    --Declare @Pnt geometry = geometry:Stick out tongueoint( 193132.393610252,-2740515.20026403, 9822);

    Declare @Pnt geography = geography::Point(30.017241 ,-95.389108, 4326);

     --Reproject

    declare @albers Projection

    set @albers = Projection::AlbersEqualArea(-96, 37.5, 29.5, 45.5)

    select @albers.ToString();

    Declare @ProjPnt geometry = @albers.Project(@Pnt);

    Select @ProjPnt.ToString();


    --Result
    POINT (0.0092216785304098364 -0.131441507182944)

    I'm looking for this point:
    --geometry:Stick out tongueoint( 193132.393610252,-2740515.20026403)

    Thanks,
    Dion

    Monday, September 8, 2008 2:55 PM

Answers

  • Hi Folks,

    I think there are two differences in expectation that are manifest here.  First, the code in the SQL Spatial Tools project projects the unit sphere, and so it must be scaled to match the size of the sphere you're interested in projecting.  We can get much closer to the expected result by multiplying by the size of the ellipsoid in the correct unit of measure:

    X computed = 0.0092216785304098364
    Y computed = -0.131441507182944

    X scaled = X computed * 6378137 meters *
    3.280833 (us survey feet / meter) = 192969.17790
    Y scaled = Y computed * 6378137 meters * 3.280833 (us survey feet / meter) = -2750492.71134

    This compares favorably with the desired result:

    X expected = 193132.393610252
    Y expected = -2740515.20026403

    I suspect the remaining difference is, as Goldie suggests over on the SQL Spatial Tools discussion, that the code is using a spherical Albers, not an ellipsoidal one.  (Note that I'm just using the semi-major axis in the conversion above---this ignores the flattening of the globe.)

    In terms of this project, I'm not sure yet how much effort we (at Microsoft) are going to put into this.  Adding a scaling parameter would be easy; implementing the ellipsoidal Albers probably less so.  I will [ahem] note that an interested party could join and augment the project.  Wink

    There have been a few suggestions on how to do this, including various GIS providers and Safe's FME.  All of these are good.  Another option may be to take a look at the Proj.NET project, also on Codeplex.  I suspect Morten's crew are leagues ahead of SQL Spatial Tools in this arena.

    Cheers,
    -Isaac
    Sunday, September 14, 2008 5:11 PM

All replies

  • I'm guessing that the Projection Stuff is part of the SQL Spatial bits from codeplex? If so, the usual best way to get help with that is using their discussion forum. But I see nobody has answered your question there either. I'll poke some folks to see if I can't get you an answer.

     

    Monday, September 8, 2008 3:32 PM
    Answerer
  • I wasn't getting any response from the CodePlex forum so that's why I posted here. I was hoping maybe someone else may have tried the code.

    Thanks for the help.

    Dion

     

    Monday, September 8, 2008 5:57 PM
  • I have to be honest and admit I've never used SQLSpatialTools either, so what follows in this response is completely unfounded guesswork, but here goes anyway!

     

    To start with, let's be clear about exactly what you're expecting....

     - You create a point at latitude 30.017241, longitude -95.389108, using SRID 4326.

     - You are expecting the results to be projected as a point with an Easting co-ordinate of 193132.393610252, and Northing of -2740515.20026403 (or is that the other way round?) using the Albers Equal Area projection, but in what spatial reference system? what makes you expect those particular answers?

     

    Your original co-ordinates are supplied in angular degrees of latitude and longitude (as specified by SRID 4326), but it looks like the values you are expecting are probably in metres, but you haven't specified this anywhere... I'm guessing that whatever the Project() method does, it does not actually convert co-ordinates between different spatial reference systems, but simply unprojects/reprojects existing planar data between different projections. In this case, since you are supplying geodetic co-ordinates measured in degrees then you'll have to include a multiplication factor to account for the radius of the earth get the answer in metres.

     

    Something like this gets close to the results you were looking for:

     

    Code Snippet

    -- Project point and linestring using Albers Equal Area projection
    Declare @Pnt geography = geography::Point(30.017241 ,-95.389108, 4326);

     

     --Reproject
    declare @albers Projection

    set @albers = Projection::AlbersEqualArea(-96, 37.5, 29.5, 45.5)

    select @albers.ToString();

     

    Declare @ProjPnt geometry = @albers.Project(@Pnt);

    Select
    @ProjPnt.ToString(),
    @ProjPnt.STX * 20943301,
    @ProjPnt.STY * 20943301

     

     

    Like I said though, this is complete guesswork, so don't take my word for it!

    Monday, September 8, 2008 8:22 PM
    Answerer
  • I'm looking for a result in Albers projection in feet with a geographic coordinate system of NAD 83.

    I was looking through the code and saw a mention of radians and was wondering if I had to convert the Lat/Longs into that before passing them into the method.

     

    Thanks,

    Dion

    Monday, September 8, 2008 10:47 PM
  • Hmmm... it sounds to me like you're going to need more than can be achieved using a Project() method that only accepts 4 parameters (which I'm guessing are the standard parallels and map center) - you're also looking for a complete co-ordinate transformation from a GeoCS to a ProjCS.

    Have you tried FME from Safe software? (http://www.safe.com)
    It's a pretty popular bit of conversion software with many users on the forums and might be better suited to this kind of operation.

    Tuesday, September 9, 2008 6:51 AM
    Answerer
  • I currently use ESRI and other open source gis software for projection conversions. I am currrently testing what the new SQL Server Spatial extensions and the "free" spatial tools from CodePlex can do. I'd like to replace functionality imbedded in our desktop geoprocessing apps with those within sql server for live/near-live geoprocessing for reporting and visualization on the web using Virtual Earth. Other functionality like intersection, linear-referencing and point/distance calculations are on my list to test, as well, as coordinate transformations.

     

    It seems like I need to apply calculations for the NAD83 Datum using the GRS80 spheroid.

    Anyone know how to do this?

     

    Thanks,

    Dion

    Tuesday, September 9, 2008 9:19 PM
  • Hi Folks,

    I think there are two differences in expectation that are manifest here.  First, the code in the SQL Spatial Tools project projects the unit sphere, and so it must be scaled to match the size of the sphere you're interested in projecting.  We can get much closer to the expected result by multiplying by the size of the ellipsoid in the correct unit of measure:

    X computed = 0.0092216785304098364
    Y computed = -0.131441507182944

    X scaled = X computed * 6378137 meters *
    3.280833 (us survey feet / meter) = 192969.17790
    Y scaled = Y computed * 6378137 meters * 3.280833 (us survey feet / meter) = -2750492.71134

    This compares favorably with the desired result:

    X expected = 193132.393610252
    Y expected = -2740515.20026403

    I suspect the remaining difference is, as Goldie suggests over on the SQL Spatial Tools discussion, that the code is using a spherical Albers, not an ellipsoidal one.  (Note that I'm just using the semi-major axis in the conversion above---this ignores the flattening of the globe.)

    In terms of this project, I'm not sure yet how much effort we (at Microsoft) are going to put into this.  Adding a scaling parameter would be easy; implementing the ellipsoidal Albers probably less so.  I will [ahem] note that an interested party could join and augment the project.  Wink

    There have been a few suggestions on how to do this, including various GIS providers and Safe's FME.  All of these are good.  Another option may be to take a look at the Proj.NET project, also on Codeplex.  I suspect Morten's crew are leagues ahead of SQL Spatial Tools in this arena.

    Cheers,
    -Isaac
    Sunday, September 14, 2008 5:11 PM
  • Sorry for the late reply. I was dealing with Hurricane Ike over the last week.

     

    After reading the reply from Goldie on the SQL Spatial Tools discussion, I now understand that the code is using the spherical Albers formula, not an ellipsoidal. I was looking for ellipsoidal.

     

    I am currently using the Proj.Net code over a webservice through SQL Server to project the data. Since I am evaluating SQL Server 2008 to alleviate bottlenecks in our geoprocessing procedures, I was hoping to remove the webservice and run the projection code directly in SQL Server.

     

    I understand the CodePlex code bits are "Free Extras" from Microsoft and that one day they may be embedded in the core. Right now it is a "nice to have" but not a neccessity. There are other alternatives on the market, both open source and commercial for coordinate projections. Now there's another option, Mentor Software's CS-Map projection engine just went open source. I used their stuff in the lat 90's.

     

    So far though, the intersection functionality should move some of my desktop geoprocessing to the server, as well as, the ability to retrieve WKT and view geometry directly into Virtual Earth. The sample app I created is working great!

     

    I appreciate all the help.

    Thanks,

    Dion

    Monday, September 22, 2008 4:01 PM