The installation of PostgreSQL for Windows also installs pgAdmin III, a graphical tool for administering PostgreSQL databases. This tool is accessible from the Windows Start Menu and is necessary for this tutorial. It is assumed that the reader is familiar with the Microsoft Windows operating system.

 

For the demonstration, we will be using a shapefile named “edges.shp”.

 

 

pgRouting Tutorial

 

Step 1: Create a database called “testdb” using pgAdmin III and add PostGIS functionality via the default template.

 

  • Open pgAdmin III from the Windows Start Menu (“Start->Programs->PostgreSQL 8.3->pgAdmin III”).
  • Connect to your database by double clicking it in the object browser. You may need to enter password information.
  • In pgAdmin III, right click on “Databases” in the table and click “New Database…”.


  • Name the database “testdb” and for the template, select “template_postgis”.
  • Click “OK”.


 

 

Step 2: Add the core pgRouting functionality to the newly created database.

 

  • In pgAdmin III, select the newly created “testdb” database in the object browser.
  • Look at the top toolbar in pgAdmin III. There is a SQL query tool. Click on this tool to open it, or click “Tools->Query Tool” from the application menu.
  • In the SQL query tool window, click “File->Open” and select “C:\Program Files\PostgreSQL\8.3\share\contrib\routing_core.sql” (the directory structure may differ depending on where you installed your software).
  • To execute this query, click the “play” button or navigate the application menu by clicking “Query->Execute”.
  • Repeat the same process for “C:\Program Files\PostgreSQL\8.3\share\contrib\routing_core_wrappers.sql”.
  • Now the routing functionality is available to “testdb”.

 

 

Step 3: Convert the crude polyline shapefile (edges.shp) to the same crude PL/pgSQL file so that it can be imported to the database.

 

  • From the Windows command prompt, make sure that “C:\Program Files\PostgreSQL\8.3\bin>” is displayed as your current directory location. An alternate option would be to add that directory to the system path.
  • Enter this command, replacing variables where necessary:

 

shp2pgsql -s 2276 -i -I "C:\edges.shp" edges > "C:\edges.sql"

 

  • The successful execution of this command should output this to the screen:

 

Shapefile type: Arc

Postgis type: MULTILINESTRING[2]

 

  • NOTE: In the above command, “-s 2276” sets the spatial reference (SRID) for the dataset, which happens the be "NAD83 / Texas North Central (ftUS)". How do we find the SRID for our preferred projection?

 

    • In pgAdmin III, look at the top toolbar. There is a SQL query tool.
    • Make sure that a PostGIS-enabled database is selected (i.e. “template_postgis” or “testdb”), and click on the SQL query tool to bring up the query window (or click "Tools->Query Tool").
    • Enter the following code in the SQL query tool to find all spatial reference systems that have “Texas” in the WKT (Well-Known Text) definition:

 

SELECT *

FROM spatial_ref_sys

WHERE srtext LIKE ('%Texas%');

 

    • To execute this query, click the “play” button or navigate the application menu by clicking “Query->Execute”.
    • This SQL query will return a list of spatial references with SRIDs. Take your pick.

 

 

Step 4: Import the crude SQL file to the database as a new table using the SQL query tool.

 

  • In the SQL Query tool window, click “File->Open” and navigate to your newly created SQL file (“C:\edges.sql”) and click "Open".
  • Click the “play” button (or “Query->Execute”) to create your new table in the database.
  • You may have to refresh the view to see the “edges” table in the “testdb” database.

 

 

Step 5: Prepare the new table for Dijkstra by adding source, target, and length columns. In this example “length” will be the cost of the edges.

 

  • In the SQL query tool, enter the following code and execute:

 

ALTER TABLE edges ADD COLUMN source integer;

ALTER TABLE edges ADD COLUMN target integer;

ALTER TABLE edges ADD COLUMN length double precision;

 

 

Step 6: Create the network topology in the “edges” table. Also populate the “length” field which is to be the edge cost in the network topology.

 

  • In the SQL query tool, enter the following code and execute:

 

SELECT assign_vertex_id('edges', 0.001, 'the_geom', 'gid');

UPDATE edges SET length = length(the_geom);

 

 

Step 7: Create indices for source, target, and geometry columns in the “edges” table.

 

  • In the SQL query tool, enter the following code and execute:

 

CREATE INDEX source_idx ON edges(source);

CREATE INDEX target_idx ON edges(target);

CREATE INDEX geom_idx ON edges USING GIST(the_geom GIST_GEOMETRY_OPS);

 

 

Step 8: Perform the routing operation and store the results in a “dijkstra_result” table.

 

  • In the SQL query tool, enter the following code and execute:

 

            DROP TABLE IF EXISTS dijsktra_result;

CREATE TABLE dijsktra_result(gid int4) with oids;

SELECT AddGeometryColumn('dijsktra_result', 'the_geom', '2276', 'MULTILINESTRING', 2);

INSERT INTO dijsktra_result(the_geom)

            SELECT the_geom FROM dijkstra_sp('edges', 52, 35);

 

  • Now the results can be viewed in uDig (or any other GIS software that can view PostGIS layers) by connecting to the database and adding the “dijkstra_result” table.
  • Here is an example of the results viewed in uDig (start node: 52, end node: 35):