1

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

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.

  • Great! That helped me a lot. With your help, I got my query finished. It may not be perfect and the query runs long. But the result is as expect. See my Answer for the query – Solinar Dec 2 '15 at 10:07
1

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;
  • I think that is not, what I am searching for. I want to compare 2 linestrings and return the points which exist on both linestrings. I don't want to count any points over all recordsets. – Solinar Dec 2 '15 at 7:57
  • It is not far from what you want. Just edit the subquery so that it dumps only your two input linestrings into points instead of the whole table. – user30184 Dec 2 '15 at 8:32
  • You can put those two linestrings into the table. This solution would work for any number of lines. – Michal Zimmermann Dec 2 '15 at 8:55
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.