Identifying road intersections using PostGIS

Question

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.

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
SELECT      
    ST_Intersection(a.geom, b.geom),
    a.gid
FROM
    roads as a,
    roads as b
WHERE
    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):

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

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?

Answer 1

If you group, you should get only unique points.

CREATE TABLE test_points as
SELECT      
    ST_Intersection(a.geom, b.geom),
    Count(Distinct a.gid)
FROM
    roads as a,
    roads as b
WHERE
    ST_Touches(a.geom, b.geom)
    AND a.gid != b.gid
GROUP BY
    ST_Intersection(a.geom, b.geom)
;

Answer 2

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
   ,roads 
 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.

HTH

Nicklas

Answer 3

 CREATE TABLE test_points as
    SELECT      
        ST_Intersection(a.geom, b.geom),
        Count(Distinct a.gid)
    FROM
        roads as a,
        roads as b
    WHERE
        ST_Touches(a.geom, b.geom)
        AND a.gid < b.gid   /* !!! Changed "!=" for "<"  */
    GROUP BY
        ST_Intersection(a.geom, b.geom)
    ;

If line A (id 1) crosses line B (id 2) it's a point of intersection we need. But line B also crosses line A in the same point. But we don't need this point twice. That's why I'm using a.gid < b.gid instead of a.gid != b.gid