find number of polygons a line intersects

Question

I am working with SQL Server and spatial types. Have found the very interesting. I have reached a situation and I am not sure how I might work it out. Let me outline

I have 2 points (coordinates) eg. 3,9 and 50, 20 - this is a straight line, joining such.

I have multiple polygons (50 +).

I want to be able to calculate how many polygons that the above line passes through. What I mean by pass through, when I join the 2 coordinates, how many polygons the line intersects? I want to work this out with a SQL query.

Please let me know if not clear - it is difficult to explain!

Answer 1

Based on your coordinates, I'm assuming geometry (as opposed to geography), but the approach should hold regardless. If you have a table dbo.Shapes that has a Shape column of type geometry and each row contains one polygon, this should do:

declare @line geometry = geometry::STLineFromText('LINESTRING(3 9, 50 20)', 0);

select count(*)
from dbo.Shapes as s
where s.shape.STIntersects(@line) = 1;

If you want to know which polygons intersect, just change the count(*) to something more appropriate.