Convert geography to SQL Server 2008R2 geometry

Question

Hello, I have the following code in SQL Server, why if I want to calculate sTArea @geog to fail and using @geom to succeed? How can I convert this polygon from geometry to a geostatic data type to get STArea ?, thanks.

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(); 

Answer 1

I thought a little and this answer How to convert geometry data to geography data in MS SQL Server 2008? which more or less simply point 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.

Purpose: You must make sure that your geometry can be first translated into real geography.

Code (which, of course, may contain some operations, but they are deduced 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 who don’t read all the way through the Spatial Ed blog, one important tip: “Please keep in mind that this approach is naive because it does not accommodate several potential boundary conditions. However, this approach should work in many cases "