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?
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With