This note assumes - School of Computing

needmoreneedmoreΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

243 εμφανίσεις

1

Lab3

(26
-
Feb
-
2010
)


for

Spatial Databases Course (DT211

& DT249)

Lecturer Pat Browne


This no
te describes how to install pgRouting
software

on Windows
XP.

1)

Install
pgRouting
on Windows
XP and execute simple
network

query

2
)

Explore
School

of Computing Da
ta



1)Install
pgRouting

Step 1
:

Cop
y and unzip.



Use
pgRouting
-
1.03_pg
-
8.4.2


from
CD or
http://pgrouting.postlbs.org/



The Zip contains source SQL files and Windows DLL files






Unzip (extract) to a folder (say

pgTemp).



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



You should copy

the DLLs
and 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 c
alled “testdb” using pgAdmin III and add PostGIS
functionality via the default template.

A template can be used 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 information.



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

3





Name the database “testdb” and for the template, sel
ect “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 to
ol window, click “File
-
>Open” and select


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


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



To execute this query, click the
green
“play” button or navigat
e 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 functionalit
y is available to “testdb”.

The output from the

execution of the

three

above SQL files should be;
Query returned 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 kn
ow 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 fo
r routing.



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




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



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

shp2pgsql
-
s 2276
-
i
-
I edges.shp edges > edges.sql



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

Shapefile typ
e: Arc

Postgis type: MULTILINESTRING[2]





NOTE: In the above command, “
-
s 2276” sets the spatial reference (SRID) 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

Ente
r 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 navig
ate 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
\
PostgreSQL
\
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 re
fresh the view to see the “edges” table in the “testdb”
database.

Step
6
: Viewing edges in OpenJump.



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





Make a new connection:


7



Run the query



You should see the map

8


Step
7
: Prepar
e 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 SQ
L 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” tabl
e. 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 SET length = l
ength(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);

CREATE 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 dijsktra_result;

C
REATE 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 is 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.

\
c testdb

AL
TER 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 length and make a new length

ALTER TABLE
roads
RENAME COLUMN
length

TO
leng
t
h
old;

ALTER TABLE roads ADD COLUMN length double precision;

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
“leng
th” 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);


12


To v
iew the shortest path from Dublin to Waterford in text

format
, issue the f
ollowing
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.864
9903547372








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




How t
o g
raphically view a route.

To view the rou
te 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,

13

target::integer,

length::double precision as cost

FRO
M roads
',

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


Then run
the following query in OpenJump

Run Datastore Query

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 (

S
ELECT * FROM shortest_path('

SELECT gid as id,

source::integer,

target::integer,

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_we
xford dw, roads r
where r.gid = dw.edge_id;


Run in SQL shell
.

CREATE VIEW dublin_galway

AS (

14

SELECT * FROM shortest_path('

SELECT gid as id,

source::integer,

target::integer,

length::double precision as cost

FROM roads
',

381, 48
, false, false));


Then run

the following query in OpenJump

Run Datastore Query

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






Understanding the topology

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


select ta
r
get fro
m 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 source from roads where target = 375;


source

--------


381

(1 row)


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



se
lect gid,source,target from

road
where source=381
;

15

gid | source | target

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


384 | 381 | 382


390 | 381 | 388


790 | 381 | 375

(3 rows)






Edges

(gid)
:






Source

16





Target




17

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











Measuring the 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

18

from dublin_cork as dc, roads as r whe
re r.gid =
dc.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 a
s dc, roads as r where r.gid =
dc.edge_id;


The straight lines distance is about
215
-
220 Kilometres

19