Search code examples
sql-serversqlgeography

TSQL - Geography: Which polygon?

Using SQL Server, when I get a result of 1 for the expression @multipolygon.STIntersects(@points), indicating that the point is within one of the polygons comprising the multi-polygon

is there a way of finding out which polygon inside the many within the multi-polygon actually contains the point?

Solution

  • I've used something like this before:

    select *
from dbo.Numbers as n
where @point.STIntersects(@multipolygon.STGeometryN(n.Number)) = 1
   and n.Number <= @multipolygon.STNumGeometries();

    Where dbo.Numbers is a tally table. This query will return a 1-based index of which polygon(s) matched. If you want the polygons themselves as well, add STGeometry(n.Number) to the select list.