Assalamualaikum,

I ran through a day trying to get everything works for PostgreSQL 9.1, PostGIS 2.0 & pgRouting 1.03.

But so far, pgRouting not working when run in Windows, missing librouting.dll when execute the SQL file provided from pgRouting page, even thought the library is there…not sure why…so I skip the part investigating this issue.

And for you Ubuntu, i’m pretty sure it’s working fine with PostgresSQL 9.1 version even thought am yet try to install it. 😉

For Windows, I need PostgreSQL 8.4, PostGIS 1.5.4 and pgRouting 1.03 in order to use pgRouting.

Here the installation process:

1. Install PostgreSQL 8.4.4 (follow the instructions – and remember the password! 😉 )
2. Install PostGIS 2.0 (follow the instructions)
3. Extract pgRouting-1.03_pg-8.4.2.zip then, copy & paste following to PostgreSQL/8.4/lib.
   1. librouting.dll
   2. librouting_dd.dll
   3. librouting_tsp.dll
4. Open up pgAdmin3(which installed together in #1) & connect to the PostgreSQL. Proceed with following
   1. Open up SQL Pane.
   2. Execute all SQL located in ‘<path_to_the_folder>/pgRouting-1.03_pg-8.4.2/Share/Contrib’

Now, we done with pgRouting core.

Next, to the table schema & data. For this purpose, If you already have routes in shapefile ( or any other format ), you can insert it to PostgreSQL using any tools you like to. In my case, I use QGis – Spit.

Once you already export it to the database, ensure the table have the following columns:

1. gid (integer)
2. id (integer)
3. the_geom ()

You may need to add id column if you don’t have one – my case, I don’t have..so just add the id column and update id = gid.

Next, you need 3 main columns – source, target & length. Just run following command and of course, change the table name. 🙂

1. ALTER TABLE <table_name> ADD COLUMN source integer;
2. ALTER TABLE <table_name> ADD COLUMN target integer;
3. ALTER TABLE <table_name> ADD COLUMN length double precision;

OK, now we’re done with table fields. Next, with table index, run following queries:

1. SELECT assign_vertex_id(‘<table_name>’, 0.001, ‘the_geom’, ‘gid’);
2. UPDATE <table_name> SET length = length(the_geom);
3. CREATE INDEX source_idx ON <table_name>(source);
4. CREATE INDEX target_idx ON <table_name>(target);
5. CREATE INDEX geom_idx ON <table_name> USING GIST(the_geom GIST_GEOMETRY_OPS);

Hopefully there’s no issue so far… ^_^

Now, it’s the test part…let’s test it! Run following queries!

1. Test core function of shortest path – SELECT * FROM shortest_path(‘SELECT gid as id,source::integer,target::integer,length::double precision as cost FROM road_malaysia’,<source>,<target>, false, false);
2. Test Dijkstra – SELECT gid, AsText(the_geom) AS the_geom FROM dijkstra_sp(‘road_malaysia’,<source>,<target>);

If you get the results, means all OK! if not…it’s better to run through step by step…or..diagnose what’s the error message…or can drop by the message here. 😉

Reference: http://www.pgrouting.org/docs/foss4g2007/ch02.html

Wassalam.