How to find the shortest path from an Internet Zoo topology file throught Postgis/pgrouting

First, you need to download a topology from Internet zoo.
I will use Napnet as example:


Now, we need to convert graphgml to sql (to load from postgres). To do it, you can use simplekml library for python or pykml.
It's important use geographic coordinates without altitude value and only write edges in the kml file.

After, you can follow this post to convert from kml to sql.

To begin, first we need to create a database and create a password (usually is postgres) for postgres user:

sudo -u postgres createdb testing
sudo -u postgres psql  

postgres=# \password postgres
Enter new password: 
Enter it again: 

postgres=# \q

Now, load the sql query:

psql -U postgres -d testing -q -f "Napnet.sql"
psql: FATAL:  Peer authentication failed for user "postgres"

To avoid this issue, you can run it throught this way:

psql 'host=localhost port=5432 dbname=testing user=postgres' -q -f "Napnet.sql"
psql:Napnet.sql:17: ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist

If you see this error, is it because you forgot to load pgrouting extension previously.
To load it, follow this steps:

sudo apt install postgis pgadmin3
psql 'host=localhost port=5432 dbname=testing user=postgres'
testing=# CREATE EXTENSION postgis;
testing=# CREATE EXTENSION pgrouting;

Now, we need to create nodes in the postgis topology:

testing=# alter table napnet add column source integer;
testing=# alter table napnet add column target integer;
testing=# SELECT pgr_createTopology('napnet',0.0001,id:='gid',the_geom:='geom');

NOTICE:  pgr_createTopology('napnet', 0.0001, 'geom', 'gid', 'source', 'target', rows_where := 'true', clean := f)
NOTICE:  Performing checks, please wait .....
NOTICE:  Creating Topology, Please wait...
NOTICE:  Unexpected error Geometry has Z dimension but column does not
(1 row)

If you see this issue, is it because you didn't remove the altitude in coordinates from kml file.
You can delete them with sed:

sed 's/,0.0//g' napnet.kml

Now to check that the topology it's ok, you can create a table with dijkstra output using paths lenght as cost from node 1 to 6.

create table dijkstra_napnet
as SELECT * FROM pgr_dijkstra(
'SELECT gid as id, source, target, st_length(geom, true) as cost FROM napnet', 1,6, false) as di
  JOIN napnet pt
  ON di.node = pt.gid ;

Now, you can load dijkstra_napnet and napnet in Qgis to see the result.

Enjoy it!


Popular posts from this blog

from: can't read /var/mail/ in Python scripts

Fix "Set scan parameters failed: Input/output error" on Ubuntu 16.04

How to install a WhatsApp bot based on Python