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