0

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!

alrikai
  • 4,123
  • 3
  • 24
  • 23
Cindy
  • 3
  • 1

2 Answers2

0

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.

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • Awesome thanks for making it simple! I could even understand what's going on! The query is running without error, however, I still get no selection. The query makes sense to me so I'm not sure why its not selecting. Also did you mean AND a.gid<>b.gid --prevents duplicates? Thanks! – Cindy Apr 24 '13 at 15:54
  • 1
    No, I meant a.gid – Jakub Kania Apr 24 '13 at 16:04
  • @Cindy It should be `LEFT JOIN` not `JOIN` certainly, fixed that. – Jakub Kania Apr 24 '13 at 16:32
  • Hey thanks again, now I get results, but its still selecting T intersections and 4 way intersections. I uploaded a image of the results: http://s1277.photobucket.com/user/Cindy466/media/Capture_zps61531176.jpg.html – Cindy Apr 24 '13 at 16:56
  • It is selecting the two way intersections which is great! The other query I was doing couldn't even do that. – Cindy Apr 24 '13 at 16:57
  • And I uploaded the selection as another layer so the selection is the nodes and I put the original layer overtop for reference – Cindy Apr 24 '13 at 17:01
  • @Cindy I think I finaly fixed it, there was a missing bracket in ON logic. Well, at least it now works well with my test data though there still can be something I haven't thought about. – Jakub Kania Apr 24 '13 at 17:50
  • Thanks for all the help! I still get multiple intersections unfortunately as well as the nodes I want so its still very useful. Thanks again! – Cindy Apr 24 '13 at 19:46
0

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'

user157116
  • 1