locked
Convert Spatial Data from Geography Data Type to Geometry Data Type RRS feed

  • Question

  • How can I convert existing GIS data that is using the SQL Geography data type to SQL's geometry data type?

     

    Thanks in advance.

    Monday, May 9, 2011 5:42 PM

Answers

  • It doesn't generally make sense to convert from one type to another:- if you've got planar data, use the geometry datatype. If you've got geodetic data, use the geography datatype. Storing latitude/longitude data using the geometry datatype is akin to storing integer values in a varchar column as '1', '2', '3' etc. - you can do it, but the results you get from any calculations won't make much sense.

    Still, if you want to go ahead, try this:

    SELECT geometry::STGeomFromWKB(@geog.STAsBinary(), @geog.STSrid)

    where @geog is your geography data.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Proposed as answer by Peja Tao Wednesday, May 11, 2011 7:17 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 17, 2011 9:36 AM
    Monday, May 9, 2011 7:13 PM
    Answerer
  • You can use geometry::STGeomFromWKB(g.STAsBinary(),g.STSrid) but there are a few things to be cautious of.

    You might need to "densify" the geography if the vertices are far enough apart that there might be some "great circle" considerations.

    • Proposed as answer by Peja Tao Wednesday, May 11, 2011 7:17 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 17, 2011 9:36 AM
    Monday, May 9, 2011 7:42 PM

All replies

  • It doesn't generally make sense to convert from one type to another:- if you've got planar data, use the geometry datatype. If you've got geodetic data, use the geography datatype. Storing latitude/longitude data using the geometry datatype is akin to storing integer values in a varchar column as '1', '2', '3' etc. - you can do it, but the results you get from any calculations won't make much sense.

    Still, if you want to go ahead, try this:

    SELECT geometry::STGeomFromWKB(@geog.STAsBinary(), @geog.STSrid)

    where @geog is your geography data.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Proposed as answer by Peja Tao Wednesday, May 11, 2011 7:17 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 17, 2011 9:36 AM
    Monday, May 9, 2011 7:13 PM
    Answerer
  • You can use geometry::STGeomFromWKB(g.STAsBinary(),g.STSrid) but there are a few things to be cautious of.

    You might need to "densify" the geography if the vertices are far enough apart that there might be some "great circle" considerations.

    • Proposed as answer by Peja Tao Wednesday, May 11, 2011 7:17 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 17, 2011 9:36 AM
    Monday, May 9, 2011 7:42 PM