install pgRouting

outstandingmaskData Management

Nov 29, 2012 (4 years and 11 months ago)

336 views

1

Install pgRouting


for

Spatial Databases

Lecturer Pat Browne


This no
te des
cribes how to i
nstall
pgRouting
on Windows
XP
in
new

database
and
execute simple
network

query
. You could also install pgRouting in your existing
postgis

database. The note is inte
nded as general advice it is
not

a

lab.



1)Install
pgRouting

Step 1
:

Cop
y and unzip.



Use
pgRouting
-
1.03_pg
-
8.4.2


from
studentdisrib


or
http://www.pgrouting.org/



Note that this is compatable with PostgreSQL 8.4



The Zip contains source SQL files and Windows DLL files






Use Windows E
xtract

All command (or unzip
) to

copy all the files

a
temporary
folder (say pgTemp).



The extraction process will make two sub
-
folders called
“lib” and “share”



You should copy

the DLLs
a
nd SQL files
provid
ed in these folders the correct
location
under your PostgreSQL installation, which should be:



Copy the DLL files to

C:
\
Program Files
\
PostgreSQL
\
8.4
\
lib



Copy the SQL files to

C:
\
Program Files
\
PostgreSQL
\
8.4
\
share
\
contrib



These folders are

shown below:

2


Start

pgAdmin III, a graphical tool for administering PostgreSQL databases. This tool is
accessible from the
Windows Start
| All P
rograms
Menu

and is necessary for
this tutorial. It is assumed that the reader is familiar with the Microsoft

Windows
operating system.

It is assumed that the pgRouting files have been installed in the correct
folders as above.

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

A template can be use
d to construct many database
with the same properties.



Open pgAdmin III from the Windows Start Menu (“Start
-
>Programs
-
>PostgreSQL

8.4
-
>pgAdmin III”).



Connect to your database by double clicking it in the object browser. You may
need to enter password infor
mation.



In pgAdmin III, right click on “Databases” in the table and click “New
Database…”.

3





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



Click “OK”.



Your pgAdmin should look something like this:

4


Step
3
: 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.4
\
share
\
contrib
\
routing_core.sql”


(t
he directory structure may differ depending on where you installed your software).



To execute this query, click the
green
“play” button or navigate the application
menu by clicking “Query
-
>Execute”.



Repeat the same process for


“C:
\
Program Files
\
PostgreSQL
\
8.4
\
share
\
contrib
\
routing_core_wrappers.sql”.

“C:
\
Program Files
\
PostgreSQL
\
8.4
\
sha
re
\
contrib
\
routing_topology
.sql”.




Now the routing functionality is available to “testdb”.

The output from the

execution of the

three

above SQL files should be;
Query retur
ned successfully
with no result in XXms.

5

Step
4
:

You do not have to do this step

as the SQL file is on the course web page, but it is
useful to know how to convert a shape file to an SQL file.

Convert

the crude polyline
sh
apefile (edges.shp) to an equally

crude SQL

file so that it can be imported to the
database.

Note, at point this file does not have any network topology, and it cannot be
used for routing.



Copy the edges shape file from the course web page to
“C:
\
P
rogram
Files
\
PostgreSQL
\
8.4
\
bin




From t
he Windows command prompt, make sure that
“C:
\
Program
Files
\
PostgreSQL
\
8.4
\
bin>” is displayed as your current directory location. An
alternate option would be to add that directory to the system path.



To make the SQL file from the shape file e
nte
r this com
mand
:

shp2pgsql
-
s 2276
-
i
-
I edges.shp edges > 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 (SR
ID) for the
dataset, which happens to be "NAD83 / Texas North Central (ftUS)". How do we
find the SRID for our preferred projection

in pgAdmin
?

o

In pgAdmin III, look at the top toolbar. There is a SQL query tool.

o

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").

o

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%');

o

To execute this query, click the “play” button or navigate the application
menu by clicking “Query
-
>Execute”.

o

This SQL query will return a list of spatial references

with SRIDs. Take
your pick.

6

Step
5
:
Import

the crude SQL file to the database as a new table using the
SQL query tool
in
pgAdmin III
.



In the SQL Query tool window, click “File
-
>Open” and navigate to y
our newly
created SQL file (“
C:
\
P
rogram Files
\
PostgreSQ
L
\
8.4
\
bin
\
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.



Alternatively



cd
“C:
\
P
rogram Files
\
Postgre
SQL
\
8.4
\
bin





c
md
;
psql
-
d

testdb

-
U postgres
-
f
edges
.sql


Step
6
: Viewing edges in OpenJump.



From main menu bar in OpenJump select Layer | Add Datastore Layer:





Make a new connection

to
new

database
:


7



Run the query



You should see the map

8


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

This step is required because as it
stands the edges table does not contain topology (or connectivity information
).



In the SQL shell
, enter the following code and execute:

\
c testdb

ALTER TABLE edges ADD COLUMN source integer;

ALTER TABLE edges ADD COLUMN target integer;

ALTER TABLE edges ADD COLUMN length double precision;

Step
8
: Create the network top
ology in the
“edges” table. This SQL command also

populate
s

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 SE
T length = length(the_geom);

9

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



In the SQL shell
, enter the following code and execute:

CREATE INDEX source_idx ON edges(source);

CREATE INDEX target_idx ON edges(target);

C
REATE INDEX geom_idx ON edges USING GIST(the_geom
GIST_GEOMETRY_OPS);

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



In the SQL shell
, enter the following code and execute:



DROP TABLE IF EXISTS dijskt
ra_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 result of
the shortest path from node 52 to node 35

can be viewed in
OpenJump by connecting to the
"testdb"
database and adding the “dijkstra_result”
table

to OpenJump

using:
.

SELECT
source,target,length,
asbinary(
d.
the_geom) FROM
dijsktra_result

AS d, edges;



Here i
s an example of the res
ults

(start node: 52, end

node: 35).

10




You can view vertices and edges by right clicking on the resulting layer and
selecting Change

Styles





.


Then select labels and choose the appropriate
label.

11





Examples of route finding using Irish road data.

Here we look in more detail at the topology.
As in the above edges example, we must add
the appropriate columns and create topology.

We must have the road data loaded into the
testdb
database.

At the command prompt change directory:

cd
C:
\
Program Files
\
PostgreSQL
\
8.4
\
bin


Use the following command
at the command prompt in:


p
sql
-
d testdb
-
U postgres
-
f roads
.sql


Now login into Postg
reSQL and connect to
testdb
.

\
c testdb

ALTER TABLE roads ADD COLUMN source integer;

ALTER TABLE roads ADD COLUMN target integer;

--

a column called row already exists in roads.

--

but we would like to calculate the distances

--

so we rename the old leng
th and make a new length

ALTER TABLE
roads
RENAME COLUMN
length

TO
leng
t
h
old;

ALTER TABLE roads ADD COLUMN length double precision;

12

In the SQL shell
, enter the foll
owing two commands.
The first SQL command c
reate
s

the
network top
ology in the “
roads
” table.

The second
SQL command
populate
s

the
“length” field which will

be
used as
the edge cost in the network topology.


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

UPDATE
roads
SET length = length(the_geom);

Check the lengths have been calculate
d

select length(the_geom) from roads;


Note the actual vertex ids may differ on your map so you should check visually for the
appropriate numbers
, before running the following query

To v
iew the shortest path from Dublin

(
381)

to Waterford
(660)
in text

for
mat
, issue
the following command in SQL shell:


SELECT * FROM shortest_path('

SELECT gid as
id,

source::integer,

target::integer,

length::double precision as cost

FROM roads
',

381, 660
, false, false);


The output
consists of vertices, edges, and distances

(cost)


vertex_id | edge_id | cost

-----------
+
---------
+
------------------


381 | 390 | 1113.27442795795


388 | 389 | 325.036719294703


385 | 386 | 135.071544129141


383 | 385 | 552.598485315868


384 |

387 | 84.8649903547372


Or in PgAdmin

13





Some other Irish locations
to query:

===============================

381

= Source in Dublin

71

= source in
Cork

48


= source in
Galway

550

= Source in Limerick

660 = source in
Waterford

Note the actual vertex

ids may differ on your map so you should check visually
in
OpenJump. Get

the appropriate numbers
, before running the queries.




How t
o g
raphically view a route.

To view the route from Dublin to Cork in OpenJump:

First crea
te a view in SQL shell

then the
view is queries in OpenJump




CREATE VIEW dublin_cork

AS (

SELECT * FROM shortest_path('

SELECT gid as id,

source::integer,

target::integer,

length::double precision as cost

FROM roads
',

3
8
1
, 7
1
, false, false));


Then run
the following query in OpenJump

R
un Datastore Query

14

select asbinary(
r.the_geom) from du
blin_cork dc, roads r
where
r.
g
id = dc.edge_id;


Which should give
:




Run in SQL shell
.

CREATE VIEW dublin_wexford

AS (

SELECT * FROM shortest_path('

SELECT gid as id,

source::integer,

target::intege
r,

length::double precision as cost

FROM roads
',

381, 660
, false, false));


Then run
the following query in OpenJump

Run Datastore Query

select asbinary(r.the_geom)from dublin_wexford dw, roads r
where r.gid = dw.edge_id;


Run in SQL shell
.

CREATE VIEW dub
lin_galway

AS (

SELECT * FROM shortest_path('

SELECT gid as id,

source::integer,

target::integer,

length::double precision as cost

FROM roads
',

15

381, 48
, false, false));


Then run
the following query in OpenJump

Run Datastore Query

select asbinary(r.the_geo
m)from dublin_galway dg, roads r
where r.gid = dg.edge_id;


Waterford to Galway

CREATE VIEW wat_gal

AS (

SELECT * FROM shortest_path('

SELECT gid as id,

source::integer,

target::integer,

length::double precision as cost

FROM roads
',

660, 48
, false, false))
;


SELECT

asbinary(r.the_geom)
FROM

wat_gal dc, roads r
WHERE

r.
g
id = dc.edge_id;





Understanding the topology

We look at the relationship between the edges,
source
s
, and targets.


select ta
r
get from roads where source = 381
;



G
ives
:

382,388, 375

Then
if we check each of these we find that their source includes 381

select source from roads where target = 382;


source

--------


381


389


390

(3 rows)


select source from roads where target = 388;


source

--------


381

(1 row)




select sourc
e from roads where target = 375;


source

--------


381

16

(1 row)


The following is a graphical representation of how edges (gids), sources and targets are
represented.



se
lect gid,source,target from road
s

where source=381
;

gid | source | target

-----
+
--
------
+
--------


384 | 381 | 382


390 | 381 | 388


790 | 381 | 375

(3 rows)






Labelled with
Edges

(gid)
:






Labelled with
Source

17





Labelled with
Target




18

Road classification National Primary (N
1
,
N11, N4
)











Measuring t
he length of a path

The first example only computes the lengths of individual sections of the path.

Select asbinary(geomunion(r.the_geom,r.the_geom)),
length(geomunion(r.the_geom,r.the_geom))/1000 as dist

19

from dublin_cork as dc, roads as r where r.gid =
d
c.edge_id;





Use the following to compute the
length

of the shortest path from Dublin to Cork.

Note, the
Dublin_Cork

view must exist.

select asbinary(MemGeomUnion(r.the_geom)),
length(MemGeomUnion(r.the_geom))/1000 as dist

from dublin_cork as dc, roads
as r where r.gid =
dc.edge_id;


The straight lines distance is about
215
-
220 Kilometres

20