Selecting Nodes Separating Line Segments

Question

I want to select nodes separating line segments in a layer. I want to select nodes only where they are intersected by two lines, NOT when they meet with more than two line (e.g. a T intersection or four way intersection, etc.).

Here's the best picture I can give (I dont have the reputation to post pictures). The --- line on the left is the first segment and the --x--x--x line on the right the second. The O is the node in the middle I want to select.

--------------------------------------0--x---x--x---x---x---x--x--x--x--x--x--x--x

I do NOT want to select nodes where more than two lines touch the node.

So far I have tried this query

CREATE TABLE contacts_st_touching_faults as
SELECT ST_Intersection(a.the_geom, b.the_geom), Count(Distinct a.gid) = 2
FROM final_layer as a, final_layer as b
WHERE ST_Touches(a.the_geom, b.the_geom)
AND a.gid != b.gid
GROUP BY ST_Intersection(a.the_geom, b.the_geom)

When I run this query it gives me intersections with more than two lines intersecting (T intersections and 4 way intersections).

I have also tried subing ST_intersects in and that didn't seem to work as well as ST_touches, but if you know how to make them work or any other method, it would be much appreciated!

Thanks for the help!

Answer 1

This should work:

WITH contacts AS(
SELECT a.gid AS gid1,b.gid AS gid2, ST_Intersection(a.the_geom, b.the_geom) AS intersection
FROM final_layer as a, final_layer as b
WHERE ST_Touches(a.the_geom, b.the_geom)
AND a.gid<b.gid
)
SELECT *
FROM contacts c1
LEFT JOIN contacts c2
  ON ((c1.gid1=c2.gid1 AND c1.gid2<>c2.gid2) OR (c1.gid1=c2.gid2 AND c1.gid1<>c1.gid2))
  AND c1.intersection=c2.intersection
WHERE c2.gid1 IS NULL;

It will perform better if ST_Intersection is moved to the final query but I wanted to make it simple.

Answer 2

This will list nodes with tow lines intersecting.

    SELECT array_agg(gid) AS gids, count(gid) AS count, geom
    FROM 
      -- lists all vertices (points) from lines
      (SELECT gid, (ST_DumpPoints(geom)).geom AS geom
      FROM lines_layer) AS p
    GROUP BY p.geom
    HAVING count(gid) = 2
    ORDER BY count(gid);

For all nodes, replace '= 2' with '> 1'