problem to use openstreetmap data file with pgrouting2.0 on PostgreSql9.3

Question

I'm a newcomer on pgrouting, and I want to test its functionalities.

I've installed and configured my computer with Postgresql9.3, pgrouting2, and PostGIS.

I've downloaded from openstreetmap the .osm file of the zone I need (4.0706 North, 9.7321 East, 3.9972 South, 9.6737 West).

My problem is that it comes with 4 layers: points, lines, multipolygons, and multilinestrings.

I don't know which layer I must use to test the pgrouting routage function. I've taken the lines layer, inserted it on postgres, and added 2 columns (source:integer, target:integer, cost:double precision).

Now when I use

select pgr_createTopology('littoral_lines', 0.0001, 'geom', 'osm_id');    

where my table is littoral_lines. I get the following errors:

NOTICE:  PROCESSING:
NOTICE:  pgr_createTopology('littoral_lines',0.0001,'geom','osm_id','source','target','true')
NOTICE:  Performing checks, pelase wait .....
NOTICE:  Creating Topology, Please wait...
NOTICE:  l'opérateur n'existe pas : character varying = integer
NOTICE:  UPDATE public.littoral_lines SET source = 1,target = 2 WHERE osm_id =  4173643
Durée totale d'exécution de la requête :1751 ms.
1 ligne récupérée.

I don't know what's wrong, if I'm using the correct table, or if I miss something. There aren't any "roads" or "ways" tables. Please, help !!!

Answer 1

You should use osm2pgrouting to import the osm data into you DB, it's a command line tool that will automatically create the appropriate graph for pgrouting.

http://www.pgrouting.org/docs/tools/osm2pgrouting.html

Answer 2

When you use OSM data for pgRouting, then you need to use special import tools. This will automatically solve your problem.

To get started I highly recommend you the pgRouting workshop, because it will exactly guide you to get pgRouting work with OSM data: http://workshop.pgrouting.org/

Answer 3

The problem was just that: I didn't load data with postgis with the SRID (4326 in my case). Then I've clone a geom column by creating a route table, changing the preceding geom column by the simple_geom column, with a special srid (linestring) that seems to be more comprehensible from the version 2.x of pgrouting.

CREATE TABLE routes AS SELECT *, (ST_Dump(geom)).geom AS simple_geom FROM littoral_lines WHERE geom IS NOT NULL;
ALTER TABLE routes DROP COLUMN geom;
SELECT UpdateGeometrySRID('routes', 'simple_geom', 4326);

now everything works fine!! Thank you.