locked
Valid geometries invalid in collection RRS feed

  • Question

  • Hi,
    I've found a strange behaviour. I've got 10 valid geometries, but when I define them inside a geometrycollection, the collection is invalid. Here is the code:

    declare @col geometry
    
    set @col='GEOMETRYCOLLECTION (
     POLYGON ((-53654820 466950613, -53654910 466951040, -53654910.778543927 466951038.95740443, -53654820 466950613))
    ,POLYGON ((-53654386 466946439, -53654647 466947653, -53654512 466947014, -53654386 466946439))
    ,POLYGON ((-53635163 466923950, -53638188 466926683, -53638086 466926590, -53637097 466925696, -53635163 466923950))
    ,POLYGON ((-53630297 466916522, -53631363.650925882 466918237.704847, -53631127 466917855, -53630297 466916522))
    ,POLYGON ((-53628398 466913530, -53630297 466916522, -53629361 466915040, -53628398 466913530))
    ,POLYGON ((-53626144 466910071, -53627112 466911548, -53627499 466912138, -53626747 466910985, -53626144 466910071))
    ,POLYGON ((-53625306 466908822, -53626144 466910071, -53625672 466909364, -53625306 466908822))
    ,POLYGON ((-53622962 466906059, -53624180 466907396, -53623623 466906779, -53622962 466906059))
    ,POLYGON ((-53618541.826558381 466901502.64478743, -53619606 466902550, -53618732 466901689, -53618541.826558381 466901502.64478743))
    ,POLYGON ((-53618539.836853296 466901500.68652767, -53618540.45888672 466901501.29873049, -53618540.30078125 466901501.140625, -53618539.83984375 466901500.68945312, -53618539.836853296 466901500.68652767))
    )'
    
    declare @p1 geometry = 'POLYGON ((-53654820 466950613, -53654910 466951040, -53654910.778543927 466951038.95740443, -53654820 466950613))'
    declare @p2 geometry = 'POLYGON ((-53654386 466946439, -53654647 466947653, -53654512 466947014, -53654386 466946439))'
    declare @p3 geometry = 'POLYGON ((-53635163 466923950, -53638188 466926683, -53638086 466926590, -53637097 466925696, -53635163 466923950))'
    declare @p4 geometry = 'POLYGON ((-53630297 466916522, -53631363.650925882 466918237.704847, -53631127 466917855, -53630297 466916522))'
    declare @p5 geometry = 'POLYGON ((-53628398 466913530, -53630297 466916522, -53629361 466915040, -53628398 466913530))'
    declare @p6 geometry = 'POLYGON ((-53626144 466910071, -53627112 466911548, -53627499 466912138, -53626747 466910985, -53626144 466910071))'
    declare @p7 geometry = 'POLYGON ((-53625306 466908822, -53626144 466910071, -53625672 466909364, -53625306 466908822))'
    declare @p8 geometry = 'POLYGON ((-53622962 466906059, -53624180 466907396, -53623623 466906779, -53622962 466906059))'
    declare @p9 geometry = 'POLYGON ((-53618541.826558381 466901502.64478743, -53619606 466902550, -53618732 466901689, -53618541.826558381 466901502.64478743))'
    declare @p10 geometry = 'POLYGON ((-53618539.836853296 466901500.68652767, -53618540.45888672 466901501.29873049, -53618540.30078125 466901501.140625, -53618539.83984375 466901500.68945312, -53618539.836853296 466901500.68652767))'
    
    select 'COL',@col.STIsValid()
    union all
    select 'P1',@p1.STIsValid()
    union all
    select 'P2',@p2.STIsValid()
    union all
    select 'P3',@p3.STIsValid()
    union all
    select 'P4',@p4.STIsValid()
    union all
    select 'P5',@p5.STIsValid()
    union all
    select 'P6',@p6.STIsValid()
    union all
    select 'P7',@p7.STIsValid()
    union all
    select 'P8',@p8.STIsValid()
    union all
    select 'P9',@p9.STIsValid()
    union all
    select 'P10',@p10.STIsValid()

    Monday, January 25, 2010 10:48 AM

All replies

  • It appears that a couple of your polygons intersect.  I did a couple tests

    declare @p1 geometry = 'POLYGON ((-53654820 466950613, -53654910 466951040, -53654910.778543927 466951038.95740443, -53654820 466950613))'
    declare @p2 geometry = 'POLYGON ((-53654386 466946439, -53654647 466947653, -53654512 466947014, -53654386 466946439))'
    declare @p3 geometry = 'POLYGON ((-53635163 466923950, -53638188 466926683, -53638086 466926590, -53637097 466925696, -53635163 466923950))'
    declare @p4 geometry = 'POLYGON ((-53630297 466916522, -53631363.650925882 466918237.704847, -53631127 466917855, -53630297 466916522))'
    declare @p5 geometry = 'POLYGON ((-53628398 466913530, -53630297 466916522, -53629361 466915040, -53628398 466913530))'
    declare @p6 geometry = 'POLYGON ((-53626144 466910071, -53627112 466911548, -53627499 466912138, -53626747 466910985, -53626144 466910071))'
    declare @p7 geometry = 'POLYGON ((-53625306 466908822, -53626144 466910071, -53625672 466909364, -53625306 466908822))'
    declare @p8 geometry = 'POLYGON ((-53622962 466906059, -53624180 466907396, -53623623 466906779, -53622962 466906059))'
    declare @p9 geometry = 'POLYGON ((-53618541.826558381 466901502.64478743, -53619606 466902550, -53618732 466901689, -53618541.826558381 466901502.64478743))'
    declare @p10 geometry = 'POLYGON ((-53618539.836853296 466901500.68652767, -53618540.45888672 466901501.29873049, -53618540.30078125 466901501.140625, -53618539.83984375 466901500.68945312, -53618539.836853296 466901500.68652767))'

    declare @combo geometry
    set @combo = @p1.STUnion(@p2).STUnion(@p3).STUnion(@p4).STUnion(@p5).STUnion(@p6).STUnion(@p7).STUnion(@p8).STUnion(@p9).STUnion(@p10)

    select @combo.STAsText(), @combo.STNumGeometries()
    -- multipolygon, 11 geometries

    select @p4.STIntersects(@p5)
    select @p6.STIntersects(@p7)

    Perhaps the GeometryCollection is unhappy with creating a bunch of polygons that have some "overlapping areas".    Would a "STUnion"ed MULTIPOLYGON work for you instead?
    Monday, January 25, 2010 12:31 PM
  • Thanks for your answer. Actually, it works just the opposite as you say. You can define intersecting polygons in a GEOMETRYCOLLECTION, but it fails if you try to build them as MULTIPOLYGON. Check this simple example:

    declare @p1 geometry = 'POLYGON ((0 0, 10 0, 10 10 , 0 10, 0 0))'
    declare @p2 geometry = 'POLYGON ((5 0, 15 0, 15 10 , 5 10, 5 0))'
    
    declare @col geometry ='GEOMETRYCOLLECTION(POLYGON ((0 0, 10 0, 10 10 , 0 10, 0 0)),POLYGON ((5 0, 15 0, 15 10 , 5 10, 5 0)))'
    
    declare @mpol geometry ='MULTIPOLYGON(((0 0, 10 0, 10 10 , 0 10, 0 0)),((5 0, 15 0, 15 10 , 5 10, 5 0)))'
    
    
    select 'P1',@p1.STIsValid()
    union all
    select 'P1',@p2.STIsValid()
    union all
    select 'GCOL',@col.STIsValid()
    union all
    select 'MPOL',@mpol.STIsValid()
    
    

    Monday, January 25, 2010 2:30 PM
  • Hi Folks,

    This is by design.

    About MultiSurfaces (from which MultiPolygons derive) the OGC states: "The interiors of any two Surfaces in a MultiSurface may not intersect."

    But for a GeometryCollection they are very permissive: "All elements in a GeometryCollection shall be in the same Spatial Reference... GeometryCollection places no other constraints on its elements."

    Cheers,
    -Isaac
    Isaac Kunen, Microsoft SQL Server
    Thursday, February 11, 2010 5:00 PM
  • So there is no reason for these polygons to be invalid in a collection... 


    Thursday, February 11, 2010 8:29 PM
  • Sorry: Selective reading error.  :)

    In your original example, all of the polygons are valid, but the @col.STIsvalid() call returns 0.  Is that right?

    I have a SQL 2008 R2 build on my machine, and all of them---including the GC---return valid.  If you're seeing something else on an earlier build it's possible we fixed a bug.

    Cheers,
    -Isaac

    Isaac Kunen, Microsoft SQL Server
    Thursday, February 11, 2010 10:17 PM
  • I'm happy to hear that. I've been having several problems operating with collections, and this is certainly good news.

    Thanks!
    Friday, February 12, 2010 8:05 AM
  • If you haven't done so, I'd take the time to file these through Connect.  It's possible that we fixed some, but not all of the issues you're seeing.  And if there are customer reports of issues, it should increase the chance that we release fixes in service packs for prior releases.

    Cheers,
    -Isaac
    Isaac Kunen, Microsoft SQL Server
    Friday, February 12, 2010 3:18 PM
  • Hi,
    I've installed R2 and i'm still having the same problem. I've sended this topic to connect:

    Saturday, February 13, 2010 12:03 AM
  • Thanks vIndEx, I voted for you on connect...

    I recently encountered a situation similar to this, only it is related to the Geography type.  I was able to import a set of data into a geography column using Shape2Sql.exe.  I then tried to create a geometry column from the WKB from the geography column (wrongly thinking that since geography seems to be much more strict and does not even allow "Invalid" data to be inserted, given it made it to geography it should make it into geometry).  The geography for the record in question was a multipolygon, and two of the polygons happened to share a point.  Converting to geometry, the multipolygon was invalid.

    I guess my question is this - should the row have made it into the database when importing into the geography column or rejected as an error?  I don't know how closely (if at all) the geography type follows the OGC reference Isaac mentioned earlier in this thread. 

        About MultiSurfaces (from which MultiPolygons derive) the OGC states: "The interiors of any two Surfaces in a MultiSurface may not intersect."

    Overlapping/shared single points are allowed in geography, but overlapping/shared lines are not allowed and overlapping areas are not allowed.

    To see this try the following:

    -- construct a geography object that is a multipolygon that happens to touch at a single point
    declare @b1 geography = geography::STGeomFromText('MULTIPOLYGON (((0 1, 1 0, 2 1, 1 2, 0 1 )),((2 1, 3 0, 4 1, 3 2, 2 1 )))',4326)
    select @b1
    -- no error

    -- convert the geography object to a geometry object (it will go in without trouble, but will be invalid)
    declare @b2 geometry = geometry::STGeomFromWKB(@b1.STAsBinary(), @b1.STSrid)
    select @b2.STAsText(), @b2, @b2.STIsValid()
    -- no error, but invalid

    -- construct a geography object that is a multipolygon that happens to have some overlapping area instead of touching at single point
    declare @b3 geography = geography::STGeomFromText('MULTIPOLYGON (((0 1, 1 0, 2 1, 1 2, 0 1 )),((0 1, 3 0, 4 1, 3 2, 0 1 )))',4326)
    select @b3

    /*
    Msg 6522, Level 16, State 1, Line 2
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
    System.ArgumentException:
       at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
    */
      
    -- construct a geography object that is a multipolygon that happens to share a line segment instead of touching at single point
    declare @b4 geography = geography::STGeomFromText('MULTIPOLYGON (((0 0, 1 0, 1 1, 0 1, 0 0 )),((1 0, 2 0, 2 1, 1 1, 1 0 )))',4326)
    select @b4

    /*
    Msg 6522, Level 16, State 1, Line 2
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
    System.ArgumentException:
       at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
    */

     

    Tuesday, February 23, 2010 10:06 PM
  • Hi,

    I've tried this code in CTP R2 and I'm not getting the error:

    declare @b1 geography = geography::STGeomFromText('MULTIPOLYGON (((0 1, 1 0, 2 1, 1 2, 0 1 )),((2 1, 3 0, 4 1, 3 2, 2 1 )))',4326)
    declare @b2 geometry = geometry::STGeomFromWKB(@b1.STAsBinary(), 4326)
    select @b2.STIsValid()
    Result:1


    Tomorrow at work I'll try with an older version and see what happens
    Tuesday, February 23, 2010 11:30 PM
  • Thanks for taking a look.  I tried it on my dev machine at home and am seeing that for the code I posted for @b2 STIsValid is coming out as 1.   I must have grabbed the wrong output from my scripts as the real polygons that are showing this behavior are some admin boundaries with many thousand vertice points.  It is late and getting a bit delirious...

    Please try the other two tests (@b3 and @b4) to see if you get the runtime errors.   Seems funny to me that for a geography object a shared point is ok, but shared line or overlapping area are not.  Is that the intended behavior, and I am misunderstanding?

    I constructed another case that has 2 shared points but not a shared line and it did not complain either.
     declare @b5 geography = geography::STGeomFromText('MULTIPOLYGON (((0 0, 2 0, 1 1, 2 2, 0 2, 0 0)), ((2 2, 2 0, 3 0, 3 2, 2 2)))',4326)
     select @b5

    No shared vertice but a single tangent point does not complain.
    declare @b6 geography = geography::STGeomFromText('MULTIPOLYGON (((0 1, 1 0, 2 1, 1 2, 0 1 )),((2 2, 2 0, 3 0, 3 2, 2 2 )))',4326)
    select @b6

    I would try to cook up a "bowtie" shape, but at this point might be convinced this is all confusion on my end and need some sleep.
    Wednesday, February 24, 2010 5:02 AM
  • As Isaac said "The interiors of any two Surfaces in a MultiSurface may not intersect"

    The key word is interiors. Let's say that geometries can touch, but not overlap in a multipolygon.  The spatial relations are based on DE-9IM matrix, wich calcultes the relation between the interiors, the boundaries and the exteriors. For more information, read this:



    So, it's ok for @b3 to throw an exception. But according to OGC definition, @b4 should be valid.

    Actually, it's better to think this way: Can I build the multipolygon as a simple polygon? If so, it's invalid. If you want to treat the polygons as indepent geometries, use a geometrycollection.



    Wednesday, February 24, 2010 8:06 AM
  • Thanks for highlighting "interiors",  when reading it the first time I picked up "surfaces may not intersect" but not "interiors".  This now makes sense.
    Wednesday, February 24, 2010 11:26 AM