More

Intersection of 2 Linestrings (not interpolated)

Intersection of 2 Linestrings (not interpolated)


I am struggling with a query in POSTGIS

I want to find linestring geometries where the geometries are containing the same point.

I don't want to calculate the intersection points. I only want to get a point, if the point is a real member of both geometries.

Example

SELECT ST_AsText(ST_Function(ST_GeomFromText('LINESTRING(1 2, 2 2, 0 0)'), ST_GeomFromText('LINESTRING(0 1, 2 2, 3 0)') ) )

should return

POINT(2 2)

and

SELECT ST_AsText(ST_Function(ST_GeomFromText('LINESTRING(1 2, 10 10)'), ST_GeomFromText('LINESTRING(1 1, 2 2, 3 3)') ) )

should return

EMPTY

or something similiar for empty.

How can I achieve that in a single query?

I tried to use ST_Intersection(), but this one calculates the point (which is wrong to my requirements).


Here is a query that returns vertices which are common for your input linestrings:

select ST_AsText(ST_Intersection(subq.g1,subq.g2)) from (select ST_Collect(dump1.geom) as g1, ST_Collect(dump2.geom) as g2 from ST_DumpPoints(ST_GeomFromText('LINESTRING(1 2, 2 2, 0 0)')) as dump1, ST_DumpPoints(ST_GeomFromText('LINESTRING(0 1, 2 2, 3 0)')) as dump2) as subq;

Result:

"POINT(2 2)"

Query is dumping the vertices of the linestrings into points and collects them into multipoints, and finally finds the intersections of the multipoints.


This one gives you all the points that occur more than once in the linestrings.

BEGIN; CREATE TEMPORARY TABLE test ( id serial, wkb_geometry geometry(LINESTRING, 4326) ) ON COMMIT DROP; INSERT INTO test(wkb_geometry) VALUES (ST_GeomFromText('LINESTRING(1 2, 2 2, 0 0)', 4326)), (ST_GeomFromText('LINESTRING(0 1, 2 2, 3 0)', 4326)); SELECT ST_AsText((dp).geom), COUNT(1) FROM ( SELECT (ST_DumpPoints(wkb_geometry)) dp FROM test ) a GROUP BY ST_AsText((dp).geom) HAVING COUNT(1) > 1; COMMIT;

With the help of user30184, I was able to create my query. Here it is:

WITH Data AS(

SELECT a.id as ID1, b.id as ID2, a.way as Geom1, b.way as Geom2 FROM ways AS a JOIN ways AS b ON not(ST_Equals(a.way, b.way))

)

SELECT ID1, ID2, ST_ASTEXT(ST_Intersection(subq.g1,subq.g2)) AS Intersection, ST_LineLocatePoint(Geom1, ST_Intersection(subq.g1,subq.g2)) AS Geom1Percent, ST_LineLocatePoint(Geom2, ST_Intersection(subq.g1,subq.g2)) AS Geom2Percent, ST_GEOMETRYTYPE(Geom1) FROM Data,

(

SELECT ST_Collect(dump1.geom) AS g1, ST_Collect(dump2.geom) AS g2 FROM Data, ST_DumpPoints(Geom1) as dump1, ST_DumpPoints(Geom2) as dump2

) AS subq

WHERE ST_INTERSECTS(subq.g1, subq.g2)

AND

Concat(ST_GEOMETRYTYPE(Geom1), ST_GEOMETRYTYPE(Geom2)) = 'ST_LineStringST_LineString'

That query may not be very good at performance, but gives the result I expect.