Getting Started on pgRouting

pgRouting is a useful tool extended from PostgreSQL database that stores geographical data in the form of edges and vertices in SQL tables which then allows for routing queries to be performed using the provided functions.

This guide will walk you through a setup using a Windows machine. There are a few items required as listed below. Simply click on them and you’ll be directed to the downloads.

1. PostgreSQL installer
2. PostGIS bundle (Note that the PostGIS bundle that you choose has to be the same version as the PostGreSQL)
3. Data in the form of shp — read as shape — files

Install PostgreSQL

It is recommended to install using default selections. Otherwise, keep in mind that the command line tool is compulsory, pgAdmin is highly recommended for ease of database access and stack builder is optional.

Adding PostGIS Features

Unzip the PostGIS bundle file and copy the files in the following folders to the respective PostgreSQL program folders. Here, we assume that PostgreSQL v13 is installed. Otherwise the directory for PostgresSQL will change according to the version number.

1. All .dll files in ‘bundle_directory\lib’ to ‘C:\Program Files\PostgreSQL\13\lib’
2. All folders in ‘bundle_directory\share’ to ‘C:\Program Files\PostgreSQL\13\share’
3. All files and folders in ‘bundle_directory\bin’ to ‘C:\Program Files\PostgreSQL\13\bin’

In order to use PostgreSQL command line tool and the shp file converter tool, we will need to edit our system environment variables.

1. Using Windows Search, enter ‘system environment’. Select ‘Edit the system environment variables’.
2. Click on the ‘Environment Variables…’ button below.
3. Under ‘System variables’, double click on ‘Path’, then click on the ‘New’ button.
4. Browse to ‘C:\Program Files\PostgreSQL\13\bin’, click ‘OK’ and save it.

Convert, Import And Prepare Data

Unzip the data folder and run the script shown below — assuming that the folder you unzipped into is ‘roads_digitized_0_0_0’, otherwise edit the script accordingly.

Notice that I changed the data projection from the original 32644 to 4326 because PostGIS only provide map visualisation on data projected onto 4326 through pgAdmin. It is a really cool tool to visualise and better understand your data.

Data Visualisation of Sri Lankan Roads

To view the image above, open up pgAdmin and from the left panel navigate accordingly from ‘Server’ > ‘PostgreSQL’ > ‘Databases’ > ‘roads’ > ‘Schemas’ > ‘Tables’ > ‘roads_digitized’. Right click on it and and select ‘All Rows’ under ‘View/Edit Data’. Go to the geom column and click on the eye icon beside the header.

Now your database is set up and ready for you to continue to learn more about pgRouting!

If you like my notes, especially if it saves you some time pulling your hair out, feel free to give me a clap or even buy me a donut ;)