Answered by:
Convert Spatial Data from Geography Data Type to Geometry Data Type
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 PMAnswerer 
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 PMAnswerer 
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