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.

2 Answers 2


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.

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

This post is a good way to resolve this problem.

CREATE OR REPLACE FUNCTION compute_network() RETURNS text as $$
streetRecord record;
pointIndex integer;
geomFragment record;
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));
return 'Done';
$$ LANGUAGE 'plpgsql';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.