Strange behavior of spatial data
June 29, 2012 2 Comments
As of today, I’m kind of forced to admit I have a problem… I’m in love with spatial data. And once you’re hooked, there’s no turning back. It could be worse of course! And in these circumstances, you come across the most interesting cases…
After trying to draw a geometry polygon in SQL Server 2008, I wondered what the difference is between a polygon and a multipolygon. Polygon also accepts more then 1 polygon just like a multipolygon. So what’s the difference then? I think I found the difference, with the help of Andreas Wolter (Blog | @AndreasWolter).
Just look at the query below:
DECLARE @Obj GEOMETRY SET @Obj = GEOMETRY::STGeomFromText('POLYGON((10 0, 10 10, 0 10, 0 0, 10 0), (10 15, 10 25, 0 25, 0 15, 10 15, 10 15))' ,4326) SELECT @Obj.ToString(), @Obj
This query produces a valid set of polygons, even though it’s two objects in a single polygon. It draws the polygons, even though a polygon should only consist of 1 object, and not two as the example above.
This isn’t the weirdest I’ve seen. Just look at the example below:
DECLARE @Obj GEOMETRY SET @Obj = GEOMETRY::STGeomFromText('POLYGON((10 0, 10 10, 0 10, 0 0, 10 0), (10 15, 10 25, 0 25, 0 15, 10 15, 10 15))' ,4326) --================================================== SELECT 'Geo Object' AS Description, @Obj AS GeoObject, @Obj.ToString() AS GeoObject_ToString, @Obj.STIsValid() AS GeoObject_IsValid UNION ALL SELECT 'Geo Object + MakeValid()' AS Description, @Obj.MakeValid() AS GeoObject, @Obj.MakeValid().ToString() AS GeoObject_ToString, @Obj.MakeValid().STIsValid() AS GeoObject_IsValid
If you run the example, you’ll see a description of the objects, the spatial object itself, the object ToString(), and a bit (boolean) for STValid(). The first record in the resultset is just the same as in the previous example. The second row contains the method .MakeValid().
As you see the first record (2 objects in 1 polygon) is not a valid geometry object. The second records shows that .MakeValid() converts your polygon into a multipolygon. And if you check if the multipolygon is valid, it returns true.
This example was getting weird, but now run the example on a SQL 2012 instance. You will see a difference in the multipolygon coordinates. I’ve ran them on both versions, and you can see the differences in the screenshot below:
SQL Server 2008:
SQL Server 2012:
The conversion to multipolygon causes a different rounding of coordinates. It seems like the converison in SQL 2008 is changed quite a bit for SQL 2012.
So what does this mean for the usage of geometry and geography data? I don’t know… This might mean we always need to use .MakeValid() before storing a polygon. Part of the issue is, that spatial data is pretty new stuff. This means that there are no best practices yet. So time will tell what the best practices will become…