I am using Postgres with Postgis extension to retrieve streets (only for pedestrian use) and several "natural" points. I need to make updates every minute and i am working on a small bounding box. For rendering I am using leaflet that makes request to a java servlet and all is working well...Postgres is populated with "osm2pgsql".

I would implement some kind of routing service, unfortunately i am not expert with PLPGSQL dialect but i found this excellent post in LOUDHUSH BLOG. To make it work I had to change the name of the last function to "pgr_createTopology":

drop table if exists network;
create table network(gid serial, osm_id bigint, name varchar, the_geom     
geometry, source bigint, target bigint, length float);

CREATE OR REPLACE FUNCTION compute_network() RETURNS text as $$
streetRecord record;
wayRecord record;
pointCount integer;
pointIndex integer;
geomFragment record;

-- for each street
FOR streetRecord in select way, osm_id, name from planet_osm_line where     
highway is not null LOOP

-- for each street in the region of interest
 SELECT * from planet_osm_ways where id = streetRecord.osm_id into wayRecord; 
 FOR pointIndex in array_lower(wayRecord.nodes, 1)..array_upper(wayRecord.nodes,1)-1 LOOP

RAISE NOTICE 'Inserting name % source %, target %', streetRecord.name, wayRecord.nodes[pointIndex], wayRecord.nodes[pointIndex+1];
  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, source, target, length) values(streetRecord.osm_id, streetRecord.name, geomFragment.way,
  wayRecord.nodes[pointIndex], wayRecord.nodes[pointIndex+1], st_length(geomFragment.way));
return 'Done';
$$ LANGUAGE 'plpgsql';

select * from compute_network();
select pgr_createTopology('network', 1, 'the_geom', 'gid');

"network" table is populated in the right manner..i think, before to run Shortest Path Dijkstra i changed manually "source" and "target" column to integer type but the query below return an empty table

CREATE TABLE route AS SELECT seq, id1 AS node, id2 AS edge, cost, b.the_geom FROM pgr_dijkstra('
            SELECT gid AS id,
                     length::double precision AS cost
                    FROM network',
            41, 50, false, false) a LEFT JOIN ways b ON (a.id2 = b.gid);

can you tell me what am I doing wrong?


The source and target parameters in pgr_dijkstra are id's of vertices, not edges. I assume 41 and 50 are edge id's = column gid in table network.

Moreover pgrouting is constrained to 32bit-integers as edge- and vertex-id's, so you can't use OSM-id's directly, as they are too big.

  • 1
    In pgRouting 2.1.0 this limit is lifted so pg_dijikstra works with bigints as well. – LR1234567 Aug 21 '15 at 4:44
  • 1
    wow, i'll try it, working only with integers is a bit frustrating, thanks Redoute for reply but the problem is solved keeping empty "source" and "target" columns and than running "pgr_createTopology" – gaia Aug 21 '15 at 23:13

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.