1

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.

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.

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

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';

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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