locked
Cannot convert nested polygons from geometry to geography. RRS feed

  • Question

  •  

     

    In the example below, there is a smaller polygon nested within the larger polygon.    The intent is for the smaller, nested polygon to define an area to be excluded.     It works just fine as a geometry datatype, but fails when converted to a geography type. 

    I know the syntax of the polygon statement is correct, because it works as a geometry.   

    I know the two polygons are valid geographies standing alone.

    What am I missing?

    --------

    declare @poly1 geometry
    set @poly1 =
    '
    POLYGON
    (

    (-118.07169914245605 34.56580707468465
                    ,-118.0770206451416 34.556900989085214
                    ,-118.06963920593263 34.55025614542689
                    ,-118.06191444396974 34.55216482505817
                    ,-118.05830955505372 34.56008184341472
                    ,-118.07169914245605 34.56580707468465)
    ,  (-118.072 34.557
                    ,-118.07 34.555
                    ,-118.068 34.557
                    ,-118.07 34.562
                    ,-118.072 34.557)

    )
    '
    select @poly1



    set @poly1 = 'POLYGON(
      (-118.07169914245605 34.56580707468465
                    ,-118.0770206451416 34.556900989085214
                    ,-118.06963920593263 34.55025614542689
                    ,-118.06191444396974 34.55216482505817
                    ,-118.05830955505372 34.56008184341472
                    ,-118.07169914245605 34.56580707468465)

       )'
    select @poly1

    set @poly1 = 'POLYGON(
      (-118.072 34.557
                    ,-118.07 34.555
                    ,-118.068 34.557
                    ,-118.07 34.562
                    ,-118.072 34.557)
       )'
    select @poly1
    Thursday, July 29, 2010 7:35 PM

Answers

  • The problem is the orientation of the interior ring. For geography, exterior ring must be counterclockwise and interior must be clockwise. 

     

    declare @poly1 geography
    set @poly1 =
    '
    POLYGON
    (
    
    (-118.07169914245605 34.56580707468465
            ,-118.0770206451416 34.556900989085214
            ,-118.06963920593263 34.55025614542689
            ,-118.06191444396974 34.55216482505817
            ,-118.05830955505372 34.56008184341472
            ,-118.07169914245605 34.56580707468465)
    , (-118.072 34.557
            ,-118.07 34.562
            ,-118.068 34.557
            ,-118.07 34.555
            ,-118.072 34.557)
    
    )
    '
    select @poly1
    

    • Marked as answer by Bob Hovious Thursday, July 29, 2010 9:00 PM
    Thursday, July 29, 2010 8:18 PM
  • What am I missing?

    One word: "ring orientation". (well, ok, that's two words).

    Since the inner polygon defines an area of space that you want to exclude from the polygon, points of the inner polygon should be listed in a clockwise direction, whereas points in the outer polygon should be listed in a counterclockwise direction.

    declare @poly1 geography
    set @poly1 =
    '
    POLYGON
    (
     (-118.07169914245605 34.56580707468465
      ,-118.0770206451416 34.556900989085214
      ,-118.06963920593263 34.55025614542689
      ,-118.06191444396974 34.55216482505817
      ,-118.05830955505372 34.56008184341472
      ,-118.07169914245605 34.56580707468465)
    , 
     (-118.072 34.557
      ,-118.07 34.562
      ,-118.068 34.557
      ,-118.07 34.555
      ,-118.072 34.557)
    )
    '
    select @poly1
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Bob Hovious Thursday, July 29, 2010 9:01 PM
    Thursday, July 29, 2010 8:18 PM
    Answerer

All replies

  • The problem is the orientation of the interior ring. For geography, exterior ring must be counterclockwise and interior must be clockwise. 

     

    declare @poly1 geography
    set @poly1 =
    '
    POLYGON
    (
    
    (-118.07169914245605 34.56580707468465
            ,-118.0770206451416 34.556900989085214
            ,-118.06963920593263 34.55025614542689
            ,-118.06191444396974 34.55216482505817
            ,-118.05830955505372 34.56008184341472
            ,-118.07169914245605 34.56580707468465)
    , (-118.072 34.557
            ,-118.07 34.562
            ,-118.068 34.557
            ,-118.07 34.555
            ,-118.072 34.557)
    
    )
    '
    select @poly1
    

    • Marked as answer by Bob Hovious Thursday, July 29, 2010 9:00 PM
    Thursday, July 29, 2010 8:18 PM
  • What am I missing?

    One word: "ring orientation". (well, ok, that's two words).

    Since the inner polygon defines an area of space that you want to exclude from the polygon, points of the inner polygon should be listed in a clockwise direction, whereas points in the outer polygon should be listed in a counterclockwise direction.

    declare @poly1 geography
    set @poly1 =
    '
    POLYGON
    (
     (-118.07169914245605 34.56580707468465
      ,-118.0770206451416 34.556900989085214
      ,-118.06963920593263 34.55025614542689
      ,-118.06191444396974 34.55216482505817
      ,-118.05830955505372 34.56008184341472
      ,-118.07169914245605 34.56580707468465)
    , 
     (-118.072 34.557
      ,-118.07 34.562
      ,-118.068 34.557
      ,-118.07 34.555
      ,-118.072 34.557)
    )
    '
    select @poly1
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Bob Hovious Thursday, July 29, 2010 9:01 PM
    Thursday, July 29, 2010 8:18 PM
    Answerer
  • Understood, and this rule is applicable to the geography datatype only?   (I'll make it a habit to do it with either.)

    So, first define the area to be included (counterclockwise), and then define points to be excluded (clockwise).

    Thanks, guys.

     

     

    Thursday, July 29, 2010 8:29 PM
  • Yes, geography datatype only. For geometry there's no ambiguity as to what's "inside" and what's "outside" a polygon, so ring orientation is unimportant.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, July 29, 2010 9:07 PM
    Answerer