locked
Converting XY to LongLat

    Question

  • Hi:

    Does anyone know how to convert XY values from shape data to long lats?

    We have pulled some .shp files (and loaded them into SQL08) that store the geo information in XY format, and we need to convert to long lat to draw polygons on a given layer.

    When running a given query against the afore mentioned geo data in Sql Management Studio,  the "Spatial Results" tab shows the shapes rendered correctly,  so somewhere this XY data is getting translated and processed correctly.  

    However,  every method I've tried both in C# on the server side and Javascript on the client side has not produced correct results.  When running any SQL Geo method dealing with WKB formats,  Sql complains that the binary geo data is "not in WKB format."  WKB format returns goofy long/lat pairs...

    Are we looking at non-standard data possibly?  If so,  it comes directly from the State of Michigan...

    Can this be done on the fly, or do we need to convert the data from the shape file and bulk load time?

    I can post a shape file it helps...

    Thanks

    PS



    PCS
    • Edited by Seevo Tuesday, March 03, 2009 2:00 PM
    Monday, March 02, 2009 8:10 PM

Answers

  • What you are describing is the process of 'projection' (or, specifically, 'reprojection').

    Latitude and Longitude are angular coordinates, measured from the centre of the Earth. Angular coordinates are very useful for describing positions on a sphere (or near-sphere), like on the Earth's surface. However, in general we want maps to be flat. Projection is the process of creating a flat map from a curved model of the Earth's surface. This means that, once projected, you can refer to positions using Cartesian X, Y coordinates rather than Latitude and Longitude.

    Now, you cannot perfectly flatten a curved surface onto a flat plane without some form of distortion. There are thousands of different sorts of projection, each designed to minimise distortion for a different purpose. If you have data expressed using X and Y coordinates obtained from the State of Michigan center for geographic information (http://www.michigan.gov/cgi/0,1607,7-158-12693---,00.html) then I'm guessing it's been projected using the Michigan State Plane Coordinate System. This projection, unsurprisingly, is designed to display features in the state of Michigan with the minimal amount of distortion. To get it back to latitude/longitude, you need to unproject it.

    The bad news? Neither Virtual Earth nor SQL Server 2008 provide the ability to reproject spatial data. Your best bet is to use a dedicated tool such as Safe FME (www.safe.com) to do it.

    To answer the second(?) question, the SQL native geometry and geography datatypes are NOT WKB - they are a custom binary format that is similar to, but different from WKB. If you want to obtain the WKB representation of a geometry, you need to use the STAsBinary() method. (I assume that it is the geometry type you are using, not the geography datatype...)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, March 02, 2009 9:37 PM
    Moderator

All replies

  • What you are describing is the process of 'projection' (or, specifically, 'reprojection').

    Latitude and Longitude are angular coordinates, measured from the centre of the Earth. Angular coordinates are very useful for describing positions on a sphere (or near-sphere), like on the Earth's surface. However, in general we want maps to be flat. Projection is the process of creating a flat map from a curved model of the Earth's surface. This means that, once projected, you can refer to positions using Cartesian X, Y coordinates rather than Latitude and Longitude.

    Now, you cannot perfectly flatten a curved surface onto a flat plane without some form of distortion. There are thousands of different sorts of projection, each designed to minimise distortion for a different purpose. If you have data expressed using X and Y coordinates obtained from the State of Michigan center for geographic information (http://www.michigan.gov/cgi/0,1607,7-158-12693---,00.html) then I'm guessing it's been projected using the Michigan State Plane Coordinate System. This projection, unsurprisingly, is designed to display features in the state of Michigan with the minimal amount of distortion. To get it back to latitude/longitude, you need to unproject it.

    The bad news? Neither Virtual Earth nor SQL Server 2008 provide the ability to reproject spatial data. Your best bet is to use a dedicated tool such as Safe FME (www.safe.com) to do it.

    To answer the second(?) question, the SQL native geometry and geography datatypes are NOT WKB - they are a custom binary format that is similar to, but different from WKB. If you want to obtain the WKB representation of a geometry, you need to use the STAsBinary() method. (I assume that it is the geometry type you are using, not the geography datatype...)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, March 02, 2009 9:37 PM
    Moderator
  • We may be able to help confirm what the projection of the data is if your data came with a .PRJ file.  If it did - open the file in Notepad and post the text here.

    If the data's projected and if you've loaded the data into SQL 2008 using the Geography data type then you've got a problem as you should only load Lat/Long data that way.

    If you've loaded the data in using the Geometry data type then I can only guess you may have a problem because you didn't input a coordinate system number (SRID) when you loaded it?  Although, SQL 2008 doesn't really care what projection the data is in using the Geometry data type.  The other alternative is that the data has invalid geometries.

    Re: converting the data to Lat/Long.  You'll need a GIS tool to do that.  You could try an eval of FME (http://downloads.safe.com/fme_eval/fme_professional.exe).  You'll need to register it.

    Cheers,
    Hugh


    Monday, March 02, 2009 9:37 PM
  • Forgot to say - your shapefile should come with an associated .prj file that will describe the properties of the projection from which the data was obtained. It will contain parameters such as the name of the ellipsoid, which prime meridian to use, the radius of the earth, and any false easting or false northing applied.

    If your data is based on the central Michigan state plane coordinate system, it will look a bit like this:
    PROJCS[ 
      "NAD83 / Michigan Central", 
      GEOGCS["GCS_North_American_1983", 
      DATUM["D_North_American_1983", SPHEROID["GRS_1980",6378137,298.257222101]], 
      PRIMEM["Greenwich",0], 
      UNIT["Degree",0.017453292519943295]], 
      PROJECTION["Lambert_Conformal_Conic"], 
      PARAMETER["standard_parallel_1",45.7], 
      PARAMETER["standard_parallel_2",44.18333333333333], 
      PARAMETER["latitude_of_origin",43.31666666666667], 
      PARAMETER["central_meridian",-84.36666666666666], 
      PARAMETER["false_easting",6000000], 
      PARAMETER["false_northing",0], 
      UNIT["Meter",1] 


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, March 02, 2009 9:43 PM
    Moderator