How can I convert Geometry data into Geography data in MS SQL Server 2008?

Question

How can I convert some Geometry data into Geography data in MS SQL Server 2008?

Answer 1

You can convert Geometry to Geography by first getting the Well-Known Text (WKT) of your geometry data and then using that text to create a new Geography object with a geographic SRID (like 4326).

Your Geometry data lives on a flat plane (like a paper map), while Geography data lives on a globe. The key is to take the coordinates from your flat map and "re-plot" them onto the globe model.

The Magic Spell (The T-SQL) 🪄

You'll use the .STAsText() method to get your Geometry's text string, and then feed it into the geography::STGeomFromText() method.

The most important part is specifying the SRID (Spatial Reference ID). For geography, you almost always want 4326, which is the WGS 84 standard (the one GPS uses!).

UPDATE MySpots

SET GeogColumn = geography::STGeomFromText(GeomColumn.STAsText(), 4326)

WHERE GeogColumn IS NULL; -- Only update new ones

If you want to see the conversion in a query:

SELECT

GeomColumn, -- Your original "flat" data

geography::STGeomFromText(GeomColumn.STAsText(), 4326) AS MyNewGlobeData

FROM

MySpots;

So, What's Happening?

1. GeomColumn.STAsText(): This says, "Tell me the coordinates of this shape as text," like POINT (-117.3 33.8).
2. geography::STGeomFromText(...): This says, "Take that text...
3. ... 4326): ...and plot it on the WGS 84 globe!"

And just like that, your data is no longer flat! ✨