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 $$ DECLARE streetRecord record; wayRecord record; pointCount integer; pointIndex integer; geomFragment record; BEGIN -- 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)); END LOOP; END LOOP; return 'Done'; END; $$ 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, source::integer, target::integer, 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?