SQL Spatial number of times a Linestring intersects other Linestrings

Question

So, I have a table named "lines" that has a field named "WKT" with the corresponding geography.

I builded a query that gives me the name of the line that was intersected, like this:

DECLARE @DF GEOGRAPHY  
Set @DF=GEOGRAPHY::STLineFromText('LINESTRING(-9.564498 52.237100,-9.564906 52.243924,-9.565699 52.245563,-9.568173 52.251014,-9.567142 52.257567,-9.564291 52.262366,-9.563453 52.262972,-9.563447 52.262980,-9.563447 52.262980,-9.563447 52.262980)', 4326)  
select name, @DF.STIntersects(WKT) AS inters
from lines WHERE @DF.STIntersects(WKT)=1

And it works very well. The problematic case is when this @DF line is a "go and return by the same path". In this case I wanted to know the names of the repeated lines that were intersected.

Is this possible to do?

Answer 1

Sure. Assuming that you've got a tally table (i.e. a table with numbers 1 through some large number):

DECLARE @DF GEOGRAPHY  
Set @DF=GEOGRAPHY::STLineFromText('LINESTRING(-9.564498 52.237100,-9.564906 52.243924,-9.565699 52.245563,-9.568173 52.251014,-9.567142 52.257567,-9.564291 52.262366,-9.563453 52.262972,-9.563447 52.262980,-9.563447 52.262980,-9.563447 52.262980)', 4326)  

WITH cte AS (
    SELECT n, @DF.STCurveN(n) AS curve
    FROM dbo.Numbers
    WHERE n <= @DF.STNumCurves()
)
select name, @DF.STIntersects(WKT) AS inters
from lines AS l
JOIN cte AS c
    ON l.WKT.STIntersects(c.curve) = 1

I'm sure that you could get away without using the CTE, but that was what made the most sense in my head.