Partilhar via

Working with Invalid Data and the SQL Server 2008 Geography Data Type, Part 1b

  • Artigo
"Fixing" Polygon Ring Orientation in SQL Server 2008 using T-SQL

In the last installment, I promised to try and correct invalid Geography data using FME.  While I am making progress on that front, I decided to take a small detour and discuss an interesting but little known behavior of the SQL Server Geometry type, polygons and data validation.

One of the most common data errors when attempting to import spatial data into the Geography data type involves the ring orientation of polygons:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.

Consequently, we often see inquiries about changing ring orientation in SQL Server, using T-SQL.  Since SQL Server does not allow polygons with the "wrong" ring orientation into the Geography data type, any solution to this dilemma must use the Geometry data type. At this point, I should note that FME 2008, when loading data into a column of type Geography, attempts to correct polygon ring orientation.  I say attempts, since there are edge conditions (+- 180 degree meridian and the Poles) which can derail this process.  The same goes for the technique in T-SQL that I am about to describe.

Here is the aforementioned behavior - 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.

So, how might you take advantage of this behavior?  I'll use a very simple test case:

--
-- Create table with Geometry column
--
CREATE TABLE foo (
id INTEGER,
geom GEOMETRY);
--
-- Create two similar polygons with opposite ring orders
--
DECLARE @g GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @h GEOMETRY = 'POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))';
--
-- Persist polygons to the table
--
INSERT INTO foo (id, geom) VALUES(1,@g);
INSERT INTO foo (id, geom) VALUES(2,@h);
--
-- Update rows, forcing validation of spatial objects
--
UPDATE foo
  SET geom = geom.STUnion(geom.STStartPoint());
--
-- Verify new, consistent, ring order after forced validation
--
SELECT id,geom.ToString() FROM foo;
--
-- Results:
-- 1 POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
-- 2 POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))

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? ;-).  This method illustrated is quite efficient, keeping overhead small in the quest to achieve our goal of re-orienting the polygon rings.

Since the Geometry must be valid for this to work, you might want to add the MakeValid() method to the processing stream. 

UPDATE foo
SET geom = geom.MakeValid().STUnion(geom.STStartPoint());

It is important to note that a MakeValid() on an already valid polygon geometry will not change ring orientation, hence the need to "force" a complete validation pass with the OGC-based method (STUnion, in this case).

To finish up this exercise, let's move the Geometry data to Geography data in a sister table.  Before doing this, please bear in mind that this approach is naive in that it does not accommodate several potential edge conditions.  Never-the-less, this approach should work in many cases.

--
-- Create table with Geography column
--
CREATE TABLE gfoo (
id INTEGER,
geog GEOGRAPHY);
--
-- Convert from Geometry to Geography using Well Known Text
--
INSERT INTO gfoo
SELECT foo.id, GEOGRAPHY::STGeomFromText(foo.geom.STAsText(),4326)
FROM foo;
--
-- Convert from Geometry to Geography using Well Known Binary
--
INSERT INTO gfoo
SELECT foo.id, GEOGRAPHY::STGeomFromWKB(foo.geom.STAsBinary(),4326)
FROM foo;

A special thanks to IsaacK and BobB for suggestions and assistance with the SQL syntax...  

Technorati Tags: SQL Server,Spatial,polygon,ring orientation,geometry,geography,validation

Comments

  • Anonymous
    August 26, 2008
    Thanks Ed, this has been a source of great frustration for me.

  • Anonymous
    October 28, 2008
    This post covers the following methods: STDimension, STGeometryType, STNumGeometries, STGeometryN, STNumPoints,

  • Anonymous
    February 16, 2009
    This is an excellent post. It works for 99.9% of the geometries Ive had to deal with. For the other .1%, try adding this.. Reduce the geometry value by a very small amount 1st. Im not actually sure what this does internally, but it's solved most my issues. Example... select geography::STGeomFromWKB(geom.MakeValid().Reduce(.00000001).STUnion(geom.STStartPoint()).MakeValid().STAsBinary(), 4326) from tl_2008_us_csa

  • Anonymous
    December 15, 2009
    I tried the method and able to achieve the convertion. a problem still persist. when i call geom.Lat on the converted column it returns null. any clues

  • Anonymous
    December 16, 2009
    @pmsudhi - could you give me a little more detail?  If you would be willing to share your data, I'd be happy to take a closer look at it.

  • Anonymous
    December 21, 2009
    Is it possible to store more than one outer boundary polygon with potential inner boundaries within the same geometry field?  (i.e. store two completely separated polygons within the same geometry field for a single row)

  • Anonymous
    December 21, 2009
    @TJ - yes, this would be a multipolygon instance.

  • Anonymous
    December 21, 2009
    So how would you insert that entry into the geometry field.  I use an insert in the following form: geometry::STGeomFromText('POLYGON((OuterBoundaryPointsString),(InnerBoundaryPointsString1),(InnerBoundaryPointsString2), (InnerBoundaryPointsStringN))', 0)) How would it be loaded with more than one outer boundary points string? Thank you for your rapid reply by the way.

  • Anonymous
    December 21, 2009
    Nevermind.  I think I got it. DECLARE @g geometry; SET @g = geometry::STGeomFromText('MULTIPOINT(0 0, 13.5 2, 7 19)', 0); SELECT @g.STGeometryN(2).ToString();

  • Anonymous
    April 08, 2010
    Awsome It works perfectly. Thank you.

  • Anonymous
    December 07, 2010
    Mate, that's a life savior, thanks heaps

  • Anonymous
    February 14, 2012
    Brilliant. Still - if STUnion needs a MakeValid, STStartPoint might, too. At least I needed that to fix my imported data.

  • Anonymous
    December 04, 2012
    @Robert Heinig II - Thank you that was just the tip I needed.

  • Anonymous
    April 09, 2014
    I have seen an error thrown indicating that MakeValid may cause points to shift slightly. Can you please clarify what that means? I have not been able to find any further documentation on that. If My points passed into the original Geometry then using your method to make a geography have 6 decimal places, what level of shift would I need to expect?

  • Anonymous
    October 07, 2014
    The comment has been removed

  • Anonymous
    October 14, 2014
    Ed, thank you very much for this - you've preserved my sanity - for a while at least! @Kanchan, you don't by any chance have your longitude and latitude around the wrong way do you?

  • Anonymous
    October 16, 2014
    @Leigh, I just have a geometry column and I use below query to convert it to geography  SELECT GEOGRAPHY::STGeomFromText(geom.STAsText(),4326)  FROM [Spatial].[dbo].[Table1] There is no where i am using lOngitude on latitude specifically in my query. So where could this get mixed up?? I have used the transformation on this blog as well.

  • Anonymous
    October 17, 2014
    The comment has been removed

  • Anonymous
    October 13, 2015
    I'm having the same error as @Kanchan. Did you have the chance @Kachan of fix it?