locked
STArea() and STLength() behavior with GeometryCollections RRS feed

  • Question

  • I observed the following behavior difference using the STLength() and STArea() functions on GeometryCollections of type Geometry vs. Geography and I wonder if there's a specific reason for the different behaviors.

    Scenario:

    Assume GeometryCollection "A" of type Geometry and GeometryCollection "B" of type Geography. Each GeometryCollection contains 2 polygons. In both cases, the two polygons contained in each GeometryCollection partially overlap each other.

    STLength():

    When applying the STLength() function, in the case of SQL GeometryCollection "A" ( type Geometry ), STLength() returns the length of the UNION between the two polygons in the collection whereas in the case of SQL GeometryCollection "B" ( type Geography ), it returns the aggregate length of the two polygons. In other words, in the case of SQL GeometryCollection "A" ( type Geometry ), the length of the intersection between the two polygons is not included in the STLength result.

    STArea():

    The behavior here seems to be the exact opposite for the two types. When applying the STArea() function, in the case of SQL GeometryCollection "A" ( type Geometry ), STArea() returns the aggregate area of the two polygons in the collection whereas in the case of SQL GeometryCollection "B" ( type Geography ), it returns the area of the UNION between the two polygons.

    I am curious as to why the discrepancy and which is the "correct" behavior.

    Friday, June 10, 2011 4:06 AM

Answers

  • I agree there's a discrepancy between the behaviour of the two datatypes - attached below is a script to demonstrate. However, I don't get quite the same results as you - I find that the results of STLength() for 'A' is neither the same as the aggregate or union length of the individual elements.

    As for what is "correct" - looking at the OGC specs (which are notoriously vague in places), I can't see any mention of how, or even if, the Area() method is implemented on a GeometryCollection. Section 7.2.16 of OGC 05-134 lists NumGeometries and GeometryN as the only defined SQL functions on a GeomCollection.

    The Area function is defined for Surface and MultiSurface types, where is is helpfully described that it "returns the area (of x)"...

     

    DECLARE @Poly1WKT nvarchar(255) = 'POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))';
    DECLARE @Poly2WKT nvarchar(255) = 'POLYGON((8 0, 12 0, 12 2, 8 2, 8 0))';
    DECLARE @GeomCollWKT nvarchar(255) = 
    'GEOMETRYCOLLECTION(
     POLYGON((0 0, 10 0, 10 5, 0 5, 0 0)),
     POLYGON((8 0, 12 0, 12 2, 8 2, 8 0))
     )';
    
    DECLARE @geomPoly1 geometry = @Poly1WKT;
    DECLARE @geomPoly2 geometry = @Poly2WKT;
    DECLARE @geomColl geometry = @GeomCollWKT;
    DECLARE @geogPoly1 geography = @Poly1WKT;
    DECLARE @geogPoly2 geography = @Poly2WKT;
    DECLARE @geogColl geography = @GeomCollWKT;
    
    SELECT
    'geometry (A)' AS 'datatype',
    @geomPoly1.STLength() AS 'poly1 length',
    @geomPoly2.STLength() AS 'poly2 length',
    @geomPoly1.STLength() + @geomPoly2.STLength() AS 'combined length',
    @geomColl.STLength() AS 'geometrycoll length',
    @geomPoly1.STArea() AS 'poly1 area',
    @geomPoly2.STArea() AS 'poly2 area',
    @geomPoly1.STArea() + @geomPoly2.STArea() AS 'combined area',
    @geomColl.STArea() AS 'geomcoll area'
    
    UNION ALL SELECT
    'geography (B)',
    @geogPoly1.STLength() AS 'poly1 length',
    @geogPoly2.STLength() AS 'poly2 length',
    @geogPoly1.STLength() + @geogPoly2.STLength() AS 'combined length',
    @geogColl.STLength() AS 'geometrycoll length',
    @geogPoly1.STArea() AS 'poly1 area',
    @geogPoly2.STArea() AS 'poly2 area',
    @geogPoly1.STArea() + @geogPoly2.STArea() AS 'combined area',
    @geogColl.STArea() AS 'geomcoll area'
    
    



    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Friday, June 10, 2011 7:04 AM
    Answerer

All replies

  • I agree there's a discrepancy between the behaviour of the two datatypes - attached below is a script to demonstrate. However, I don't get quite the same results as you - I find that the results of STLength() for 'A' is neither the same as the aggregate or union length of the individual elements.

    As for what is "correct" - looking at the OGC specs (which are notoriously vague in places), I can't see any mention of how, or even if, the Area() method is implemented on a GeometryCollection. Section 7.2.16 of OGC 05-134 lists NumGeometries and GeometryN as the only defined SQL functions on a GeomCollection.

    The Area function is defined for Surface and MultiSurface types, where is is helpfully described that it "returns the area (of x)"...

     

    DECLARE @Poly1WKT nvarchar(255) = 'POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))';
    DECLARE @Poly2WKT nvarchar(255) = 'POLYGON((8 0, 12 0, 12 2, 8 2, 8 0))';
    DECLARE @GeomCollWKT nvarchar(255) = 
    'GEOMETRYCOLLECTION(
     POLYGON((0 0, 10 0, 10 5, 0 5, 0 0)),
     POLYGON((8 0, 12 0, 12 2, 8 2, 8 0))
     )';
    
    DECLARE @geomPoly1 geometry = @Poly1WKT;
    DECLARE @geomPoly2 geometry = @Poly2WKT;
    DECLARE @geomColl geometry = @GeomCollWKT;
    DECLARE @geogPoly1 geography = @Poly1WKT;
    DECLARE @geogPoly2 geography = @Poly2WKT;
    DECLARE @geogColl geography = @GeomCollWKT;
    
    SELECT
    'geometry (A)' AS 'datatype',
    @geomPoly1.STLength() AS 'poly1 length',
    @geomPoly2.STLength() AS 'poly2 length',
    @geomPoly1.STLength() + @geomPoly2.STLength() AS 'combined length',
    @geomColl.STLength() AS 'geometrycoll length',
    @geomPoly1.STArea() AS 'poly1 area',
    @geomPoly2.STArea() AS 'poly2 area',
    @geomPoly1.STArea() + @geomPoly2.STArea() AS 'combined area',
    @geomColl.STArea() AS 'geomcoll area'
    
    UNION ALL SELECT
    'geography (B)',
    @geogPoly1.STLength() AS 'poly1 length',
    @geogPoly2.STLength() AS 'poly2 length',
    @geogPoly1.STLength() + @geogPoly2.STLength() AS 'combined length',
    @geogColl.STLength() AS 'geometrycoll length',
    @geogPoly1.STArea() AS 'poly1 area',
    @geogPoly2.STArea() AS 'poly2 area',
    @geogPoly1.STArea() + @geogPoly2.STArea() AS 'combined area',
    @geogColl.STArea() AS 'geomcoll area'
    
    



    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Friday, June 10, 2011 7:04 AM
    Answerer
  • Many thanks for the quick reply. I went back and recaclulated my results and indeed, for 'A'  I also get neither the same as the aggregate or union length of the individual elements, using my shapes (close but not quite equal to the union).

    For 'B', your example confirms:

    (@geogPoly1.STUnion(@geogPoly2)).STLength() = @geogColl.STLength()


    I suppose the best way to handle this is to calculate the lengths/areas individually for each element in a collection and then aggregate them (much like you did in your example).


    Friday, June 10, 2011 11:54 AM