Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I divide city streets by intersection using PostGIS?

I want to use PostGIS to break up OpenStreetMap streets by intersection. I'm trying to put a city's streets into a graph structure, with nodes made by intersections and edges drawn by roads.

I'm currently using the ST_Intersection command carves up a road into all possible permutations of its components.

For a road with three intersections, for example (the second horizontal line from the top below), I want four pieces of separated road, but instead I get every possible combination of the road for the length of three blocks, two blocks, and one block. How do I simply get all the roads divided by each individual block?

intersections

like image 277
duber Avatar asked Dec 19 '22 11:12

duber


1 Answers

There are two options for this-

1) Use pgRouting, which takes into account things like the vertical differences between roads (like tunnels and overpasses)

2) Use this SQL code, where dc_streets_subset is the OSM data for DC.

--Get a list of all intersections in city
CREATE TABLE dc_intersections AS 
SELECT DISTINCT (ST_DUMP(ST_INTERSECTION(a.geom, b.geom))).geom AS ix 
FROM dc_streets_subset a 
INNER JOIN dc_streets_subset b 
ON ST_INTERSECTS(a.geom,b.geom)
WHERE geometrytype(st_intersection(a.geom,b.geom)) = 'POINT';

CREATE INDEX ON dc_intersections USING gist(ix);

CREATE TABLE dc_union AS 
SELECT ST_UNION(geom) as geom
FROM dc_streets_subset;

CREATE INDEX ON dc_union USING gist(geom);

CREATE TABLE dc_segments AS
SELECT (ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom
FROM dc_union a
INNER JOIN dc_intersections b
ON ST_INTERSECTS(a.geom, b.ix);
like image 124
duber Avatar answered Dec 28 '22 07:12

duber