Calculate the distance a line intersects lines

Question

I hope I have enough detail here to make some sense!

I have created a table containing Geom Lines in a column. The table was populated using these inputs:

INSERT INTO tblLines ("geomLines") VALUES (geometry::STGeomFromText('LINESTRING (10 15, 100 100, 120 79, 90 40, 80 5)', 0));
INSERT INTO tblLines ("geomLines") VALUES (geometry::STGeomFromText('LINESTRING (75 25, 50 150, 120 120, 135 40)', 0));

Resulting in table tblLines looking like:

fidID     geomLines
1         wkt
2         wkt

I have then created on the fly line AB:

SET @AB = geometry::STGeomFromText('LINESTRING (60 40, 100 140)', 0);

So to summarise all my data looks like this:

I can now query using:

DECLARE @AB GEOMETRY = geometry::STGeomFromText('LINESTRING (60 40, 100 140)', 0);
DECLARE @A GEOMETRY = geometry::STGeomFromText('POINT (60 40)', 0);
-- Combined multipoint value
DECLARE @ABINT GEOMETRY = (SELECT  GEOMETRY::UnionAggregate(@AB.STIntersection(geomLines)) FROM tblLines WHERE @AB.STIntersects ( geomLines ) = 1);

SELECT * 
FROM tblLines
WHERE @ABINT.STDistance(geomLines) < 0.01
ORDER BY @A.STDistance(@ABINT) ASC;

This produces a rather nice summary of the lines AB crosses, but it does fail on two things:

1 - It does not show duplicates as would be expected for "2"

2 - @A.STDistance(@ABINT) is always the measurement from A to the first multipoint, not each multipoint as I would hope.

I am hoping to get to this:

[Intersected Line] [Distance along AB]
2                  18
1                  25
2                  60

Does anyone have any hints?

score 0 · Accepted Answer · answered Jun 10 '20 at 22:15

Test Data

CREATE TABLE #tblLines( geomLines GEOMETRY )
INSERT INTO #tblLines (geomLines) VALUES (geometry::STGeomFromText('LINESTRING (10 15, 100 100, 120 79, 90 40, 80 5)', 0));
INSERT INTO #tblLines (geomLines) VALUES (geometry::STGeomFromText('LINESTRING (75 25, 50 150, 120 120, 135 40)', 0));

Solution

DECLARE @AB GEOMETRY = geometry::STGeomFromText('LINESTRING (60 40, 100 140)', 0);
-- Combined multipoint value
SELECT  GEOMETRY::UnionAggregate(@AB.STIntersection(geomLines)) FROM #tblLines WHERE @AB.STIntersects ( geomLines ) = 1

Credit goes to https://stackoverflow.com/a/8247981/6305294

RE: "That's a wonderful start" - Did I answer all your questions or is there something that I missed? — Alex, Jun 11 '20 at 22:59

Calculate the distance a line intersects lines

1 Answers1