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


"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… 

 

Comments (19)

  1. George says:

    Thanks Ed, this has been a source of great frustration for me.

  2. This post covers the following methods: STDimension, STGeometryType, STNumGeometries, STGeometryN, STNumPoints,

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

  4. pmsudhi says:

    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

  5. Ed Katibah says:

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

  6. TJ says:

    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)

  7. Ed Katibah says:

    @TJ – yes, this would be a multipolygon instance.

  8. TJ says:

    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.

  9. TJ says:

    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();

  10. suresh says:

    Awsome It works perfectly. Thank you.

  11. fd says:

    Mate, that's a life savior, thanks heaps

  12. Brilliant. Still – if STUnion needs a MakeValid, STStartPoint might, too. At least I needed that to fix my imported data.

  13. @Robert Heinig II – Thank you that was just the tip I needed.

  14. Patrick Fornes says:

    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?

  15. kanchan says:

    I tried this method but got below error

    Msg 6522, Level 16, State 1, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.

    System.FormatException:

    Could you please suggest what could be wrong

  16. Leigh Hunt says:

    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?

  17. kanchan says:

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

  18. Kanchan says:

    Complete error message is below:-

    Msg 6522, Level 16, State 1, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.

    System.FormatException:

      at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)

      at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)

      at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)

      at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)

      at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringText()

      at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePolygonText()

      at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)

      at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)

      at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

  19. Mauricio says:

    I'm having the same error as @Kanchan. Did you have the chance @Kachan of fix it?