Wednesday, April 02, 2008

Install a routing mechanism on postgis (windows).

based on http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007

download and install
- postgres 8.2 (pgRouting is not yet available as binary for postgres 8.3)
- postgis (comes with postgres 8.2)
- pgRouting 1.0

C:
cd Program Files\PostgreSQL\8.2\bin

//create db
createdb -U postgres -E UNICODE routing
createlang -U postgres plpgsql routing

//load postgis and pgrouting into db
psql -U postgres -f ../share/contrib/lwpostgis.sql routing
psql -U postgres -f ../share/contrib/spatial_ref_sys.sql routing
psql -U postgres -f ../share/contrib/routing_core.sql routing
psql -U postgres -f ../share/contrib/routing_core_wrappers.sql routing

//load datafile generated witd shp2pgsql

//add columns
ALTER TABLE dummy ADD COLUMN source integer;
ALTER TABLE dummy ADD COLUMN target integer;
ALTER TABLE dummy ADD COLUMN length double precision;
UPDATE dummy SET length = length(the_geom);

//create network
SELECT assign_vertex_id('dummy', 0.001, 'the_geom', 'gid');

//optimise table
CREATE INDEX source_idx ON dummy(source);
CREATE INDEX target_idx ON dummy(target);
CREATE INDEX geom_idx ON dummy USING GIST(the_geom GIST_GEOMETRY_OPS);

//test network trace
SELECT gid, AsText(the_geom) AS the_geom FROM dijkstra_sp('dummy', 1, 100);

//update srid (option)
select updategeometrysrid('dummy', 'the_geom', 4326);
update dummy set the_geom = setsrid(the_geom,4326);

//finished! now check out http://boston.freemap.in/routing.html for implementation options!
Blogged with the Flock Browser

2 comments:

Dulur lanang said...

thank before

i've installed postgre 8.2 and postgis. and all of them were succesfully installed. however when i trying to load routing_core.sql ends with the following error: -- ERROR: incompatible library "C:/Program Files/PostgreSQL/8.2/lib/librouting.dll": version mismatch
SQL state: XX000
Detail: Server is version 8.2, library is version 8.3. --

can i get a compatible library(librouting.dll) for this server which version is 8.2?

Paul said...

This post is quite old and pg versions can't be mixed that easily, check out this post: http://www.rqna.net/qna/nwquvm-configuring-pgrouting-on-win7.html