Best Practice when Loading geographic Polygons into SQL Server Spatial geometry column

(I’ve referred to the issues around use of geography vs geometry data types in articles elsewhere on this website.)

One common data-storage decision that is often made is the loading of geographic (latitude/longitude) data into SQL Server Spatial’s geometry column instead of a geography column.

The reason it is common, is that open source tools like GeoTools does not support the geography data type only the geometry data type. (This is for both SQL Server Spatial and PostgreSQL/PostGIS.)

Note that if you do use the geometry data type to store geographic polygon data (or points or linestrings for that matter) then any SQL Server functions like STBuffer() will not allow you to enter a distance in meters: one can only do this for geography data:

SELECT geog.STBuffer(5.0 /* meters */) as buffered_geography_object

Rather one has to express distances is a very unnatural way using decimal-degree values:

SELECT geom.STBuffer(0.000045 /* 5m meters along equator expressed as decimal degrees */) as buffered_geography_object

Of course 5m expressed in decimal degrees varies as one moves over the Earth’s surface!

Problem

A customer stored the Local Government Areas (LGA) of Queensland as geographic data (SRID 7844) but in a geometry SQL Server Spatial data type in a table (dbo.lga) but was not careful about the coordinate ordering of those polygons.

As mentioned, any processing of the geometry stored data by native SQL will not be geodetically aware, treating the long/lat coordinates in the geometry object as if they were planar XY ordinates.

However storage in a geometry data type doesn’t strictly speaking stop you from processing the data geodetically as one can do this:

WITH seachObjects AS (
SELECT geography::Point(-21.01, 148.2, 7844) AS gPt,
       geometry::STGeomFromText(geography::Point(-21.01, 148.2, 7844).STBuffer(5000.0).STAsText(),7844) AS gBuf
)
SELECT g.lga_code, g.lga AS shire_name,
       geography::STGeomFromText(g.ogr_geometry.STAsText(),7844).STDistance(x.gPt) AS DistMeters
  FROM seachObjects AS x JOIN [dbo].[lga] AS g ON g.ogr_geometry.STIntersects(x.gBuf) = 1
 ORDER BY geography::STGeomFromText(g.ogr_geometry.STAsText(),7844).STDistance(x.gPt);

What this query – through dynamic casting of the geometry data to geography data – is doing is finding all LGA polygons within 5,000 meters of a search point and then returning the found LGAs in order of closeness to the original point.

However there is one critical requirement that must be followed if such an query is to be successful and that is that polygon coordinates must have a correct winding order. That is:

* counter-clockwise for exterior rings and
* clockwise for interior rings.

Now the customer’s polygon data is not all stored with correct coordinate winding such that when a query like the above is executed incorrect distances are calculated because the incorrect polygons convert to whole-of-earth rectangles (thus everything is inside so the STDistance is always 0).

Invalid polygons can be found quite simply via:

SELECT g.lga_code, g.ogr_geometry
  FROM [dbo].[lga] as g
 WHERE geography::STGeomFromText(g.ogr_geometry.STAsText(),7844).STIsValid() = 0;

But once found they are hard to correct using the MakeValid().ReorientRings() SQL Server Spatial function pairing for a geography object as this function pairing is brittle and struggles to correct them properly.

So a best practice statement is this::

When storing geographic polygons in geometry columns, the coordinate winding/ordering must be corrected by the loading software.

How to Load Correct Geographic Polygons

Software like FME does this with the Orienter transformer which adjusts the orientation of a polygonal feature (or the direction of a linear feature). Its key parameter is Orientation Type, which lets you choose:

* Right hand rule – outer boundary vertices are arranged clockwise, holes are counter-clockwise.
* Left hand rule – outer boundary vertices are counter-clockwise, holes are clockwise.

Storing geodetic polygons with correct coordinate winding order enables a belt-and-suspenders approach to spatial data processing. Once can use GeoTools (belt) to access geometry data but then execute native SQL queries (suspenders) such as the above in any SQL tool tool such as dBeaver etc.