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?

khajlk
  • 1,081
  • 2
  • 8
  • 25

1 Answers1

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.

John Powell
  • 13,649
  • 5
  • 46
  • 62
  • 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