5

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

Community
  • 1
  • 1
duber
  • 2,769
  • 4
  • 24
  • 32
  • Does this do what you want. http://stackoverflow.com/questions/7595635/how-to-convert-polygon-data-into-line-segments-using-postgis? – John Powell Sep 10 '14 at 06:12

1 Answers1

12

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);
duber
  • 2,769
  • 4
  • 24
  • 32