STArea() gives strange result for GeometryCollection object

Category: sql server spatial

Question

xlcottawa on Fri, 12 May 2017 16:56:07


Hi,

I have a question about the STArea() function. With SQL server 2012 spatial, I run the following code,

declare @geo_10 Geography, @geo_11 Geography, @geo_12 Geography, @geo_13 Geography

Set @geo_10 = Geography::STGeomFromText(‘GEOMETRYCOLLECTION (POINT (-79.617174120000016 47.470068989999994), LINESTRING (-79.606468080000027 47.465965980000085, -79.601627880000024 47.463477030000007, -79.600453919999822 47.462874030000158, -79.600161959999866 47.462723009999927, -79.5946161600001 47.459208959999913), POLYGON ((-79.628018943793677 47.477663435787981, -79.631256959999931 47.480499000000087, -79.63188012 47.486418029999982, -79.631256959999988 47.480498999999995, -79.628018943793677 47.477663435787981)), POLYGON ((-79.584221160000169 47.451236039999863, -79.593315839999818 47.45821400999985, -79.5946161600001 47.459208959999913, -79.593315839999988 47.45821401, -79.584221160000169 47.451236039999863)))', 4269)

Set @geo_11 = Geography::STGeomFromText('LINESTRING (-79.606468080000027 47.465965980000085, -79.601627880000024 47.463477030000007, -79.600453919999822 47.462874030000158, -79.600161959999866 47.462723009999927, -79.5946161600001 47.459208959999913)', 4269)

Set @geo_12 = Geography::STGeomFromText('POLYGON ((-79.628018943793677 47.477663435787981, -79.631256959999931 47.480499000000087, -79.63188012 47.486418029999982, -79.631256959999988 47.480498999999995, -79.628018943793677 47.477663435787981))', 4269)

Set @geo_13 = Geography::STGeomFromText('POLYGON ((-79.584221160000169 47.451236039999863, -79.593315839999818 47.45821400999985, -79.5946161600001 47.459208959999913, -79.593315839999988 47.45821401, -79.584221160000169 47.451236039999863))', 4269)

select @geo_10, @geo_10.STArea()  // gives 510065621710996

select @geo_11, @geo_11.STArea() // gives 0

select @geo_12, @geo_12.STArea() // gives 2.38418579101563E-06

select @geo_13, @geo_13.STArea() // gives 0

The @geo_11, @geo_12, and @geo_13 are created with a  same WKT component as in that for creating @geo_10.

Why is the area of @geo_10 given by STArea() so big?

Thanks

Replies

Teige Gao on Mon, 15 May 2017 02:50:59


Hi xlcottawa,

 

Based on my test, this is a problem related to the behavior of combination between STArea() and GEOMETRYCOLLECTION() function.

 

In some scenario, the STArea() function will give the result of aggregate area of the these polygons, points in the collection. However in other scenario, it will return the value of area of the UNION between these polygons, points.

 

Here is a similar case, you can refer to this case: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/242da196-aaa6-4117-849e-c02c22c4d18f/starea-and-stlength-behavior-with-geometrycollections?forum=sqlspatial

 

Best Regards,

Teige