locked
select smallest polygon point falls in RRS feed

  • Question

  • How do I return the smallest or inner most polygon a point falls in? If i have two or more separate polygons, one inside the other (like rings on a target) and a point at the center, the point is within more than one polygon. How do I query to get only the the inner most polygon containing the center point?
    Tuesday, May 11, 2010 10:32 PM

Answers

  • The innermost polygon will be the one of smallest area that intersects the point, so something like this?

     

    SELECT TOP 1 Polygon
    FROM Table
    WHERE Polygon.STIntersects(Point) = 1
    ORDER BY Polygon.STArea() ASC

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Edited by tanoshimiEditor Wednesday, May 12, 2010 7:03 AM oops - got sort order the wrong way round!
    • Marked as answer by TheGriff Wednesday, May 12, 2010 3:22 PM
    Tuesday, May 11, 2010 10:41 PM
    Answerer

All replies

  • The innermost polygon will be the one of smallest area that intersects the point, so something like this?

     

    SELECT TOP 1 Polygon
    FROM Table
    WHERE Polygon.STIntersects(Point) = 1
    ORDER BY Polygon.STArea() ASC

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Edited by tanoshimiEditor Wednesday, May 12, 2010 7:03 AM oops - got sort order the wrong way round!
    • Marked as answer by TheGriff Wednesday, May 12, 2010 3:22 PM
    Tuesday, May 11, 2010 10:41 PM
    Answerer
  • declare @point geometry = '..'
    
    SELECT top 1 [geo]
     FROM [Table]
     where geo.STIntersects(@point)=1
     order by geo.STArea() asc
    Wednesday, May 12, 2010 6:24 AM
  • Sorry I didn't see your answer 
    Wednesday, May 12, 2010 6:33 AM