Here starts a series of articles to set up a routing web application (route
calculation) completely autonomous, ie using no web service or API for
calculating the route.
We will set up the database including the route network (or fluvial, etc.) with
Postgresql, then we will set up a wms flow with Geoserver, and finally we will
develop a calculation page with OpenLayers 3. All using Windows .
In this first article we will discuss the setting up of the network
database.
You will need to have an operational installation of PostreSQL / Postgis. If
this is not the case, refer to the article Starting with PostgreSQL / Postgis.
Pgrouting installation
For routing calculations, we will use the Postgis pgrouting extension. It
is installed, in theory, with the new Postgis versions. To be on the safe side,
open pgAdmin III and run the following SQL query:
SELECT pgr_version ();
You must have a result like this:
If pgrouting is not installed:
- Download the zipped pgRouting file zip file for your system (32 or 64
bit) from Winnie . - Unzip the
file. You will find three directories: bin, lib and share folders and two text
files. - Copy
these files and directories into the directory of your Postgres installation
(in my case C: \
Programs \ PostgreSQL \ 9.5 ) - In
pgAdmin III select your Postgis database and run the following SQL query:
CREATE EXTENSION pgrouting;
Check with the following query that the installation has been completed
successfully
SELECT pgr_version ();
If the version you have is
not the last (2.2.3), download and update your version. The whole series of
these articles is achieved with this version and be aware that the syntax of
many pgrouting commands has been modified. If
you try to run the queries shown here with older versions you risk error
messages like this:
Setting up the data
We will take the simplest case, but if you already have a network
database, you will need to take inspiration from this example to do the necessary
adaptation for its use with pgrouting.
In our case, we will download openStreetMap data from Brittany.
We download them from Geofabrik (http://download.geofabrik.de/europe/france/bretagne.html
).
You have several available formats (osm, shp, pbf). Whichever your
choice, there will be work to be done to make the data usable once loaded into
your Postgis database. The format that gives the least work and the best final
results is the osm .In addition, there is a tool (osm2pgrouting) that allows
the loading and creation of the topology in, just, one path.
How to import the OSM data with osm2pgrouting
In the latest Postgis versions, this tool is installed automatically. Check
that the C: \ Program Files \ PostgreSQL \ 9.5 \ bin \ osm2pgrouting.exe file
is present in your installation.
If this is not the case, download the tool version corresponding to your
installation from winnie. Unzip the file and follow the installation
instructions included in the osm2pgrouting_Readme.txt file.
To simplify your command lines, add the \ Program Files \ PostgreSQL \
9.5 \ bin directory to your PATH environment variable.
To use the tool you will need to open a prompts command window.
To get the complete syntax of the tool you can enter
osm2pgrouting.exe -help
The essential elements to launch the tool:
- the name of the osm file to be loaded
( bretagne-latest.osm ) - the name of the postgis database
( postgis_22_sample ) - the listening port of your
Postgresql installation ( 5432 ) - the name of the host ( localhost )
- the name of the config xml file:
two files are provided with mapconfig.xml (generic) and mapconfif_for_cars.xml (for cars)
Here is an example of a command line
osm2pgrouting.exe -clean = 1 -f bretagne-latest.osm -d postgis_22_sample
-p 5432 -h localhost -c mapconfig_for_cars.xml
The clean parameter= 1 indicates to overwrite
existing tables. This command loads data into multiple tables, but also
generates the corresponding topology. For pgrouting the base table is the ways
table and the topology created the ways_vertices_pgr table .
Once
the command is executed, you will find a series of new tables in your database
The only tables you will use for your routing application are ways
(linear network) and ways_vertices_pg r (network nodes).
How to import the data without osm2pgrouting
If you download the Geofabrik data in shapefile format or if you have
data with this format, it’s a bit lengthier.
You must start by loading your shapefile including the linear network
into your PostgreSQL / Postgis database. If necessary, relaunch your data in
Geographic WGS84 because all pgrouting routing tools work on latitude /
longitude data.
Refer to the article Starting with PostgrSQL / Postgis -Introduction to pgAdmin3
for loading a shape into Postgis.
The created table corresponds to the ways table from the
previous chapter. Therefore, it is necessary to create the nodes table( ways_vertices_pg
r) through the generation of the network topology. In order to make the following
simpler, we will consider that you named your table ways and that
you placed it in the public database .
This is executed by the SQL command pgr_createTopology . But
this command assumes that your table contains two columns named source
and target .
Here are the commands to enter in the pgAdmin
III SQL window.
The value 0.00001 is equivalent to the tolerance in degrees, geom
to the name of the geometry field and gid to the table identifier field.
At the exit of this command you will find the table ways_vertices_pg
r in your PostgreSQL database.
How to validate your data in Postgis
In an ideal world, you could go directly to setting up your data server with
Geoserver. But, there is a good chance that things will not work out properly
…
We will discuss the most common problems, letting you discover by yourself
a lot of additional problems!
Firstly, we will use QGis to test our database and the operation of
different routing algorithms.
Pgrouting tests with QGis
You will will start by connecting with your Postgis database and loading the ways
layer :
To
run the pgrouting sql commands on your database, from QGis, open the Database
Manager
Then,
select your Postgis database from the databases list, and open a SQL window
It is in this upper window that we will enter the pgrouting sql
commands.
The lower window will list the table records resulting from the sql
command.
By checking the box on the bottom Load as new layer , we will
load the result in the QGis map window.
Example:
The SQL command corresponds to the route search in pgrouting using the
algorithm A *, starting from node 69072 and arriving to node 64204
The Run button sends the command to the Postgres database and
retrieves the number of lines (30 lines in this example) corresponding to the
route and displays in the bottom window these lines in the order for the route.
If we now ask to load the layer in QGis, it is the data of this lower
window that will be displayed in the map window.
Problem: There is no geometry column in the result. We will not be able
to view the route as a layer.
To get around this problem, we will add the geometry in the command
results. It’s a little complicated to explain, so let’s go straight to discuss
how to do it:
We will make a join between the algorithm pgr_astar result (…) (All
that is in parentheses are the algorithm parameters), which does not return in
the result the geometries but only nodes identifiers (edges) , and the node table,
then with the ways table:
SELECT seq, id1 AS node, id2 AS edge, the_geom
FROM pgr_astar (‘SELECT gid as id, source, target, cost, x1, y1, x2, y2 FROM
public.ways’, 69072, 64204, false, false) as res
JOIN public.ways ways_vertices_pgr
ON res.id2 = ways_vertices_pgr.gid;
In short, we look for the node corresponding to
the edge of the result and we recover the linear section which is joined to it
by the topology. Now we can add in the result fields of the selected field the_geom which will allow us to trace
the result in QGis.
To load the layer in QGis, select the Load
as new layer checkbox , select the geometry field, and click the Load
button .
Now that we have discussed how to test our database, we will discuss the
main algorithms proposed by pgrouting, and which changes in the data must be
made in order to work properly … but all this in the next article.
