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?