2

I have two tables point and roads in my PostgreSQL 9.5 database. Some of the points lie on the intersections of three roads (line segments). The sample scenario is as follows:

sample scenario

I want to identify all such points that lie on the intersection of three roads. I came across this question on GIS-SE, but the code returned me all points on the intersections of individual line segments which is not desired. Can someone suggest me how do I identify the points that lie on the intersection of three segments (street crossing) only?

2

Something along the lines of a join on ST_Intersects, where you put the count of road IDs in a where clause, and group by node_id.

SELECT count(road_id) as cnt, node_id
  FROM nodes n
    INNER JOIN roads r ON ST_Intersects(n.geom, r.geom)
  GROUP BY node_id
  HAVING count(road_id) = 3;

Intersecting lines and points can be problematic, depending on rounding/precision issues, so you might need to combine this with ST_SnapToGrid, ie, snap both the points and lines to the same precision grid.

EDIT: As @ziggy has pointed out, you need to use HAVING not WHERE when using aggregates as a condition.

This is untested, so if you could provide some sample data, it would help.

  • untested comment here but shouldnt you use the having clause instead of the agg function in the where clause? – ziggy Sep 15 '17 at 11:58
  • @ziggy, probably, yes. – John Powell Sep 15 '17 at 12:03
  • Tested comment: Thanks @JohnPowellakaBarça for your help. You need to place "Group by" clause before "Having" clause for this query to work. I was getting syntax error with this version of your query. I fixed it and it seems to be giving me points as desired. – khajlk Sep 15 '17 at 12:37
  • Uf, need more coffee. Edited question. Please accept it, if it works, even if you deserve half the credit for debugginng it :-) – John Powell Sep 15 '17 at 12:45
  • 1
    I had coffee with me when I fixed it ;) Accepted. – khajlk Sep 15 '17 at 12:51

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.