Current SQL Server Blog Articles
A colleague is trying to load shapefiles into SQL Server 2008 and asked me the following question:
With a bit of directed questioning I found out that my colleague’s problem is predominantly with polygon ring orientation.
Ed Katibah’s blog article Working with Invalid Data and the SQL Server 2008 Geography Data Type, Part 1b is instructive as it informs us that:
Now, my colleague’s problem stems from the fact that, as Ed tells us:
However, it appears that the geometry type in SQL Server 2008 not only doesn’t suffer from this but that it also attempts some correction:
(Note: Oracle’s SDO_Geometry polygons, to be valid, must have anti-clockwise outer ring rotation and clockwise inner ring rotation otherwise SDO_GEOM.VALIDATE_GEOMETRY will return:
Then, Ed lets the power of STUnion() be known:
(As an side, when using Oracle Spatial, the standard “trick” to fix polygon problems, including ring orientation, was to do a self-intersect using SDO_GEOM.SDO_UNION; the use of SDO_MIGRATE.TO_CURRENT was also another method. However, the use of SDO_UNION requires a Spatial license and so is not available to Locator users: at 10g onwards Oracle added the RECTIFY_GEOMETRY function to the SDO_UTIL package; this function will fix ring rotation for polygons.)
So, the whole method is then pretty clear:
If you still have troubles with step 1, then consider loading a WKT description into a text column and use *AsText() and MakeValid() functions to gradually copy the data across. Other than that, if your GIS client doesn’t fix the data, or you can’t afford a copy of the excellent FME, try using Open Jump’s excellent shapefile validation tools. As the following images show.
and, processing the resulting bad features theme…
But how did we get into this Mess
Well, other than the fact that the shapefile is nothing more than a bit-bucket for unvalidated data (thus you can store more “crap” in them than my sheep deposit in my pastures each week) – we need a better exchange format – there are some other fundamental issues going on here.
And, like all good standards (ISO and OGC) etc there is plenty of “wiggle room” with respect to ring orientation because both standards are pretty damn quiet about something IMV very fundamental. Perhaps there is something in what Jason Follas observes in his bog SqlGeography: Ring Orientation of Polygon Interior Rings that is a little too close to the truth.
If you read Polygons: the unstable foundation of spatial modeling by
(I have had trouble tracking down this assertion.)
But we certainly know that the OGC Simple Features Specification 1.1 for which SQL Server 2008 was built to comply, is deafly silent with respect to polygon ring orientation. Enough so that I am surprised that Microsoft can claim for their geography type that (for their STUnion method):
Yet I see no evidence for this “orientation required”.
Look, let’s just get over this issue. How about we define a polygon to have anti-clockwise rotation for its outer rings and clockwise for their inner rings (it has excellent basis in graphics programming of areas: outer ring produces a +ve value, inner ring a -ve value. Add both together and you get the right answer). After all, if you believe Microsoft (cf argument about geography and STUnion) and Oracle (their polygon rotation is as per the ISO standard) then the rotation should be as I have just defined. Microsoft + Oracle are heavyweights trying to be standards compliant: not a bad basis for consensus.
If anyone can help me with quotes directly from later ISO document that show that the rotation should be as described, please let me know.