0

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?

Pete Yorn
  • 3
  • 2

1 Answers1

0

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.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • At the first approach I never thought that the SQL version could be an issue, but after investigating on your reply, I found that STNumCurves() only works on SQL Server 2012 and I'm using SQL Server 2008 (not possible to upgrade)... Do you think that this could be obtained by another method? – Pete Yorn May 29 '15 at 16:33
  • Maybe. Looking at the list of methods available in 2008 (https://msdn.microsoft.com/en-us/library/bb933917%28v=sql.105%29.aspx), the closest we have is `STGeometryN()` and `STNumGeometries()`. You could make those work if you are willing to define @DF as a MULTILINESTRING instead of a LINESTRING. It's kind of a pain to convert one to another; right now the linstring def looks like a → b → c whereas the multilinestring would be a → b, b → c, but the two definitions should be equivalent. – Ben Thul May 29 '15 at 17:37
  • Long story short: there were significant improvements in 2012 to spatial. If you have the option to use that, do it. – Ben Thul May 29 '15 at 17:40