Embedded Files
Network analyses
Network analyses
In order to use network analysis functions the pgrouting extension should be created to the database where it is already created the postgis and hstore extension.
Use the following code to create the pgrouting extension
CREATE EXTENSION pgrouting;
Prepare the network data
Prepare the network data
Network analysis in pgRouting requires a topology for the network, which can be built from road networks.
For this, you need to build a routing graph based on the planet_osm_line or osm_roads table .
Here is how you can set up the routing graph:
Create a routing table
Create a routing table
First, you need to create a table for the network nodes and edges.
This table will store the network graph structure for pgRouting.
You can use the following sql code for it:
SELECT pgr_createTopology('planet_osm_line', 0.00001, 'geom', 'osm_id');
SELECT pgr_createTopology('planet_osm_line', 0.00001, 'geom', 'osm_id');
pgr_createtolology function takes the road network from planet_osm_lines table and computes the topology of the network
0.00001 is the tolerance, which determines the precision when matching nodes that are close to each other.
geom column, is the geometry column and osm_id is the identifier for road segments.
This function will create 2(two) important tables:
edges : this table contains all roads segments and their connection points
nodes : this table contains the netowork nodes, intersections and junctions
Example
Based on planet_osm_line, let-s createa road_network
create table road_network
as
select osm_id,way,name,highway,ST_LENGTH(way) as cost
from planet_osm_line
where highway is not null ;
We will need to add two more columns to the road_network table
alter table road_network add column source bigint;
alter table road_network add column target bigint;
Now we can create the network topology , or the graph in two tables the road network and nodes (intersections).
Function pgr_createTopology does this for us.
select pgr_createTopology('road_network',0.00001,'way','osm_id');
road_network is the table where the edges of the graph are
0.00001 is the tolearnce distance to consider two nodes the same
way is the geometry column storing the lines data
'osm_id' is the id of the edges
This command will create another table of the nodes, the table will have the same name with the edges table, road_network with a suffix _vertices_pgr
Let us now query our graph using dijkstra algorythm
SELECT * FROM pgr_dijkstra(
'SELECT osm_id as id, name, source, target, cost FROM road_network',
154, 156,
directed := true
);
For a better result will write
SELECT
route.seq,
route.node,
route.edge,
road_network.name AS road_name,
route.cost,
route.agg_cost,
road_network.way
FROM pgr_dijkstra(
'SELECT osm_id as id, source, target, cost FROM road_network',
154,156,
directed := true
) AS route
JOIN road_network
ON route.edge = road_network.osm_id
ORDER BY route.seq;
Routing Examples
Routing Examples
In this section will illustrate example scenarios
Scenario 1 - Build a road network for Ancona city
Scenario 1 - Build a road network for Ancona city
We have seen an example of creating an network topology from planet_osm_line table. In fact it is possible to create multiple network topologies ( graphs of nodes, edges and cost) within the same database.
Network Topology Table
Network Topology Table
As we have seen in the first example, in order to build a network topology the table should have e minimum of columns:
a record id column, osm_id in our first example
a cost column, we used the length of the line as a cost , ST_Length(way)
geometry column, which stored the geometry data of the line, named way in our example
a source (bigint) to reference the source node
target (bigint) column to reference the target node
The source and target columns are added with null values and it is the function pgr_createTopology that populates these two columns.
pgr_createTopology function
pgr_createTopology function
pgr_createTopology function:
creates a table with the same name as your network topology base table name adding the suffix vertices_pgr
Exctract the start and end point of each linestring in the net topology table
Insert each point in the vertices_pgr table assigning each of them a unique id ( primary key of the vertices_pgr table)
References those points in columns source and target at the base network topology table
pgr_createTopology function does not change the geometry of the network. It does not split lines.
Filtering planet_osm_lines for the roads within a city
Filtering planet_osm_lines for the roads within a city
As referenced in section Map Features , there is a well documented description of map features on OSM wiki https://wiki.openstreetmap.org/wiki/Map_features
In this example, we will provide some useful references to support the demonstration.
To filter the planet_osm_line table—which contains all line objects within the selected map (Central Italy)—we need to extract only those features located within the city of Ancona.
To do this, we can use the ST_Intersects function together with a polygon geometry that represents the administrative boundary of Ancona.
Find the polygon that represents the administrative boundary of Ancona
We will use columns
name = Ancona
boundary=administrative , means administrative boundary
admin-level=8 means municipality. Other values of admin_level are 2 -> Country, 4 -> Region, 6 -> Provincial , 8 -> Municipality, 10 -> Neighborhood
The complete query would be
select osm_id,name,boundary,admin_level,tags,way from planet_osm_polygon
where name='Ancona' and boundary='administrative'
The query returns two rows each representing respectively the province and the municipality
Filtering admin_level='8' (is text not integer) would give us the polygon of Ancona municipality
select osm_id,name,boundary,admin_level,tags,way from planet_osm_polygon
where name='Ancona' and boundary='administrative'
and admin_level='8'
returns the map of Ancona municipality
Now that we have the polygon representing the municipality of Ancona we could get all roads that Intersect with that polygon, meaning all roads that fall within Ancona municipality.
The query below produces the reqiured result
SELECT l.osm_id,l.highway,
ST_LENGTH(l.way) as cost,
l.name,l.tags->'ref' ref,
l.tags->'int_ref' int_ref,
l.tags->'nat_ref' nat_ref,
l.tags->'loc_name' loc_name,
l.tags,
ST_Intersection(l.way,p.way) way
FROM planet_osm_line l
JOIN planet_osm_polygon p
ON ST_Intersects(l.way, p.way)
WHERE p.name = 'Ancona'
AND p.boundary = 'administrative'
AND p.admin_level = '8' -- Optional: filter for city-level boundary
AND l.highway IS NOT NULL;
highway not null filters all type of roads. A null value os highay column would mean that the line represents another object such as railway, river, electricity lines etc.
Create the network topology table with the above query
create table ancona_road_network
as
SELECT l.osm_id,l.highway,
ST_LENGTH(l.way) as cost,
l.name,l.tags->'ref' ref,
l.tags->'int_ref' int_ref,
l.tags->'nat_ref' nat_ref,
l.tags->'loc_name' loc_name,
l.tags,
ST_Intersection(l.way,p.way) way
FROM planet_osm_line l
JOIN planet_osm_polygon p
ON ST_Intersects(l.way, p.way)
WHERE p.name = 'Ancona'
AND p.boundary = 'administrative'
AND p.admin_level = '8' -- Optional: filter for city-level boundary
AND l.highway IS NOT NULL;
Now that we have a road network table , we will add two columns to it for the source and target refernces of nodes.
This columns will be populated by the pgr_createTopology function.
alter table ancona_road_network add column source bigint;
alter table ancona_road_network add column target bigint;
Names source and target are indicative to the role of the columns. The name are recommended to be with these names, because as a default the functions expect them with those names.
Let's check that our network topology table is ready to call createTopology function.
It has:
osm_id bigint primary key identifying edges of the graph
cost column storing the cost of each grapsh edge
way column that stores geometry data
the two columns source and target that will refernce the source and end node of the line
Since the table is ready let us call the pgr_createTopology function
select pgr_createTopology('ancona_road_network',0.00001,'way','osm_id');
0.0001 is the tolerane to consider two points with a difference of 0.0001 as the same point.
Once the network topology is created , a new table with the same name but with the suffix vertices should have been created and columns source and target are populated with references to the vertices table.
The query below find the road between 2 points
SELECT * FROM pgr_dijkstra(
'SELECT osm_id as id, source, target, cost FROM ancona_road_network',
(
SELECT source
FROM ancona_road_network
ORDER BY way <-> (
SELECT way
FROM planet_osm_point
WHERE osm_id = 659630576
)
LIMIT 1
)
,
(
SELECT source
FROM ancona_road_network
ORDER BY way <-> (
SELECT way
FROM planet_osm_point
WHERE osm_id = 8795862297
)
LIMIT 1
)
,
directed := true
);
The founded way:
"seq" "path_seq" "start_vid" "end_vid" "node" "edge" "cost" "agg_cost"
1 1 4403 7581 4403 15496317 454.9774755840163 0
2 2 4403 7581 4372 39847145 488.0227842631542 454.9774755840163
3 3 4403 7581 881 39862049 204.8632176579954 943.0002598471706
4 4 4403 7581 882 148170641 313.5632762995545 1147.863477505166
5 5 4403 7581 4363 52135671 71.8829380818548 1461.4267538047204
6 6 4403 7581 4360 147227240 114.71370826276483 1533.3096918865751
7 7 4403 7581 4354 39993419 43.69335588535697 1648.02340014934
8 8 4403 7581 4603 50779468 93.93799109386931 1691.716756034697
9 9 4403 7581 4645 147227190 58.74592454334078 1785.6547471285664
10 10 4403 7581 4590 24576121 300.24270586211986 1844.4006716719073
11 11 4403 7581 4328 50779465 125.50059390762758 2144.643377534027
12 12 4403 7581 4322 24576114 17.001921528825214 2270.1439714416547
13 13 4403 7581 4321 1265752272 49.978476705095744 2287.14589297048
14 14 4403 7581 4325 19929044 307.7987790718293 2337.1243696755755
15 15 4403 7581 4661 1265752273 38.65230226662502 2644.923148747405
16 16 4403 7581 4692 24605691 856.4962730427177 2683.57545101403
17 17 4403 7581 3086 207944195 38.22796547673212 3540.071724056748
18 18 4403 7581 6637 207944194 48.69827057451068 3578.29968953348
19 19 4403 7581 6628 207944193 34.39899391530664 3626.997960107991
20 20 4403 7581 6616 207944196 147.74651867071805 3661.3969540232974
21 21 4403 7581 1967 1000927066 39.48367936680345 3809.1434726940156
22 22 4403 7581 1969 1000927065 213.30589543691946 3848.627152060819
23 23 4403 7581 1970 50971069 257.93032429081836 4061.9330474977382
24 24 4403 7581 10849 36853322 47.42011745686579 4319.863371788557
25 25 4403 7581 10845 31867631 101.4824084958711 4367.283489245423
26 26 4403 7581 10776 36853324 54.34040665462551 4468.765897741294
27 27 4403 7581 10774 22797108 46.71092605015826 4523.106304395919
28 28 4403 7581 10771 116663109 79.79257510713263 4569.817230446078
29 29 4403 7581 4242 116663095 213.63616904546325 4649.60980555321
30 30 4403 7581 4280 50971071 226.8385905126848 4863.2459745986735
31 31 4403 7581 3667 116663091 22.10262910094905 5090.084565111359
32 32 4403 7581 3645 50779453 62.75366712310027 5112.187194212308
33 33 4403 7581 3640 116663087 25.057964740584264 5174.940861335408
34 34 4403 7581 3637 116663111 23.769749525810518 5199.998826075992
35 35 4403 7581 3619 116663093 268.30951475988303 5223.768575601803
36 36 4403 7581 3620 43249084 28.941258097397665 5492.078090361686
37 37 4403 7581 3597 43249570 17.48190048152721 5521.019348459084
38 38 4403 7581 3431 1355727262 1082.2667757866752 5538.501248940611
39 39 4403 7581 1506 43249569 140.9501203058008 6620.768024727286
40 40 4403 7581 1462 24054941 49.90009340927024 6761.718145033086
41 41 4403 7581 1499 127010798 51.03094555928537 6811.618238442356
42 42 4403 7581 1543 50779456 12.242697709933289 6862.6491840016415
43 43 4403 7581 1530 181202379 114.84146468653573 6874.891881711575
44 44 4403 7581 1531 220659524 75.39995719743138 6989.73334639811
45 45 4403 7581 1575 1304747932 20.992271882530083 7065.133303595541
46 46 4403 7581 1574 116537547 59.31777970472277 7086.125575478071
47 47 4403 7581 1527 44221327 66.96823893807229 7145.443355182794
48 48 4403 7581 1525 116537490 262.3105800018656 7212.4115941208665
49 49 4403 7581 1521 44221328 61.30773376430693 7474.722174122732
50 50 4403 7581 1519 126984009 40.45090919478901 7536.029907887039
51 51 4403 7581 1520 147523329 37.235676328709076 7576.480817081829
52 52 4403 7581 1397 36873067 1778.742254100676 7613.716493410538
53 53 4403 7581 1398 1250099641 102.76761115392497 9392.458747511213
54 54 4403 7581 3780 28594615 517.1857293427842 9495.226358665139
55 55 4403 7581 10691 25560427 83.77950124248531 10012.412088007923
56 56 4403 7581 10692 62602473 140.1875040415612 10096.191589250408
57 57 4403 7581 10672 444829851 29.580118247289427 10236.37909329197
58 58 4403 7581 10671 444829852 37.18881980031317 10265.959211539259
59 59 4403 7581 10666 44221342 122.24428675848195 10303.148031339571
60 60 4403 7581 7603 884868947 125.72519529953681 10425.392318098053
61 61 4403 7581 7596 116537487 102.80892402488044 10551.11751339759
62 62 4403 7581 7583 28594549 88.35884405849751 10653.926437422471
63 63 4403 7581 7584 651230961 14.285763709706938 10742.285281480968
64 64 4403 7581 7582 60710944 79.37059546300087 10756.571045190676
65 65 4403 7581 7580 60710945 46.51822458764012 10835.941640653677
66 66 4403 7581 7581 -1 0 10882.459865241317
Let's complete it with road names
SELECT seq,Name as RoadName,trunc(a.cost) as Leng,a.way
FROM pgr_dijkstra(
'SELECT osm_id as id, source, target, cost FROM ancona_road_network',
(
SELECT source
FROM ancona_road_network
ORDER BY way <-> (
SELECT way
FROM planet_osm_point
WHERE osm_id = 659630576
)
LIMIT 1
)
,
(
SELECT source
FROM ancona_road_network
ORDER BY way <-> (
SELECT way
FROM planet_osm_point
WHERE osm_id = 8795862297
)
LIMIT 1
)
,
directed := true
) d
join
ancona_road_network a
on d.edge=a.osm_id;
The quesy above will show the name of the roads and the distance in meters.
"seq" "roadname" "leng"
1 "Corso Carlo Alberto di Savoia" 454
2 "Corso Carlo Alberto di Savoia" 488
3 "Via Generale Raffaele Pergolesi" 204
4 "Via Giordano Bruno" 313
5 "Via Giordano Bruno" 71
6 "Via Giordano Bruno" 114
7 "Via Giordano Bruno" 43
8 "Piazza Ugo Bassi" 93
9 "Piazza Ugo Bassi" 58
10 "Viale Cristoforo Colombo" 300
11 "Viale Cristoforo Colombo" 125
12 "Viale Cristoforo Colombo" 17
13 "Via della Marina" 49
14 "Via della Marina" 307
15 "Via della Marina" 38
16 "Via Montagnola" 856
17 "Via Montagnola" 38
18 "Via Montagnola" 48
19 "Via Montagnola" 34
20 "Via Montagnola" 147
21 "Via Montagnola" 39
22 "Via Montagnola" 213
23 "Via Montagnola" 257
24 "Via Achille Barilatti" 47
25 "Via Achille Barilatti" 101
26 "Via Achille Barilatti" 54
27 "Via Achille Barilatti" 46
28 "Via Achille Barilatti" 79
29 "Via Achille Barilatti" 213
30 "Via Achille Barilatti" 226
31 "Via Achille Barilatti" 22
32 "Via Achille Barilatti" 62
33 "Via Achille Barilatti" 25
34 "Via Achille Barilatti" 23
35 "Via Achille Barilatti" 268
36 "Via Achille Barilatti" 28
37 "Via Alessandro Maggini" 17
38 "Via Alessandro Maggini" 1082
39 "Via Alessandro Maggini" 140
40 "Strada di Pontelungo" 49
41 "Strada di Pontelungo" 51
42 "Strada di Pontelungo" 12
43 "Strada di Pontelungo" 114
44 "Strada di Pontelungo" 75
45 "Strada di Pontelungo" 20
46 "Strada di Pontelungo" 59
47 "Strada di Pontelungo" 66
48 "Strada di Pontelungo" 262
49 "Strada Vecchia Pinocchio" 61
50 "Strada Vecchia Pinocchio" 40
51 "Strada Vecchia Pinocchio" 37
52 "Strada Vecchia Pinocchio" 1778
53 "Strada Vecchia Pinocchio" 102
54 "Strada Vecchia Pinocchio" 517
55 "Strada Vecchia Pinocchio" 83
56 "Strada di Pontelungo" 140
57 "Strada di Pontelungo" 29
58 "Via Primo Maggio" 37
59 "Via Primo Maggio" 122
60 "Via Primo Maggio" 125
61 "Via Primo Maggio" 102
62 "Via Primo Maggio" 88
63 "Via Primo Maggio" 14
64 "Via Primo Maggio" 79
65 "Via Achille Grandi" 46
K-Nearest Neighbor (KNN) distance operator
K-Nearest Neighbor (KNN) distance operator
The <-> operator in PostGIS is called the K-Nearest Neighbor (KNN) distance operator. It’s used to quickly find the closest geometry to another, based on bounding box distance.
What it does
geom1 <-> geom2 returns the approximate distance between the bounding boxes of the two geometries.
It does not compute exact geometry distance (like ST_Distance does).
Instead, it’s used to speed up nearest-neighbor searches, especially with a spatial index.
This operator is used in our example to locate the nearest vertice in the road netowrk from our point of interest.
For example the query
SELECT trunc(way <-> (
SELECT way
FROM planet_osm_point
WHERE osm_id = 659630576
)
) DistanceInMeters
,*
FROM ancona_road_network
ORDER BY way <-> (
SELECT way
FROM planet_osm_point
WHERE osm_id = 659630576
)
LIMIT 5
Returns the 5 nearest roads to our point 659630576.
Table below shows the differences between the KNN operator and ST_Distance function
pgr_dijksta function - explained
pgr_dijksta function - explained
pgr_dijkstra implements the dijkstra algorythm, a classic shortest path algorythm that computes minimum-cost path between two nodes in a directed or undirected graph. It is used in routing applications and it uses the edges table ( typically a road network)
Syntax:
pgr_dijkstra(
sql TEXT,
source BIGINT,
target BIGINT,
directed BOOLEAN := true
)
sql - The first argument is an sql string which is expected to return the edge table, road network. this query it should return the following columns:
id - Unique Id of the edge
source - Id of the source node of the edge (start of the edge)
target- Id of the target node ( the end of the edge )
cost - a numeric value, reppresentig the cost to travel from source to end node, navigating the edge. It can be the length of the edge, the estimated time to navigate the edge based on speed limit or another formula indicating the cost of the edge
reverse_cost - cost to travel from target to source ( for directed graphs)
If reverse_cost is -1, means that the edge is one way, is not possible to navigate in the opposite direction
source and target, are the id of the start and the end nodes of the path to find.
directed
If true the graph is treated as directed
If false can go both ways and ignored reverse cost.
What the function returns
It returns a set of rows with the following structure:
Page updated
Report abuse