Convert geography to geometry SQL Server 2008R2

Question

Hello, i have the following code in SQL Server, why if i want to calculate the sTArea of @geog fails and with @geom succeed?, how can i convert this polygon from geometry to geography datatype in order to get the STArea?, thank you.

DECLARE @geom geometry;
SET @geom = geometry::STGeomFromText('POLYGON ((-99.213546752929688 19.448402404785156, -99.2157974243164 19.449802398681641, -99.2127456665039 19.450002670288086, -99.213546752929688 19.448402404785156))', 4326); 
select @geom.STArea();

DECLARE @geog geography;
SET @geog = geography::STGeomFromText('POLYGON ((-99.213546752929688 19.448402404785156, -99.2157974243164 19.449802398681641, -99.2127456665039 19.450002670288086, -99.213546752929688 19.448402404785156))', 4326); 
select @geog.STArea();

Muskogee

Answer 1

I poked around a little and this answer How can I convert Geometry data into a Geography data in MS SQL Server 2008? which more or less just points to http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx leads me to a reasonable explanation and working code.

The crux: You have to make sure your geometry can be translated to valid geography first.

The code (which can certainly have some operations combined, but they are broken out here for clarity.)

DECLARE @geog GEOGRAPHY;
DECLARE @geom GEOMETRY;

SET @geom = GEOMETRY::STGeomFromText('POLYGON ((-99.213546752929688 19.448402404785156, -99.2157974243164 19.449802398681641, -99.2127456665039 19.450002670288086, -99.213546752929688 19.448402404785156))', 4326); 
SET @geom = @geom.MakeValid() --Force to valid geometry
SET @geom = @geom.STUnion(@geom.STStartPoint()); --Forces the correct the geometry ring orientation
SET @geog = GEOGRAPHY::STGeomFromText(@geom.STAsText(),4326) 

SELECT @geog.STArea();

And for those that won't read all the way through Spatial Ed's blog post one important tip, "please bear in mind that this approach is naive in that it does not accommodate several potential edge conditions. Never-the-less, this approach should work in many cases."

Assignation

I would expect (but don't know for a fact) that converting via WKB would be a bit more efficient than via WKT. – Obtain

Life saver dude! I used this in my C# code with some adaption and it works like a charm – Bumf

Thanks very much. – Ruffle