# Identifying road intersections using PostGIS

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
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?

-

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
WHERE
ST_Touches(a.geom, b.geom)
AND a.gid != b.gid
GROUP BY
ST_Intersection(a.geom, b.geom)
;
``````
-
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
See @AlexOs suggested modification gis.stackexchange.com/a/151277/3195 – Martin F Jun 18 '15 at 15:50

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
WHERE ST_Intersects(a.geom, b.geom)
) all_crosspoints
) distinct_crosspoints
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

-
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
`````` CREATE TABLE test_points as
SELECT
ST_Intersection(a.geom, b.geom),
Count(Distinct a.gid)
FROM
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`