Loading Shapefiles into Geography type column in SQL Server 2008
A colleague is trying to load shapefiles into SQL Server 2008 and asked me the following question:
I’m migrating mapinfo and/or shapefiles GDA94 files into sql 2008 geography data type (SRID 4283). I’m using the free Shape2SQL or EasyLoader. Some files fall over with .net exceptions. I suspect something about the coordinates not being valid Geography data types in SQL, but not sure how to check for this or fix. Some files import fine. All files import ok into geometry data type, but I shouldn’t store them like that.
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:
One of the most common data errors when attempting to import spatial data into the Geography data type involves the ring orientation of polygons
Now, my colleague’s problem stems from the fact that, as Ed tells us:
SQL Server does not allow polygons with the “wrong” ring orientation into the Geography data type
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:
When executing an OGC-based method (which works on the entire geometry) on valid polygon features in the Geometry data type, SQL Server will check ring orientation. If SQL Server finds outer rings oriented in a clockwise direction, it will re-orient such rings to counter-clockwise – the direction required for outer rings in the Geography data type. The same goes for inner rings (holes), which SQL Server will orient to clockwise.
(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:
ORA-13367: wrong orientation for interior/exterior rings
Cause: In an Oracle Spatial geometry, the exterior and/or interior rings are not oriented correctly.
Action: Be sure that the exterior rings are oriented counterclockwise and the interior rings are oriented clockwise.)
Then, Ed lets the power of STUnion() be known:
The key to this behavior is the the STUnion() method. Since this is an OGC-based method, working on the entire geometry for a given feature, it forces polygons into the orientation required for the method – which just happens to be the one used for the Geography type (quite a coincidence, eh? 😉
(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:
- load data into geometry object (and tlet it correct the ring orientation);
- add a geography column to the table;
- update it with corrected data (via STUnion()) from the geometry column;
- finally, drop the geometry column.,
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.
!http://www.spatialdbadvisor.com/images/67.gif (Open Jump Validation Menu)!
and, processing the resulting bad features theme…
!http://www.spatialdbadvisor.com/images/68.gif (Bad Features display)!
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 (Holes) that is a little too close to the truth.
If you read Polygons: the unstable foundation of spatial modeling by
Peter van Oosterom, Wilko Quak and Theo Tijssen from “GIS technology, TU Delft in The Netherlands”, then part of the problem is the fact that the ISO and OGC standards disagree. The following selected quotes help. First from the 2003 ISO standard (ISO/TC 211/WG 2, ISO/CD 19107):
Also the ISO standard is very explicit about the orientation of the outer and inner boundaries (in 2D looking from above: counterclockwise and clockwise for respectively the outer and inner boundaries).
(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):
this is an OGC-based method, working on the entire geometry for a given feature, it forces polygons into the orientation required
Yet I see no evidence for this “orientation required”.
Conclusion
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.
Finally
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.