CODEWITHSUNDEEP
sql-serversqlgeography
AAsk
AAsk

Reputation: 1491

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?

Upvotes: 1

Views: 83

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

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.

Upvotes: 3

mendosi
mendosi

Reputation: 2051

Try splitting the single multi-polygon row into many, single-polygon rows in a query and then doing the intersect, that will return only matching rows.

I haven't done anything like that myself, but this link might help https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d99cef8e-d345-44ee-87e1-f9d4df851c35/multipolygon-results-split-into-polygons?forum=sqlspatial

Upvotes: 0

Related Questions