OSM Routing

From GeoBox

The pgRouting software is already installed in GeoBox.

There are also some data already inserted in the 'routing' database.

The exercises are inspired on the pgRouting workshop available online. Refer to the workshop material for further information.

Exploring existing data

Try to find out the data model used.

Find known streets in Braga. Take note of the source and target ids.

Select some starting node (source).

SELECT name, SOURCE FROM ways
WHERE name ilike '%santa cruz%'

Select some ending node (target).

SELECT name, target FROM ways
WHERE name ilike '%santo andré%'

Calculate the path from source node to target node.

SELECT * FROM shortest_path('
SELECT gid as id,
source::integer,
target::integer,
length::double precision as cost
FROM ways',
404,945, FALSE, FALSE);

Loading other data

In this exercise, we will load other data from OSM to calculate routing between points.

Select the area around Águeda city, for example, a small city between Aveiro and Coimbra.

Downloading OSM data with JOSM

Open JOSM, select the area mentioned, and download it as a new layer. Save the layer as a .osm file.

Download data directly from OSM, using the API.

The same can be archived by using the OSM API directly.

Create a new database

Create a new database called 'agueda'. Remember to use the template_postgis when creating the database.

Importing OSM data to the database

osm2pgrouting -file agueda.osm -conf "/usr/share/osm2pgrouting/mapconfig.xml" -host localhost -dbname agueda -user geobox -clean

It will take around 2 minutes to compute the network and put it in the database.

Detecção de Erros

Calcular o número de chegadas e saídas em cada vértice.

ALTER TABLE vertices_tmp ADD COLUMN cnt INTEGER;
UPDATE vertices_tmp SET cnt=0;
 
UPDATE vertices_tmp 
SET cnt=(SELECT COUNT(*) FROM ways WHERE SOURCE = id);
 
UPDATE vertices_tmp 
SET cnt=(SELECT COUNT(*) FROM ways WHERE target = id);

Mostrar as tabelas ways e vertices_tmp no QGIS.

No QGIS, mostrar a tabela vertices_tmp com uma simbologia em por classes, sobre o campo cnt. Escolher um número de classes igual ao dobro do máximo, por exemplo, para se poderem pintar os vértices com o cnt=0 a vermelho.