Go to content Go to navigation and search

Home

Current SQL Server Blog Articles

    SQL Server Spatial: STFlipVectors
    SQL Server Spatial: Extract LineStrings in GeometryCollection to create LineString
    SQL Server Spatial: Converting a LineString to COGO XML
    SQL Server Spatial: Creating (Multi)LineStrings geometries from COGO XML instructions
    New Version of Package of TSQL Spatial Functions
    Vectorize/Segmentize SQL Server 2012
    Coordinate Editing Functions for SQL Server Spatial
    Function to Scale a geometry object for SQL Server Spatial
    TSQL String Tokenizer Function for SQL Server
    geography/geometry to MBR helper functions
    generate_series for SQL Server 2008
    Extract Polygons from result of STIntersection in SQL Server Spatial
    Function to round ordinates of a SQL Server Spatial geometry object
    Extract elements of SQL Server Spatial geometry object
    Counting number of polygon rings SQL Server Spatial
    Filtering Polygon Rings in SQL Server Spatial
    Function to Move a geometry object in SQL Server Spatial
    Alternate Centroid Functions for SQL Server Spatial
    Function to Rotate geometry objects in SQL Server Spatial
    A GetVertices wrapper for DumpPoints in SQL Server 2008 Spatial
    Creating a Morton number Space Key generator for SQL Server
    Gridding a geometry or geography object (SQL Server Denali)
    On hinting spatial indexes
    Random Search Procedure (SQL Server 2008 Spatial)
    COGO: Converting (Google Earth) Formatted Longitude/Latitude points to decimal degrees (SQL Server)
    COGO: Convert Degrees, Minutes and Seconds values to Decimal Degrees
    COGO: DD2DMS Formatting a latitude/longitude decimal degree value
    COGO: Create point from bearing and distance
    COGO: Compute number of vertices required to stroke circle or circular arc
    COGO: Calculating the bearing between two points (SQL Server 2008 Spatial)
    COGO: Compute arc length subtended by angle centre of circle
    COGO: Compute chord length of segment of a circle (arc)
    COGO: Computing Arc To Chord (Arc2Chord) Separation
    COGO: Creating a stroked polygon from a circle's centre x,y and radius
    COGO: Finding centre and radius of a curve defined by three points: FindCircle function
    Identifying Tight Radius Curves sections within LineString geometry data
    Generating random point data for SQL Server 2008 Spatial
    New Presentation on Active (Searchable) Spatial Metadata for SQL Server 2008 Spatial and FreeText
    Checking is a column in a table or a view is of type geometry or geography
    Extracting geometry type string values from geometry/geography objects in SQL Server 2008 Spatial
    Vectorising geometry objects in SQL Server 2008
    MBR to geography/geometry helper functions
    Extracting geometry types from geometry/geography objects in SQL Server 2008
    Getting the number of coordinate dimensions of a geometry/geography object in SQL Server 2008
    A Dump Points Function for SQL Server 2008 Spatial
    toGeography and toGeography Conversion Functions for SQL Server 2008 Spatial
    Write text file with spatial data from SQL Server 2008
    Loading Shapefiles into Geography type column in SQL Server 2008
    Tip #6: Correcting invalid geometries
    Tip #5: Where or where has my little column gone
    Tip #4: What Coordinate System is that?
    Tip #3: What object is that?
    Tip #2: Spatial Indexing and Primary Keys
    Tip #1: SQL Server 2008 "Katmai" - Setting SRIDs

Search

    Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Loading Shapefiles into Geography type column in SQL Server 2008

Friday May 01 2009 at 09:45

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:
bq. 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:

  1. load data into geometry object (and tlet it correct the ring orientation);
  2. add a geography column to the table;
  3. update it with corrected data (via STUnion()) from the geometry column;
  4. 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.

Open Jump Validation Menu

and, processing the resulting bad features theme…

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 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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [1]

Another tool can help in loading data to SQL Serve spatial is ST-Links QMap. The loader not only load Shapefile, but also load other feature Classes (file geodatabase, personal geodatabase, and SDE Geodatabase).
It corrects the ring direction as well.

If you could write a blog about this, that will be great!. Thanks.

Ken · 24 August 2011, 18:10 · #