Get documents about "Using PostgreSQL and PostGIS as a Spatial Da - PowerPoint
Document Sample
Outline
We will look at Dijkstra‟s Shortest Path
Algorithm.
The issues in real world way-finding.
The additional algorithms available in
pgRouting
OGC Location Services (OpenLS): Core
Services
Permanent Labels Temporary Labels
V the set of vertices in the graph, xpaths are next steps
F set of vertices whose shortest-path lengths remains to be
calculated
V – F (permanent labels) set of vertices whose shortest-path
lengths have been calculated
Each iteration moves one vertex from F to (V – F)
Dijkstra‟s Shortest Path Algorithm
Dijkstra‟s algorithm (BFS) for finding the
shortest from a to v, involves assigning
labels to vertices. Let L(v) denote the
label of vertex v. At any point, some
vertices have temporary labels and the
rest have permanent labels, these are
shown in red in the following slides. We let
T denote the set of vertices having
temporary labels.
Example: Find path a to z
b 2 c
2 1
4 3
a 2 z
4
d e
1 6
7
3
f g
5
Example: Find path a to z
b 2 c
2 1
0 4 3
a 2 z
4
d e
Set cost to
a=0 and cost 1 6
to other 7
3
vertices
infinity()
f g
5
After First Iteration a to z
Current
vertex a
2 b 2 c
2 1
3
2 4
a z
4
1 d e
Labels
L(b) = min{,0+2} 6
L(f) = min{,0+1} 7
3
1 f g
5
Choose v ∊ T with minimum L(v) = f
After Second Iteration a to z
Current
vertex f
2 b 2 c
2 1
4 3
a 2 z
Labels
L(b) = min{,0+2}
1 4
L(f) = min{,0+1} 4 d e
L(d) = min{,0+1+3}
L(g) = min{,0+1+5} 6
How do we 7
3 know that
this is the
shortest
path to f
1 f g
5 6
Choose v ∊ T with minimum L(v) = b
After Third Iteration a to z
Current
vertex b
2 b 2 c 4
2 1
4 3
a 2 z
Labels
L(b) = min{,0+2}
6
1 4
L(f) = min{,0+1} 4 d e
L(d) = min{,0+1+3}
L(g) = min{,0+1+5} 6
7
L(c) = min{,0+2+2} 3
L(d) = min{4,0+2+2} --dif. path
L(e) = min{,0+2+4}
f g
5 6
1
Choose v ∊ T with minimum L(v) = c
Fourth Iteration a to z
Current
vertex c
2 b 2 c 4
2 1
4 3
a 2 z 5
Labels
L(b) = min{,0+2} 6
1 4
L(f) = min{,0+1} 4 d e
L(d) = min{,0+1+3}
L(g) = min{,0+1+5} 6
L(c) = min{,0+2+2} 7
3
L(d) = min{4,0+2+2} dif. Path
L(e) = min{,0+2+4}
L(e) = min{6,0+2+2+3} dif. Path f g
L(z) = min{,0+2+2+1} 5 6
1
Choose v ∊ T with minimum L(v) = z
Final Iteration a to z
Current
vertex z
2 b 2 c 4
2 1
4 3
a 2 z 5
Labels
L(b) = min{,0+2} 6
4
L(f) = min{,0+1} 1 4 d e
L(d) = min{,0+1+3}
L(g) = min{,0+1+5} 6
L(c) = min{,0+2+2} 7
3
L(d) = min{4,0+2+2} dif. Path
L(e) = min{,0+2+4}
L(e) = min{6,0+2+2+3} dif. Path f g
L(z) = min{,0+2+2+1} 1 5 6
Terminate zT
UML For Dijkstra‟s Shortest Path
See
http://www.comp.dit.ie/pbrowne/Spatial%20Databases%20SDEV4005/DijkstraJava.htm
Another Graph: Init
b 3
c
2
2
0 1
a z
1
2
1
d e
Choose v ∊ T with minimum L(v)
Another Example: 1
a.2
b 3
c
2
2
0 1
a z
1
Labels 2
L(b) = min{,0+2} 1
L(d) = min{,0+1} d e
a,1
Chose (v= d ) ∊ T as minimum L(v)
Another Example: 2
a,2
b 3
c
2
2
0 1
a z
1
Labels 2
L(b) = min{,0+2} 1
L(d) = min{,0+1} d e
L(e)=min{,0+1+1}
a.1 d.2
Chose (v= b ) ∊ T as minimum L(v)
Could have picked e, path to both is 2.
Another Example: 3
a,2
3
b c b,5
2
2
0 1
a z
1
Labels 2
L(b) = min{,0+2} 1
L(d) = min{,0+1} d e
L(e)=min{,0+1+1}
a.1 d.2
L(c)=min{,0+2+3}
L(e)=min{,0+2+1} diff path
Chose (v= e) ∊ T as minimum L(v)
Another Example: 4
a,2
3
b c b,5
2
2
0 1
a z e,4
1
Labels 2
L(b) = min{,0+2} 1
L(d) = min{,0+1} d e
L(e)=min{,0+1+1}
a.1 d.2
L(c)=min{,0+2+3}
L(e)=min{,0+2+1} diff path
L(z)=min{,0+1+1+2}
Chose (v=z) ∊ T as minimum L(v)
Another Example: 4
a,2
3
b c b,5
2
2
0 1
a z e,4
1
Labels 2
L(b) = min{,0+2} 1
L(d) = min{,0+1} d e
L(e)=min{,0+1+1}
a.1 d.2
L(c)=min{,0+2+3}
L(e)=min{,0+2+1} diff path
L(z)=min{,0+1+1+2}
L(c)=min{5,0+1+1+2+2} diff path Terminate zT
Distance, n1
Parent, 19 n2
Included, 8
15 12
Infinity 12
14
n4 n3
Initial Table 11
n dist par inc n5 10
19
1 inf n
13
2 inf n
n6
3 inf n
4 inf n
Find Distance from n1 to n6
5 inf n
6 inf n
Distance, n1
Parent, 19 n2
Included, 8
15 12
Infinity 12
14
n4 n3
Initial Table 11
n dist par inc n5 10
19
1 inf n
13
2 inf n
n6
3 inf n
4 inf n
Find Distance from n1 to n6
5 inf n
6 inf n
Distance, n1
Parent, 19 n2
Find Distance from n1 to n6
Included, 8
15 12
Infinity 12
14
n4 n3
After iter 1 11
n dist par inc n5 10
0 none y 19
1 13
2 19 1 n n6
3 inf n
4 15 1 n
5 12 1 n
6 inf n
Distance, n1
Parent, 19 n2
Find Distance from n1 to n6
Included, 8
15 12
Infinity 12
n4 14
After iter 2 11 n3
n dist par inc n5 10
0 none y 19
1 13
2 19 1 n n6
3 inf n
4 15 1 n
5 12 1 y
6 31 5 n
Distance, n1
Parent, 19 n2
Find Distance from n1 to n6
Included, 8
15 12
Infinity 12
n4 14
After iter 3 11 n3
n dist par inc n5 10
0 none y 19
1 13
2 19 1 n n6
3 29 4 n
4 15 1 y
5 12 1 y
New shorter
6 25 4 n distance
Distance, n1
Parent, 19 n2
Find Distance from n1 to n6
Included, 8
15 12
Infinity 12
n4 14
After iter 4 11 n3
n dist par inc n5 10
0 none y 19
1 13
2 19 1 y n6
3 27 4 n
4 15 1 y
5 12 1 y
New shorter
6 25 4 n distance
Distance, n1
Parent, 19 n2
Find Distance from n1 to n6
Included, 8
15 12
Infinity 12
n4 14
After iter 5 11 n3
n dist par inc n5 10
0 none y 19
1 13
2 19 1 y n6
3 27 4 n
4 15 1 y
5 12 1 y
6 25 4 y
Distance, n1
Parent, 19 n2
Find Distance from n1 to n6
Included, 8
15 12
Infinity 12
n4 14
After iter 5 11 n3
n dist par inc n5 10
0 none y 19
1 13
2 19 1 y n6
3 27 4 n The shortest path from n1 to n4 is 15,
4 15 1 y The shortest path from n1 to n6 (via n4) is 15+10=25,
5 12 1 y
6 25 4 y
Yet another example
Find the shortest path from a to f in the graph G
Yet another example
Each node is
labelled with total
distance and
predecessor node
Find the shortest path from a to f in the graph G
All paths example
Find the shortest distance from v1 to all other vertices
The shortest distance to each vertex can be read off the bottom row.
All paths example
Find the shortest distance from v1 to all other vertices
The shortest distance to each vertex can be read off the bottom row.
Steps
Computer Graph Representation 1
Adjacency matrix v w x y
Rows and columns v 0 1 0 1
are labeled with w 1 0 1 1
ordered vertices
write a 1 if there is an x 0 1 0 1
edge between the y 1 1 1 0
row vertex and the
column vertex and 0
if no edge exists
between them
Computer Graph Representation 2
e f g h j
Incidence matrix
Label rows with vertices v 1 1 0 0 0
Label columns with edges w 1 0 1 0 1
1 if an edge is incident to
x 0 0 0 1 1
a vertex, 0 otherwise
y 0 1 1 1 0
Issues when implementing networks
and route finding in GIS(*)
Real world networks (e.g. roads) tend to have many
intermediate non-branching vertices (e.g. polylines)
which contribute to large adjacency matrices.
Networks often multi leveled (e.g walk, bus , train, bus,
ferry, car).
In some cases shortest path problems are subject to
additional „thematic‟ or „semantic‟ constraints. (e.g.
road type or quality of transmission line) Constrained
Shortest Path Problem (CSPP).
Issues when implementing networks
and route finding in GIS(*)
Basic Dijkstra-type algorithms provide
inconsistent run-times on real-world networks,
principally because such networks have very
limited vertex connectivity (their adjacency
matrices are very sparse)1.
Most network algorithms are 2-D, route on map
maybe be in 3-D, (e.g. cross mountains).
Issues when implementing networks
and route finding in GIS
The OGC Simple Feature (SFSQL) standard used in
PostGIS does not include any network representation,
therefore an additional Network ADT must be provided,
which must integrate with the existing standard. Ideally
we would want to avoid two distinct non-compatible
ADTs. The existing OGC data structures and algorithms
for 2-dimensional objects contain polygons, edges, and
vertices and must be augmented with connectivity
information. Also, the OGC SFSQL must integrate with
the 1-D route finding algorithms and network data
structures. The Geographic Markup Language (GML1)
provides network support.
Issues when using networks and
route finding in GIS(*)
The user must be able to understand how
to use the Network ADT (e.g. pgRouting).
User must insure that the data in the
appropriate format for the network ADT.
This may require manual or software
conversion of the data.
pgRouting: preperation
A simple line graph needs to be transformed to include network
topology, with connective nodes and edges, by issuing this
command in the SQL Shell or pgAdmin SQL Query tool:
SELECT assign_vertex_id('edges', 0.001, 'the_geom', 'gid');
is a function included with the pgRouting extension.
assign_vertex_id()
This is explained in the tutorial.
In general, we need to update a table by adding a numerical value
to a column that we previously created when issued the CREATE TABLE
statement.
UPDATE edges SET length = length(the_geom);
This creates a length field to hold the length value of network
edges. This can be a cost. We would also use the same statement
to update a “cost” column.
pgRouting
Now we can use any of the GIS functions
provided by PostGIS and pgRouting.
Dijkstra‟s algorithm is a good single-
source routing algorithm provided by
pgRouting.
Methods for Analysis
To run the query and get an answer in
WKT (Well-Known-Text), you can issue
this command in pgAdmin‟s SQL Query
tool:
SELECT gid, AsText(the_geom) AS the_geom
FROM dijkstra_sp('edges', 1, 8);
Starting node = 1
Destination node = 8
„edges‟ = the table containing the network
Results
Dijkstra‟s results expressed in WKT:
The “gid” is synonymous with “FID” in ArcGIS, and “the_geom” in
this case is expressed in WKT. The numbers shown represent
coordinate pairs that make up points along a polyline.
WKT is an OGC (Open Geospatial Consortium) standard.
In order to view the result in OpenJump we usually include the
additional steps of making a result table or using an SQL view.
Details are in the labs.
Dijkstra, A*, Shooting*
Anton Patrushev Shooting * includes turn restrictions, not covered on this course.
Dijkstra, A*, Shooting*
Anton Patrushev (Orkney, Inc.)
Dijkstra, A*, Shooting*
Anton Patrushev (Orkney, Inc.)
A heuristic algorithm
A heuristic algorithm is an algorithm that is
able to produce an acceptable solution to
a problem in many practical scenarios, but
for which there is no formal proof of its
correctness. The algorithm uses a “rule of
thumb” or “educated guess” to help find a
solution.
A-Star a heuristic algorithm
A* takes uses the distance so far plus cost
heuristic.
Two distinctions between Dijkstra and A*.
A* uses a heuristic e.g. remaining distance.
A* uses the distance already traveled and not
simply the local cost from the previously
expanded node. This in contrast to Dijkstra
which is a greedy algorithm. Dijkstra can be
used when the estimate to the goal is difficult
or impossible to calculate.
A-Star a heuristic algorithm
A* takes the distance already traveled into
account and not simply the local cost from
the previously expanded node. For each
node x traversed, it maintains 3 values:
g(x) : the actual shortest distance traveled
from initial node to current node x.
h(x) : the estimated (or "heuristic") distance
from current node to goal
f(x) : the sum of g(x) and h(x).
A-Star a heuristic algorithm
The heuristics h(x) must not be an
overestimate of the distance to the goal.
The Euclidean (or straight line) distance is
always less than or equal to the road
distance.
pgRouting data structure
For further details of the data pgRouting structures see
http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007
http://pgrouting.postlbs.org/wiki
Computational complexity1
Computational complexity theory deals
with the costs of computing a solution
given problem. Costs can be measured in
terms of time (e.g. number of iterations),
space( e.g. memory required), best,
average and worst cases.
CC can be applied to a wide range of
algorithms including shortest path (e.g.
Dijkstra and A*).
Complexity of
Dijkstra MathWorld--A Wolfram Web Resource
The worst-case running time for the Dijkstra
algorithm on a graph with n nodes and m edges
is O(n2) because it allows for directed cycles. It
can find the shortest paths from a source node s
to all other nodes in the graph. This is basically
O(n2) for node selection and O(m) for distance
updates. While O(n2) is the best possible
complexity for dense graphs, the complexity can
be improved significantly for sparse graphs.
Complexity of
A* Wikipedia
The time complexity of A* depends on the heuristic. In
the worst case, the number of nodes expanded is
exponential in the length of the solution (the shortest
path), but it is polynomial when the search space is a
tree, there is a single goal state, and the heuristic
function h meets the following condition:
| h(x) − h*(x) | = O(log h*(x))
where h* is the optimal heuristic, i.e. the exact cost to get
from x to the goal. The error of h should not grow faster
than the logarithm of the “perfect heuristic” h* that
returns the true distance from x to the goal
Gateway Service Details
Gateway Mobile Location Center
Position
Determination
Entity
GMLC/MPC: this interface is modeled
after the Mobile Location Protocol (MLP),
Standard Location Immediate Service.
OGC OpenLS
Standards with respect to LBSs have been set up by the
International Standard Organisation (ISO) and by the
Open Geospatial Consortium (OGC). The OGS has
released the Open Location Services (OpenLS - Open
Geospatial Consortium 2005). OpenLS defines core
services, their access and abstract data types which
form together a framework for a open service platform,
the so called GeoMobility server. The server acts as
application server and should process and answer core
service requests. The role of this server is pictured in the
previous slide. It should be noticed that service requests
to a GeoMobility server can be send from a mobile user,
from Internet users and also from other application
servers.
Applications of LBS
Traffic Information: there is a traffic queue, so turn right.
Emergency Services: help, I'm having a heart attack!
Roadside Emergency; “help, my car has broken down!
Law Enforcement: what is the speed limit on this road
Classified Advertising; where are nearby sales featuring antiques
Object visualization: where is the historic parcel boundary
Underground Object Visualization: “where is the water main?”
Public Safety Vehicle Mgt: who is closest to that emergency?”
Location-Based Billing; free calls on your phone, in a location
Leisure Information: How do we get to the Club from here
Road Service Information: Where is the nearest petrol station
Directions: I'm lost, where is nearest Metro station
Vehicle Navigation; how do I get back to the Interstate from here?
Vehicle Theft Detection: my car has been stolen, where is it
Child Safety: tell me if my child strays beyond the neighborhood.
Location Based Services
Please read Navigation Systems: A Spatial Database Perspective
http://www-users.cs.umn.edu/~shekhar/research/lbsChapter.pdf
OGS OpenLS
Location Utilities Service
The OpenLS utilities specification provides two
services, geocoding and reverse geocoding, and
an abstract data type named as Address. The
geocoder service is a network-accessible
service that transforms a description of a
location into a normalized description of the
location with point geometry. The reverse
geocoder service maps a given position into a
normalized description of a feature location.
Directory Service
The directory service provides a search
capability for one or more points of interest (e.g.,
a place, product, or service with a fixed position)
or area of interest (e.g., a polygon or a bounding
box). The DS is a network-accessible service
that provides access to an online directory (a
kind of spatial Yellow Pages) to find the location
of a specific or nearest place, product or service.
An example query is „„Where is the nearest
thesis binder to Kevin Street?‟‟
Presentation Service.
This service deals with visualization of the
spatial information as a map, route, and/or
textual information (e.g., route
description). The Presentation (Map
Portrayal) Service portrays a map made
up of a base map derived from any
geospatial data and a set of Abstract Data
Types as overlays.
Gateway Service
This service enables obtaining the position of a
mobile terminal from the network. The Gateway
Service is a network-accessible service that
fetches the position of a known mobile terminal
from the network. The gateway is the interface
between the GeoMobility Server and the
Location Server from the Positioning Service. It
is useful to request for the current location with
different modes (e.g. multi or single terminal,
immediate or periodic position).
Gateway Service Details
Gateway Mobile Location Center
Position
Determination
Entity
GMLC/MPC: this interface is modeled
after the Mobile Location Protocol (MLP),
Standard Location Immediate Service.
Route Determination Service
This service provides the ability to find a
best route between two points that
satisfies user constraints. RDS determines
travel routes and navigation information
according to diverse criteria.
Suggested reading
Paper
Navigation Systems: A Spatial Database Perspective by Shashi
Shekhar, Ranga Raju Vatsavai, Xiaobin Ma, and Jin Soung
Yoo.
http://www-users.cs.umn.edu/~shekhar/research/lbsChapter.pdf
Book: Location-Based Services: by Schiller & Voisard
Web
http://www.opengeospatial.org/standards/ols
http://pgrouting.postlbs.org/
http://www.openrouteservice.org/
http://www.davidgis.fr/documentation/pgrouting-
1.02/http://www.utdallas.edu/~ama054000/rt_tutorial.html
http://www.directionsmag.com/article.php?article_id=2876&trv=1
http://www.geographie.uni-bonn.de/karto/
