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.
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”.
Step 2: Add the core pgRouting functionality to the newly created database.
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.
shp2pgsql -s 2276 -i -I “C:\edges.shp” edges > “C:\edges.sql”
Shapefile type: Arc
Postgis type: MULTILINESTRING
- 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:
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.
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.
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.
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.
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.
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);