Le blog de Jean David TECHER, un Réunionnais à Avignon

1. Requirements

• PostgreSQL >= 8.1
• PostGIS+Geos+Proj
• cmake for pgrouting. On Fedora, you can use you favorite tool to install it yum install cmake.x86_64

Be sure to have PostgreSQL bin in your PATH

export PATH=/usr/local/pgsql/bin:$PATH

N.B: the following instructions have been tested on Ubuntu Studio Feisty 32 bits, Fedora AMD 64 bits

2. Installation

First a directory where to compil our sources.

mkdir /tmp/src
cd /tmp/src/

2.a) GAUL

wget http://prdownloads.sourceforge.net/gaul/gaul-devel-0.1849-0.tar.gz?download
tar xvzf gaul-devel-0.1849-0.tar.gz 
cd gaul-devel-0.1849-0
./configure --enable-slang=no
make
make install

2.b) BOOST

cd ..
wget http://kent.dl.sourceforge.net/sourceforge/boost/boost_1_33_1.tar.bz2
tar xvjf boost_1_33_1.tar.bz2 
cd boost_1_33_1
./configure
make
make install

2.c) CGAL

cd ..
wget ftp://ftp.mpi-sb.mpg.de/pub/outgoing/CGAL/CGAL-3.3.tar.gz
tar xvzf CGAL-3.3.tar.gz 
cd CGAL-3.3
./install_cgal --prefix=/usr/local/cgal --with-boost=n --without-autofind -ni /usr/bin/g++
export CGAL_MAKEFILE="/usr/local/cgal/share/cgal/cgal.mk"

It's time compiling pgrouting.

2.d) PGROUTING

cd ..
wget http://files.postlbs.org/pgrouting/source/pgRouting-1.02.tgz
tar xvzf pgRouting-1.02.tgz 
cd pgrouting/
cmake -DWITH_TSP=ON -DWITH_DD=ON \
-DPOSTGRESQL_LIBRARIES=/usr/local/pgsql/lib/libpq.so -DPOSTGRESQL_INCLUDE_DIR=/usr/local/pgsql/include/server \
-DBoost_INCLUDE_DIR=/usr/local/include/boost-1_33_1/boost/ \
-DCGAL_INCLUDE_DIR=/usr/local/cgal/include -DCGAL_LIBRARIES=/usr/local/cgal/lib/libCGAL.so  \
-DGAUL_INCLUDE_DIR=/usr/local/include -DGAUL_LIBRARIES=/usr/local/lib/libgaul.so  -DGAUL_UTIL_LIBRARIES=/usr/local/lib/libgaul_util.so
make
make install

3. Extra

echo /usr/local/cgal/lib/ >> /etc/ld.so.conf
/sbin/ldconfig 

Notice: Extra is required else when you load routing_core.sql and routin_dd.dsql , you have errors. Libraries librouting.so and librouting_dd.so depend on libCGAL.so.2.

Indeed

[postgres@jdtecher ~]$ ldd /usr/local/pgsql/lib/librouting.so 
        linux-vdso.so.1 =>  (0x00007fff5edfd000)
        libgaul.so.0 => /usr/local/lib/libgaul.so.0 (0x00002aaaaad06000)
        libgaul_util.so.0 => /usr/local/lib/libgaul_util.so.0 (0x00002aaaaaf35000)
        libCGAL.so.2 => /usr/local/cgal/lib/libCGAL.so.2 (0x00002aaaab147000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00002aaaab376000)
        libm.so.6 => /lib64/libm.so.6 (0x00002aaaab676000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002aaaab8fa000)
        libc.so.6 => /lib64/libc.so.6 (0x00002aaaabb08000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00002aaaabe5f000)
        /lib64/ld-linux-x86-64.so.2 (0x000000300f200000)
[postgres@jdtecher ~]$ ldd /usr/local/pgsql/lib/librouting_dd.so 
        linux-vdso.so.1 =>  (0x00007fff6adfd000)
        libgaul.so.0 => /usr/local/lib/libgaul.so.0 (0x00002aaaaad12000)
        libgaul_util.so.0 => /usr/local/lib/libgaul_util.so.0 (0x00002aaaaaf41000)
        libCGAL.so.2 => /usr/local/cgal/lib/libCGAL.so.2 (0x00002aaaab153000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00002aaaab382000)
        libm.so.6 => /lib64/libm.so.6 (0x00002aaaab682000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002aaaab906000)
        libc.so.6 => /lib64/libc.so.6 (0x00002aaaabb14000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00002aaaabe6b000)
        /lib64/ld-linux-x86-64.so.2 (0x000000300f200000)

Now log as postgres, create a database with PostGIS+PgRouting

export PGUSER=postgres
createdb testgis
createlang plpgsql testgis
psql -d testgis -f /usr/local/pgsql/share/contrib/lwpostgis.sql
psql -d testgis -f /usr/local/pgsql/share/contrib/spatial_ref_sys.sql
psql -d testgis -f /usr/share/postlbs/routing_core.sql
psql  -d testgis -f /usr/share/postlbs/routing_core_wrapper.sql
psql -d testgis -f /usr/share/postlbs/routing_tsp.sql
psql  -d testgis -f /usr/share/postlbs/routing_ts¯wrapper.sql
psql  -d testgis -f /usr/share/postlbs/routing_dd.sql
psql -d testgis -f /usr/share/postlbs/routing_dd_wrapper.sql

Example of query, export in kml and view using Google MAPS API

First create a table

BEGIN;

DROP TABLE IF EXISTS shortest_path_astar_table;

CREATE TABLE shortest_path_astar_table(gid int4) with oids;

SELECT AddGeometryColumn( 'shortest_path_astar_table', 'the_geom', 4326, 'MULTILINESTRING', 2 );

INSERT INTO shortest_path_astar_table(the_geom) 
  SELECT Transform(SetSrid(r.the_geom,27572),4326) AS the_geom FROM shortest_path_astar(
  'SELECT id, source, target,x1,y1,x2,y2,cost,reverse_cost,to_cost,rule from routing',
  (SELECT  source FROM routing WHERE id=12580 LIMIT 1),
  (SELECT  target FROM routing WHERE id=7763 LIMIT 1),
   true,true) x(v,e,c) JOIN routing r ON  (r.id=x.e);

END;

Export it using ogr2ogr

ogr2ogr -f KML /var/www/test1.kml PG:'host=localhost dbname=routing_db user=postgres password=***' -sql "select * from shortest_path_astar_table"

Then Using a script like

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>Google Maps JavaScript API Example</title>
    <script src="http://maps.google.com/maps?file=api&v=2&key=YOUR_GOOGLE_MAP_KEY_HERE"
      type="text/javascript"></script>
    <script type="text/javascript">

    //<![CDATA[

    function load() {
      if (GBrowserIsCompatible()) {
        var map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(43.6324576574883,3.8418567112324), 14);
var gx = new GGeoXml("http://www.davidgis.fr/test1.kml");

map.addOverlay(gx);
      }
    }

    //]]>
    </script>
  </head>
  <body onload="load()" onunload="GUnload()">
    <div id="map" style="width: 1000px; height: 600px"></div>
  </body>
</html>

You will have the following image