Take the 2-minute tour ×

I'm trying to identify where roads intersect each other, and to make a point at this intersection, with the number of roads that form the intersection listed.

enter image description here

I was wondering if there was some way of using ST_NumPoints to achieve this but I can't quite figure out what I should be doing. What I have done is to create a table of points where the lines intersect using the following code:

CREATE TABLE test_points as
    ST_Intersection(a.geom, b.geom),
    roads as a,
    roads as b
    ST_Touches(a.geom, b.geom);

If I run this on a sample of roads I get the following grid of points (the roads are shown for illustration):

enter image description here

If I inspect one of the points, I see that there are many points stacked on top of each other:

enter image description here

The GID here is the road ID, but I don't understand why there are some many points. I can understand 4 points being counted for a central road intersection, but there are 12 points listed here. Is there a better way to perform this calculation in PostGIS?

share|improve this question

2 Answers 2

up vote 11 down vote accepted

If you group, you should get only unique points.

CREATE TABLE test_points as
    ST_Intersection(a.geom, b.geom),
    Count(Distinct a.gid)
    roads as a,
    roads as b
    ST_Touches(a.geom, b.geom)
    AND a.gid != b.gid
    ST_Intersection(a.geom, b.geom)
share|improve this answer
Just a notice, grouping by geometry results in grouping by the bbox of the geometry, not the geometry itself. That doasn't matter when dealing with points. Well, almost. Bboxes has less precision than the point itself which in theory can lead to grouping two points together that are not identical. –  Nicklas Avén Feb 24 '12 at 23:25
Thanks @NicklasAvén. How precise is Bbox comparison? I'd expect it to be sufficient for this use case. –  underdark Feb 25 '12 at 10:49
Thanks @underdark. Do you know how I can count the number of lines that are intersecting? I've tried a few combinations of COUNT() such as COUNT(ST_Touches(..)) and COUNT(ST_Intersection(..)) but this does not seem to work as all of the values are 12. –  djq Feb 25 '12 at 14:31
@underdark, yes it is absolutely enough, that is why I wrote "in theory". The box is in float4 and the coordinates of the point is in double precision. So the box will look the same for ST_Point(1.000001,1.0) and ST_Point(1.000002,1.0) (At least at my system, I just tried. It groups the to points together). THis difference between box and real geometry has been a disussion for some time at dev-list. –  Nicklas Avén Feb 25 '12 at 19:53

This is a little trickier than you might expect. That is because there is no good way in analyzing relations for more than pairs. You cannot put three lines into a function and ask if all of them intersect.

But, at least one approach could be to first find the crossings, then check how many roads are touching at each crossing (it can all be done in the same query).

If your roads connect perfectly to each other, and there are no roads passing by a crossing, then you could do something like this (not tested):
edited with forgotten group clause (still not tested):

SELECT distinct_crosspoints.geom as crossing, array_agg(roads.gid), count(*) FROM
  (SELECT DISTINCT (geom) geom FROM 
    (SELECT ST_Intersection(a.geom, b.geom) geom 
     FROM roads a, roads b 
     WHERE ST_Intersects(a.geom, b.geom)
    ) all_crosspoints
   ) distinct_crosspoints
 WHERE ST_Intersects(distinct_crosspoints.geom, roads.geom)
 GROUP BY distinct_crosspoints.geom;

If the roads are not connected properly and/or some roads pass by a crossing it is more complicated.



share|improve this answer
Hi @Nicklas, I'm not able to get this to run. The two inner clauses work fine; should I be replacing the distinct_crosspoints ,roads with my table name (roads_test)? I did try that but then got an error about geom being ambiguous. –  djq Feb 25 '12 at 21:21
@celenius, Sorry I had forgotten the group clause. I also see that you don't need to put distinct on an extra level. You can just put it on the intersection directly. Note that Distinct have the same behavior as group by according to the discussion under underdarks answer. –  Nicklas Avén Feb 26 '12 at 20:12
I added the distinct_crosspoints.geom to Nicklas answer to get the query running. Works now for me. –  Frank Feb 23 '13 at 22:39

Your Answer


