次の方法で共有

Working with invalid Geography objects in SQL Azure

  • [アーティクル]

I was recently helping some with a project that let the user to draw shapes on the map and upload the Well Know Text for the shape into a database. To do this they were making use of the Shape Toolbox and the Well Known Text modules for Bing Maps. Using these two modules together with a web service was really easy to do. However, although the generated Well Known Text is valid as a SQL Geometry object, they may not always be a valid Geography object depending on how the user drew the shape. In an attempt to account for this we came across a unique difference in the spatial functionality between SQL Azure and SQL 2012. If we were to create a Geography object using a Well Known Text that is invalid, SQL 2012 would create the object but would throw an error asking use to use the MakeValid method if we tried to do any calculations against it. However in SQL Azure it throws an error when we try to create the Geography. The Geography class has the MakeValid class in both databases and in SQL 2012 we just need to use that to fix our Geography and be on our way. However, reading through the release notes for spatial functionality in SQL Azure we find this:

In this release of SQL Azure, invalid objects are not allowed in the Geography type. Consequently, the MakeValid() method, while present and operational for the Geography type, cannot do anything more than operate over an already valid object – effectively a no-op.

What this means is that even though the MakeValid method is available it is pretty much useless to us as we can’t create invalid geographies.

To test this out for your self, try running the following SQL script against both database. SQL 2012 will work and return a value that indicates that the geography is invalid. SQL Azure with throw an error message on the first line.

 Declare @g geography = geography::STGeomFromText('POLYGON ((113.991 63.771, 73.737 63.771, 73.737 42.793, 113.991 42.793, 113.991 63.771), (105.378 58.434, 83.229 58.434, 83.229 49.594, 105.378 49.594, 105.378 58.434))',4326);
Select @g.STIsValid()

So what can we do about this?

What makes a Geography Valid?

Before we dive into a solution we need to first understand what makes a geography object valid. The following is the basic criteria that is required:

  • No part of the shape should overlap with itself.
  • For Polygon & MultiPolygon objects:
    • Exterior rings must be ordered in a counter clockwise direction.
    • Inner rings must be ordered in a clockwise direction.
    • Rings must have the same start and end coordinates.

Solution 1 – SQL Spatial .NET Library

All the spatial functionality that is in SQL is available as a .NET library which you can use in your code. One solution to our problem is to make the Well Known Text valid in our web service before passing it to the database. This is fairly easy to do. For information on how to use the SQL Spatial .NET library in a web service take a look at this blog post I wrote. By doing this we will be using the same logic that is in SQL 2012 which allows us to create an invalid geography and then use the MakeValid method to fix it.

Solution 2 – Custom Stored Procedures

The first solution will work for a lot of situations, unfortunately the person I was helping had a Node.js backend and couldn’t make use of the .NET spatial library. So, I needed a different solution. I have written a lot of code in the past that helps to make Geographies valid in .NET in my Spatial Toolbox project, so this got me wondering how hard it would be to reuse this code logic in a Store Procedure. After a bit of work I ended up with 3 stored procedures.

I called the first stored procedure IsCCW. This method takes a Polygon ring or LineString from a Geometry object and checks to see if the coordinates are in a counter-clockwise direction. Here is the SQL script for this.

 CREATE PROCEDURE IsCCW
    @ring geometry,
    @status bit output
AS
BEGIN
    SET @status = 0;

    --Ensure that there are at least 4 coordinates. 
    --Polygons in SQL 2008 must start and end with the same coordinate. 
    --As such they require a minium of 4 coordinates to be valid.
    if (@ring.STNumPoints() < 4)
        SET @status = 0;
    ELSE
    BEGIN
        Declare @coordinate geometry = @ring.STPointN(1);

        DECLARE @index1 int = 1;
        DECLARE @i int = 1;

        WHILE @i <= @ring.STNumPoints() 
        BEGIN
            Declare @coordinate2 geometry = @ring.STPointN(@i);

            IF (@coordinate2.STY > @coordinate.STY)
            BEGIN
                SET @coordinate = @coordinate2;
                SET @index1 = @i;
            END
            SET @i = @i + 1
        END 

        DECLARE @num4 int = @index1;

        IF (@num4 < 0)
            SET @num4 = @ring.STNumPoints() - 2;

        DECLARE @num5 int = @index1 + 1;

        IF(@num5 >= @ring.STNumPoints())
            SET @num5 = 1;
        
        Declare @coordinate3 geometry = @ring.STPointN(@num4);
        Declare @coordinate4 geometry = @ring.STPointN(@num5);

        Declare @num6 float = ((@coordinate4.STX - @coordinate.STX) * (@coordinate3.STY - @coordinate.STY)) -
            ((@coordinate4.STY - @coordinate.STY) * (@coordinate3.STX - @coordinate.STX));

        IF (@num6 = 0)
            IF (@coordinate3.STX > @coordinate4.STX)            
                SET @status = 1;
            ELSE
                SET @status = 0;

        IF (@num6 > 0)
            IF (@coordinate3.STX > @coordinate4.STX)            
                SET @status = 1;
            ELSE
                SET @status = 0;
    END
END

I called the second stored procedure CreatePolygonFromRing. This method creates a valid Polygon Geometry out of a ring. Here is the SQL script for this.

 CREATE PROCEDURE CreatePolygonFromRing
    @ring geometry, @g geometry output
AS
BEGIN
    Declare @isCCW bit;

    EXEC IsCCW @ring, @isCCW output

    IF (@isCCW = 1)
        SET @g = geometry::STGeomFromText(REPLACE(@ring.STAsText(), 'LineString', 'Polygon(') + ')', 4326)
    ELSE
    BEGIN
        DECLARE @wkt nvarchar(max) = 'POLYGON((';

        -- reverse order of points
        DECLARE @i int = @ring.STNumPoints();

        WHILE @i > 0 
        BEGIN        
            SET @wkt += @ring.STPointN(@i).STX + ' ' + @ring.STPointN(@i).STY;

            IF (@i > 1)
                SET @wkt += ',';
            SET @i = @i - 1
        END 

        SET @wkt += '))';

        SET @g = geometry::STGeomFromText(@wkt, 4326)
    END
END

I called the third stored procedure MakeValidGeographyFromGeometry. This method takes a Geometry object and if it is a polygon uses the exterior ring to create a simple polygon, it then loops through each inner ring of the polygon and creates a simple polygon out of it and then uses it with the STDifference method to cut a hole in the main polygon. This fixes all the ring orientation issues. It then returns a valid Geography object or null if it is can’t be made valid. Here is the SQL script for this.

 CREATE PROCEDURE MakeValidGeographyFromGeometry
    @g geometry, @geoOut geography output
AS
BEGIN

    -- Make sure geometry is valid
    SET @g = @g.MakeValid()

    DECLARE @i int;
    Declare @tempGeom geometry;

    SET @geoOut = null;

    IF(@g.STIsSimple() = 1) -- Check that the geometry has no overlapping lines
        IF(@g.InstanceOf('Polygon') = 1)
        BEGIN
            Declare @poly geometry;
            Declare @isCCW bit;

            Declare @ring geometry = @g.STExteriorRing();

            EXEC CreatePolygonFromRing @ring, @poly output 

            SET @i = 1;

            WHILE @i <= @g.STNumInteriorRing() 
            BEGIN
                SET @ring = @g.STInteriorRingN(@i);

                -- Turn ring into polygon
                EXEC CreatePolygonFromRing @ring, @tempGeom output

                -- Remove inner ring polygon from main polygon
                SET @poly = @poly.STDifference(@tempGeom);
                
                SET @i = @i + 1
            END 

            SET @geoOut = geography::STGeomFromWKB(@poly.STAsBinary(), 4326);
        END
        ELSE IF(@g.InstanceOf('MultiPolygon') = 1 OR @g.InstanceOf('GeometryCollection') = 1)
        BEGIN
            SET @i = 2;

            SET @tempGeom = @g.STGeometryN(1);
            -- Convert the first geometry in the collection into geography
            EXEC MakeValidGeographyFromGeometry @tempGeom, @geoOut output

            DECLARE @tempGeo geography;

            WHILE @i <= @g.STNumGeometries() 
            BEGIN
                SET @tempGeom = @g.STGeometryN(@i);

                -- Convert the additional geometries in the collection into geography
                EXEC MakeValidGeographyFromGeometry @tempGeom, @tempGeo output

                SET @geoOut = @geoOut.STUnion(@tempGeo);
                
                SET @i = @i + 1
            END 
        END
        ELSE -- All other shapes
        BEGIN
            SET @geoOut = geography::STGeomFromWKB(@g.STAsBinary(), 4326);
        END
END

At this point we have all that we need to make our Geography valid. If we go back to the original example that worked in SQL 2012 but threw an error in SQL Azure we can updated as follows to make use of these stored procedures:

 Declare @g geometry = geometry::STGeomFromText('POLYGON ((113.991 63.771, 73.737 63.771, 73.737 42.793, 113.991 42.793, 113.991 63.771), (105.378 58.434, 83.229 58.434, 83.229 49.594, 105.378 49.594, 105.378 58.434))',4326);

Declare @h geography;
EXEC MakeValidGeographyFromGeometry @g, @h output 

Select @h.STIsValid()

This now works in both databases.

Comments