Cutting OSM roads in sections using PostGIS

Question

I define a section by a part of a road that has no intersection. See image below: enter image description here

I'd like to extract all sections from all the roads of my PostGIS database. This GIS database was created with osm2pgsl:

               List of relations
 Schema |        Name        | Type  |  Owner
--------+--------------------+-------+----------
 public | geography_columns  | view  | postgres
 public | geometry_columns   | table | gis
 public | planet_osm_line    | table | postgres
 public | planet_osm_nodes   | table | postgres
 public | planet_osm_point   | table | postgres
 public | planet_osm_polygon | table | postgres
 public | planet_osm_rels    | table | postgres
 public | planet_osm_roads   | table | postgres
 public | planet_osm_ways    | table | postgres
 public | spatial_ref_sys    | table | gis
(10 rows)

I've read an interesting post about detecting the intersections but I can't figure out how to create a table with sections.

Any help appreciated.

score 1 · Answer 1 · answered Jul 05 '13 at 15:36

1

An alternative is to use the well referenced software OSM2PO which will split all the OSM ways into distinct road sections : you can check http://osm2po.de . Produced road sections (links) can be directly imported into psql for routing.

answered Jul 05 '13 at 15:36

user19544

21
2

thx, I'm checking this out. – Mermoz Jul 05 '13 at 16:30
the main problem about osm2po is that it is neither open source nor well documented. – Mermoz Jul 07 '13 at 12:38

Mermoz · Accepted Answer · 2013-07-11T17:21:18.857

This post is a good way to resolve this problem.

CREATE OR REPLACE FUNCTION compute_network() RETURNS text as $$
DECLARE
streetRecord record;
pointIndex integer;
geomFragment record;
BEGIN
FOR streetRecord in select way, osm_id, name from planet_osm_line where highway is not null LOOP
 FOR pointIndex in 1..ST_NPoints(streetRecord.way)-1 LOOP
  select st_makeline(st_pointn(streetRecord.way, pointIndex), st_pointn(streetRecord.way, pointIndex+1)) as way into geomFragment;
  insert into network(osm_id, name, the_geom, length) values(streetRecord.osm_id, streetRecord.name, geomFragment.way, st_length(geomFragment.way));
 END LOOP;
END LOOP;
return 'Done';
END;
$$ LANGUAGE 'plpgsql';

Cutting OSM roads in sections using PostGIS

2 Answers2