Intersection of 2 Linestrings (not interpolated)

Question

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).

Answer 1

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.

Answer 2

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;

Answer 3

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.