3. Good Health and Well Being¶
Good Health and Well Being is the 3rd Sustainable Development Goal which aspires to ensure health and well-being for all, including a bold commitment to end the epidemics like AIDS, tuberculosis, malaria and other communicable diseases by 2030. It also aims to achieve universal health coverage, and provide access to safe and effective medicines and vaccines for all. Supporting research and development for vaccines is an essential part of this process as well as expanding access to affordable medicines. Hospitals are a very important part of a well functioning health infrastructure. An appropriate planning is required for optimal distribution of the population of an area to its hospitals. Hence, it is very important to estimate the number of dependant people living near the hospital for better planning which would ultimately help in achieving universal coverage of health services. This chapter will focus on solving one such problem.
3.1. Problem: Estimation of Population Served by Hospitals¶
Problem Statement
To determine the population served by a hospital based on travel time
Core Idea
Population residing along the roads which reach to a hospital within a particular time is dependant on that hospital.
Approach
To prepare a dataset with:
Edges: Roads
Polygons: Buildings with population
Find the travel-time based the roads served
Estimate the population of the buildings
Find the nearest road to the buildings
Store the sum of population of nearest buildings in roads table
Find the sum of population on all the roads in the roads served
3.2. PostreSQL basics¶
3.2.1. Preparing work area¶
The search_path
is a variable that determines the order in which database
schemas are searched for objects.
By setting the search_path
to appropriate values, prepending the schema name
to tables can be avoided.
3.2.1.1. Exercise 1: Inspecting schemas¶
Inspect the schemas by displaying all the present schemas using the following command
\dn
List of schemas
Name | Owner
-----------+-------------------
buildings | runner
public | pg_database_owner
roads | runner
(3 rows)
The schema names are buildings
, roads
and public
. The owner depends
on who has the rights to the database.
3.2.1.2. Exercise 2: Inspecting the search path¶
Display the current search path using the following query.
SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
This is the current search path. Tables in other schemas cannot be accessed with this path.
3.2.1.3. Exercise 3: Fixing the search path¶
In this case, the search path needs to include roads
and
buildings
schemas. The following query is used to adjust the search path.
SET search_path TO roads,buildings,public,contrib,postgis;
SET
Checking the search path again
SHOW search_path;
search_path
--------------------------------------------
roads, buildings, public, contrib, postgis
(1 row)
3.2.1.4. Exercise 4: Enumerating tables¶
With \dt
the tables are listed showing the schema and the owner
\dt
List of relations
Schema | Name | Type | Owner
-----------+-----------------------------+-------+--------
buildings | buildings_pointsofinterest | table | user
buildings | buildings_ways | table | user
buildings | buildings_ways_vertices_pgr | table | user
public | spatial_ref_sys | table | runner
roads | configuration | table | user
roads | roads_pointsofinterest | table | user
roads | roads_ways | table | user
roads | roads_ways_vertices_pgr | table | user
(8 rows)
3.3. Preparing roads and buildings data¶
First step is to prepare the data obtained from Data for Sustainable Development Goals.
This section will work the graph and data that is going to be used for processing. While building the graph, the data has to be inspected to determine if there is any invalid data. This is a very important step to make sure that the data is of required quality. pgRouting can also be used to do some Data Adjustments.
3.3.1. Exercise 5: Counting the number of roads and buildings¶
The importance of counting the information on this workshop is to make sure that the same data is used and consequently the results are same. Also, some of the rows can be seen to understand the structure of the table and how the data is stored in it.
SELECT COUNT(*) FROM roads_ways;
count
-------
558
(1 row)
SELECT COUNT(*) FROM buildings_ways;
count
-------
1066
(1 row)
Following image shows the roads and buildings visualised.
3.3.2. Preprocessing Buildings¶
The table buildings_ways
contains the buildings in LINGSTING
type.
They have to be converted into polygons to get the area, as the area is going to
be used to get an estimate of the population living in the building.
3.3.2.1. Exercise 6: Removing columns¶
Columns can be deleted from a table. In this case instead of creating a view,
columns that are not related to a buidling concept are dropped from
buildings_ways
.
ALTER TABLE buildings.buildings_ways
DROP source, DROP target,
DROP source_osm, DROP target_osm,
DROP length, DROP length_m,
DROP cost, DROP reverse_cost,
DROP cost_s, DROP reverse_cost_s,
DROP one_way, DROP oneway,
DROP priority, DROP osm_id, DROP rule,
DROP x1, DROP x2,
DROP y1, DROP y2,
DROP maxspeed_forward,
DROP maxspeed_backward;
ALTER TABLE
3.3.2.2. Exercise 7: Add a spatial column to the table¶
Add a spatial column named poly_geom
to the table buildings_ways
to store
the Polygon Geometry
SELECT AddGeometryColumn('buildings','buildings_ways','poly_geom',4326,'POLYGON',2);
addgeometrycolumn
-------------------------------------------------------------------
buildings.buildings_ways.poly_geom SRID:4326 TYPE:POLYGON DIMS:2
(1 row)
Inspecting the table:
\dS+ buildings_ways
Table "buildings.buildings_ways"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------------------------+-----------+----------+---------------------------------------------+----------+-------------+--------------+-------------
gid | bigint | | not null | nextval('buildings_ways_gid_seq'::regclass) | plain | | |
tag_id | integer | | | | plain | | |
name | text | | | | extended | | |
the_geom | geometry(LineString,4326) | | | | main | | |
poly_geom | geometry(Polygon,4326) | | | | main | | |
Indexes:
"buildings_ways_pkey" PRIMARY KEY, btree (gid)
"buildings_ways_the_geom_idx" gist (the_geom)
Foreign-key constraints:
"buildings_ways_tag_id_fkey" FOREIGN KEY (tag_id) REFERENCES buildings.configuration(tag_id)
Access method: heap
Options: autovacuum_enabled=false
3.3.2.3. Exercise 8: Removing the polygons with less than 4 points¶
ST_NumPoints
is used to find the number of points on a geometry. Also, polygons
with less than 3 points/vertices are not considered valid polygons in PostgreSQL.
Hence, the buildings having less than 3 vertices need to be cleaned up. Follow
the steps given below to complete this task.
DELETE FROM buildings_ways
WHERE ST_NumPoints(the_geom) < 4
OR ST_IsClosed(the_geom) = FALSE;
DELETE 17
3.3.2.4. Exercise 9: Creating the polygons¶
ST_MakePolygons
is used to make the polygons. This step stores the geometry of
polygons in the poly_geom
column which was created earlier.
1UPDATE buildings_ways
2SET poly_geom = ST_MakePolygon(the_geom);
UPDATE 1049
3.3.2.5. Exercise 10: Calculating the area¶
After getting the polygon geometry, next step is to find the area of the polygons. Follow the steps given below to complete this task.
Adding a column for storing the area
ALTER TABLE buildings_ways ADD COLUMN area INTEGER;
Storing the area in the new column
ST_Area
is used to calculate area of polygons. Area is stored in the
new column
UPDATE buildings_ways
SET area = ST_Area(poly_geom::geography)::INTEGER;
UPDATE 1049
3.3.2.6. Exercise 11: Estimating the population¶
Due to the lack of census data, this exercise will fill up and estimate of the population living on the buildings, based on the area of the building and the kind of use the building gets.
Buildings of OpenStreetMap data are classified into various categories.
SELECT DISTINCT tag_id, tag_value
FROM buildings_ways JOIN buildings.configuration USING (tag_id)
ORDER BY tag_id;
tag_id | tag_value
--------+-------------
119 | retail
122 | school
124 | commercial
201 | university
314 | hotel
318 | hospital
401 | residential
402 | yes
501 | apartments
(9 rows)
For this exercise, the population will be set as follows:
Negligible:
People do not live in these places.
Population: 1 person
There may be people guarding the place.
Very Sparse:
retail
,commercial
,school
People do not live in these places.
Population: At least 2 persons.
Because there may be people guarding the place.
Sparse:
Buildings with low population density, like
university
.Population: At least 3 persons.
Because there may be people guarding the place.
Students might live there.
Moderate:
Location where people might be living temporarly, like
hotel
andhospital
.Population: At least 5 persons.
Dense:
A medium sized residential building.
Population: At least 7 persons.
Very Dense:
A large sized residential building, like
apartments
.Population: At least 10 persons.
Reference: Appendix
This class-specific factor is multiplied with the area of each building to get the population. Follow the steps given below to complete this task.
Create a function to find population using class-specific factor and area.
CREATE OR REPLACE FUNCTION population(tag_id INTEGER,area INTEGER)
RETURNS INTEGER AS
$BODY$
DECLARE
population INTEGER;
BEGIN
IF tag_id <= 100 THEN population = 1; -- Negligible
ELSIF 100 < tag_id AND tag_id <= 200 THEN population = GREATEST(2, area * 0.0002); -- Very Sparse
ELSIF 200 < tag_id AND tag_id <= 300 THEN population = GREATEST(3, area * 0.002); -- Sparse
ELSIF 300 < tag_id AND tag_id <= 400 THEN population = GREATEST(5, area * 0.05); -- Moderate
ELSIF 400 < tag_id AND tag_id <= 500 THEN population = GREATEST(7, area * 0.7); -- Dense
ELSIF tag_id > 500 THEN population = GREATEST(10,area * 1); -- Very Dense
ELSE population = 1;
END IF;
RETURN population;
END;
$BODY$
LANGUAGE plpgsql;
CREATE FUNCTION
Note
All these are estimations based on this particular area. More complicated functions can be done that consider height of the apartments but the design of a function is going to depend on the availability of the data. For example, using census data can achieve more accurate estimation.
Add a column for storing the population in the
buildings_ways
ALTER TABLE buildings_ways ADD COLUMN population INTEGER;
ALTER TABLE
3. Use the population
function to store the population in the new column created
in the building_ways
.
UPDATE buildings_ways
SET population = population(tag_id,area);
UPDATE 1049
3.3.3. Preprocessing Roads¶
pgRouting algorithms are only useful when the road network belongs to a single graph (or all the roads are connected to each other). Hence, the disconnected roads have to be removed from their network to get appropriate results. This image gives an example of the disconnected edges.
For example, in the above figure roads with label 119
are disconnected from
the network. Hence they will have same connected component number. But the count
of this number will be less count of fully connected network. All the edges
with the component number with count less than maximum count will be removed
Follow the steps given below to complete this task.
3.3.3.1. Exercise 12: Remove disconnected components¶
To remove the disconnected components on the road network, the following pgRouting functions, discussed on Graph views, will be used:
pgr_extractVertices
pgr_connectedComponents
Create a vertices table.
SELECT * INTO roads.roads_vertices
FROM pgr_extractVertices(
'SELECT gid AS id, source, target
FROM roads.roads_ways ORDER BY id');
Fill up the x
, y
and geom
columns.
UPDATE roads_vertices SET geom = ST_startPoint(the_geom)
FROM roads_ways WHERE source = id;
UPDATE roads_vertices SET geom = ST_endPoint(the_geom)
FROM roads_ways WHERE geom IS NULL AND target = id;
UPDATE roads_vertices set (x,y) = (ST_X(geom), ST_Y(geom));
UPDATE 327
UPDATE 0
UPDATE 353
Add a component
column on the edges and vertices tables.
ALTER TABLE roads_ways ADD COLUMN component BIGINT;
ALTER TABLE roads_vertices ADD COLUMN component BIGINT;
Fill up the component
column on the vertices table.
UPDATE roads_vertices SET component = c.component
FROM (
SELECT * FROM pgr_connectedComponents(
'SELECT gid as id, source, target, cost, reverse_cost FROM roads_ways')
) AS c
WHERE id = node;
UPDATE 353
Fill up the component
column on the edges table.
UPDATE roads_ways SET component = v.component
FROM (SELECT id, component FROM roads_vertices) AS v
WHERE source = v.id;
UPDATE 529
Get the component number with the most number of edges.
WITH
all_components AS (SELECT component, count(*) FROM roads_ways GROUP BY component),
max_component AS (SELECT max(count) from all_components)
SELECT component FROM all_components WHERE count = (SELECT max FROM max_component);
component
-----------
1
(1 row)
Delete edges not belonging to the most connected component.
WITH
all_components AS (SELECT component, count(*) FROM roads_ways GROUP BY component),
max_component AS (SELECT max(count) from all_components),
the_component AS (SELECT component FROM all_components WHERE count = (SELECT max FROM max_component))
DELETE FROM roads_ways WHERE component != (SELECT component FROM the_component);
DELETE 0
Delete vertices not belonging to the most connected component.
WITH
all_components AS (SELECT component, count(*) FROM roads_vertices GROUP BY component),
max_component AS (SELECT max(count) from all_components),
the_component AS (SELECT component FROM all_components WHERE count = (SELECT max FROM max_component))
DELETE FROM roads_vertices WHERE component != (SELECT component FROM the_component);
DELETE 0
3.4. Find the roads served by the hospitals¶
After pre-processing the data, next step is to find the area served by the
hospital. This area can be computed from the entrance of the hospital or from any
point on road near the hospital. In this exercise it is computed from closest
road vertex. pgr_drivingDistance
will be used to find the roads served. The
steps to be followed are:
Finding the closest road vertex
Finding the roads served
Generalising the roads served
3.4.1. pgr_drivingDistance¶
For the next step pgr_drivingDistance
will be used. This returns the driving
distance from a start node. It uses the Dijkstra algorithm to extract all the nodes
that have costs less than or equal to the value distance. The edges that are extracted
conform to the corresponding spanning tree.
Signatures
pgr_drivingDistance(edges_sql, start_vid, distance [, directed])
pgr_drivingDistance(edges_sql, start_vids, distance [, directed] [, equicost])
RETURNS SET OF (seq, [start_vid,] node, edge, cost, agg_cost)
pgr_drivingDistance Documentation can be found at this link for more information.
3.4.2. Exercise 13: Find the closest road vertex¶
There are multiple road vertices near the hospital. Create a function to find
the geographically closest road vertex. closest_vertex
function takes geometry
of other table as input and gives the gid of the closest vertex as output by
comparing geom
of both the tables.
The following query creates a function to find the closest road vertex.
CREATE OR REPLACE FUNCTION closest_vertex(geom GEOMETRY)
RETURNS BIGINT AS
$BODY$
SELECT id FROM roads_vertices ORDER BY geom <-> $1 LIMIT 1;
$BODY$
LANGUAGE SQL;
CREATE FUNCTION
Testing the function
SELECT closest_vertex(poly_geom) FROM buildings_ways;
closest_vertex
----------------
293
120
120
234
112
99
241
153
360
41
257
374
206
174
287
235
235
100
328
185
328
175
226
72
224
37
37
375
375
312
312
312
312
311
311
311
311
311
311
103
183
120
183
120
183
183
270
267
120
183
267
270
267
270
3
3
175
121
121
3
141
64
141
53
276
276
5
65
50
32
32
31
49
26
5
49
31
16
15
295
31
26
6
76
30
295
26
16
295
26
26
29
75
27
75
27
28
26
15
47
26
45
6
48
75
46
26
46
43
45
45
46
45
61
283
46
15
46
15
46
45
15
45
278
278
45
281
45
15
46
14
257
62
62
14
206
84
84
206
206
84
206
14
84
84
84
84
63
16
174
169
168
167
168
167
167
168
167
168
166
167
168
167
166
165
165
142
143
144
142
144
144
143
142
143
102
110
144
110
143
102
110
35
110
144
144
142
144
142
143
159
159
159
342
162
179
159
110
110
142
143
180
102
142
35
143
162
343
146
115
142
146
159
143
143
146
104
342
115
102
142
142
115
110
342
343
110
110
343
146
144
146
143
146
144
142
146
133
159
342
186
133
110
143
143
144
342
142
99
133
143
331
133
105
130
144
342
115
173
133
143
144
144
311
133
173
340
311
311
99
311
146
145
311
105
116
186
340
99
311
133
130
311
331
99
331
133
133
129
173
186
133
133
173
98
311
311
117
331
99
311
109
118
145
133
133
311
126
238
238
99
101
130
109
98
98
118
118
312
145
99
117
238
125
109
339
125
149
109
129
98
181
152
151
152
109
98
152
97
129
99
98
132
132
99
145
149
339
99
152
339
125
109
125
97
125
101
101
129
101
152
132
109
101
151
337
149
149
151
109
101
125
132
109
132
129
337
151
149
109
129
145
101
97
132
125
152
337
337
151
337
129
109
125
101
97
293
132
181
337
337
293
129
132
293
337
337
107
129
293
124
124
103
107
107
128
148
182
150
107
337
103
128
124
293
103
107
107
150
182
108
150
108
107
103
293
107
107
182
128
107
108
150
107
124
124
107
106
103
103
293
112
128
150
103
148
108
128
107
108
139
107
107
107
112
107
150
150
128
128
107
124
128
306
124
182
148
107
150
107
306
103
108
306
150
338
150
306
128
124
124
131
333
131
124
119
131
330
127
119
127
333
123
127
127
127
333
119
123
123
123
119
270
333
347
81
267
270
23
23
23
38
38
333
23
23
20
302
302
70
23
332
34
58
19
332
70
332
24
51
58
70
58
56
58
77
69
51
74
34
34
73
69
73
51
33
77
33
58
58
33
39
68
58
33
39
39
40
40
59
49
49
49
60
305
76
60
304
275
305
363
60
305
275
305
275
61
275
274
41
274
274
274
41
274
313
274
313
42
86
41
274
354
354
272
272
354
274
42
42
272
354
354
272
272
42
269
269
206
85
218
218
294
206
206
316
316
332
260
121
316
285
78
78
114
362
260
114
78
285
326
327
308
326
162
326
336
329
239
239
239
240
240
240
303
303
288
177
177
90
326
162
162
10
162
162
108
336
147
329
329
90
356
356
197
354
354
98
152
136
204
293
300
112
148
290
112
112
336
336
329
329
330
329
329
189
81
329
329
347
347
347
189
347
189
303
189
303
346
346
301
346
34
290
290
287
287
291
291
291
286
201
143
102
82
92
90
89
89
89
284
206
206
206
162
162
64
14
10
65
10
375
169
169
168
236
167
167
167
236
164
164
177
177
274
52
52
53
63
63
53
64
333
140
122
207
295
295
88
295
295
15
282
281
46
281
282
282
281
281
281
45
46
94
280
280
279
280
279
44
44
94
94
62
206
62
131
62
62
366
317
10
340
126
238
129
338
106
124
103
103
103
103
124
103
131
128
119
289
138
23
39
6
48
15
304
168
168
166
167
144
142
142
102
110
144
110
115
133
142
311
340
116
145
145
145
118
99
125
129
145
145
98
151
145
337
145
101
181
181
148
148
107
182
306
306
148
119
270
123
123
270
270
123
270
270
138
16
22
222
371
371
13
39
249
42
14
341
63
359
74
120
19
69
73
276
282
282
282
282
282
15
44
44
64
67
267
120
120
120
120
106
103
103
106
106
106
112
112
293
293
293
109
145
145
109
109
99
145
142
145
145
132
145
99
99
99
99
98
98
142
98
311
312
36
35
142
142
101
35
35
35
159
155
153
168
169
168
167
164
177
177
126
126
105
126
126
101
101
101
101
26
26
148
64
125
65
64
65
75
52
207
207
295
207
183
3
270
270
183
182
182
115
115
103
125
125
101
101
125
97
125
124
128
306
131
131
128
128
132
132
129
132
129
132
133
133
133
144
143
97
106
106
103
103
103
128
127
127
124
124
129
129
129
125
132
109
109
130
130
130
130
133
84
84
206
206
326
168
(1049 rows)
3.4.3. Exercise 14: Finding the served roads using pgr_drivingDistance¶
Problem
Find the roads within 10 minutes walking distance from the hospitals. Use 1
m/s
as walking speed.
Solution
In this exercise, the roads served are calculated based on a walking time of 1
m/s
, by using pgrdrivingDistance
function from pgRouting extension.
Time in minutes is considered as
cost
.the graph is undirected.
Preparing a query
PREPARE edges AS
SELECT gid as id,source,target, length_m/60 AS cost,length_m/60 AS reverse_cost
FROM roads.roads_ways;
\o exercise_15.txt
SELECT gid, source, target, agg_cost AS minutes, the_geom
FROM pgr_drivingDistance(
'edges', -- the prepared statement
(
SELECT closest_vertex(poly_geom)
FROM buildings.buildings_ways
WHERE tag_id = '318'
), -- the starting vertex
10, -- 10 minutes
false -- graph is undirected
) AS results
JOIN roads.roads_ways AS r ON (edge = gid);
PREPARE
For the following query,
The prepared statement is used.
Pedestrian speed is set to be
1 m/s
.As
time
=distance/speed
,length_m
/1 m/s
/60
gives the time in minutes.
tag_id = '318'
as 318 is the value for hospital in the configuration table of the buildings.10
for 10 minutes, which is a threshold foragg_cost
SELECT gid, source, target, agg_cost AS minutes, the_geom
FROM pgr_drivingDistance(
'edges', -- the prepared statement
(
SELECT closest_vertex(poly_geom)
FROM buildings.buildings_ways
WHERE tag_id = '318'
), -- the starting vertex
10, -- 10 minutes
false -- graph is undirected
) AS results
JOIN roads.roads_ways AS r ON (edge = gid);
gid | source | target | minutes | the_geom
-----+--------+--------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
60 | 158 | 36 | 1.4493409579441923 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
473 | 158 | 312 | 2.235454572765966 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
238 | 99 | 145 | 2.427530387055349 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
471 | 312 | 311 | 2.791877105220287 | 0102000020E6100000020000008D83A6808F355240FDAF29464C1B33400A01AF858F3552400D8D2782381B3340
20 | 36 | 13 | 3.904719927544691 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
181 | 145 | 109 | 3.970347591636453 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
260 | 157 | 158 | 4.022391725856522 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
237 | 293 | 145 | 4.742001982159778 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
214 | 109 | 132 | 5.419338196723229 | 0102000020E610000002000000A7EA1ED95C3552408E07B6A5691B3340EF6BC94F4F3552402351C3126A1B3340
342 | 157 | 213 | 5.745772843183961 | 0102000020E610000002000000F716201B923552402B6A300DC31B33406CE22E0CA235524018320D79BA1B3340
258 | 373 | 157 | 5.847982266772537 | 0102000020E61000000200000008F2A265933552404BA544B7031C3340F716201B923552402B6A300DC31B3340
550 | 371 | 373 | 5.883682975657497 | 0102000020E610000002000000505CE9109335524063B9008F031C334008F2A265933552404BA544B7031C3340
541 | 213 | 364 | 5.920892571153665 | 0102000020E6100000020000006CE22E0CA235524018320D79BA1B33409472ADACA3355240D18547D1B91B3340
232 | 35 | 142 | 5.9654226216040325 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A3340C9C9C4AD823552402DEC6987BF1A3340
233 | 142 | 143 | 6.032675642646609 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A33407C8560B0763552405D00755EBE1A3340
63 | 13 | 37 | 6.1003822505499805 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B334074942820923552407ACAC573111C3340
58 | 159 | 35 | 6.144599533793317 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
62 | 93 | 37 | 6.304422409275661 | 0102000020E6100000020000003D0565BF933552400D648742151C334074942820923552407ACAC573111C3340
444 | 112 | 293 | 6.308663595709409 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B33405E21BDF26A3552405F3AF768BB1B3340
209 | 132 | 129 | 6.548259057855224 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
552 | 373 | 374 | 6.84446984590517 | 0102000020E61000000200000008F2A265933552404BA544B7031C334062D68BA19C35524020E6DC37081C3340
352 | 37 | 223 | 7.045889619296911 | 0102000020E61000000200000074942820923552407ACAC573111C33409162804493355240B62BF4C1321C3340
154 | 227 | 93 | 7.135132752331723 | 0102000020E6100000020000000DAC3EB2943552408146448C321C33403D0565BF933552400D648742151C3340
235 | 145 | 144 | 7.238631814440762 | 0102000020E6100000020000006499D9426B3552407D40A033691B33402EF944436B3552409389004CBE1A3340
549 | 374 | 372 | 7.303451912672058 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C3340073763E2A035524090DD054A0A1C3340
155 | 111 | 93 | 7.447294838371644 | 0102000020E6100000020000004489963C9E3552408C9A54D91C1C33403D0565BF933552400D648742151C3340
353 | 223 | 224 | 7.564428934555067 | 0102000020E6100000020000009162804493355240B62BF4C1321C334067B224E593355240608EC305451C3340
356 | 228 | 227 | 7.675480664240274 | 0102000020E6100000020000005A5DF34F95355240B3446799451C33400DAC3EB2943552408146448C321C3340
203 | 129 | 125 | 7.696121163974513 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B334024C9BD0A3A355240947C36BC6A1B3340
216 | 132 | 133 | 7.8661007266761604 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33401FC253234F35524061B0D128131B3340
281 | 142 | 169 | 7.9353372537217 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A334099A14CFE82355240D8E77B574E1A3340
463 | 112 | 306 | 8.086671350735413 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B334036D6B4415C3552407A820E04121C3340
64 | 224 | 38 | 8.137454092472144 | 0102000020E61000000200000067B224E593355240608EC305451C3340139A249694355240BB7B2535591C3340
357 | 82 | 228 | 8.306556003352037 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
256 | 169 | 156 | 8.58959315335783 | 0102000020E61000000200000099A14CFE82355240D8E77B574E1A3340BA79F4D0883552405F6EE646471A3340
184 | 144 | 110 | 8.762659091477754 | 0102000020E6100000020000002EF944436B3552409389004CBE1A33400C546B065D3552404B958334BE1A3340
59 | 155 | 35 | 8.764606484713976 | 0102000020E610000002000000E13F82878E355240E807BF1E5D1A334040BE840A8E3552403E6BC889C01A3340
215 | 107 | 132 | 8.789683360449464 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B3340EF6BC94F4F3552402351C3126A1B3340
1 | 374 | 375 | 8.79495518042191 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C33409F81470A9B355240C0571932C31B3340
129 | 306 | 79 | 8.821989181019118 | 0102000020E61000000200000036D6B4415C3552407A820E04121C3340066685225D355240EF1417EC2B1C3340
279 | 143 | 168 | 8.843362432173562 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A3340ABD84E4A773552401950148D5A1A3340
252 | 159 | 153 | 8.969520574761862 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A33403982AF4390355240D25B97755A1A3340
211 | 129 | 130 | 8.985428444539977 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B33406C25749744355240FC5AF2D3131B3340
167 | 125 | 101 | 9.125993464704955 | 0102000020E61000000200000024C9BD0A3A355240947C36BC6A1B33409C1320AF2C355240D5C7E8256B1B3340
39 | 38 | 24 | 9.24489420855162 | 0102000020E610000002000000139A249694355240BB7B2535591C3340AE896077953552404D6C9967801C3340
282 | 156 | 170 | 9.24563628712511 | 0102000020E610000002000000BA79F4D0883552405F6EE646471A3340346CEFAE8E35524009CB338A401A3340
130 | 79 | 80 | 9.30869841715778 | 0102000020E610000002000000066685225D355240EF1417EC2B1C334070E591E45E355240F99FFCDD3B1C3340
133 | 92 | 82 | 9.405641379586184 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340420180089635524054AA44D95B1C3340
283 | 11 | 170 | 9.411980337025426 | 0102000020E61000000200000064C279828E3552402FB9F6AA3A1A3340346CEFAE8E35524009CB338A401A3340
262 | 170 | 160 | 9.451766455246934 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A33409EEBFB7090355240CF48298D3D1A3340
397 | 82 | 260 | 9.601885897573753 | 0102000020E610000006000000420180089635524054AA44D95B1C334059E1F148973552408E88731E5F1C3340B7F75F9D99355240ABC9F89C601C334027005A4E9D355240742090F0621C33401F20A9CF9F35524056777643641C3340CB8E43B3A1355240C00D8C17661C3340
534 | 160 | 359 | 9.602713754619254 | 0102000020E6100000030000009EEBFB7090355240CF48298D3D1A3340F8D73C5890355240E868554B3A1A33409EBDE94C903552404EF27E37381A3340
213 | 306 | 131 | 9.680181862851645 | 0102000020E61000000200000036D6B4415C3552407A820E04121C334037ED180D4F355240A277CF262C1C3340
277 | 144 | 167 | 9.741611163118138 | 0102000020E6100000020000002EF944436B3552409389004CBE1A334016139B8F6B35524055E70764651A3340
210 | 128 | 129 | 9.892410474579446 | 0102000020E6100000020000006CB2463D4435524075E1AC2DE11B33403CCFE9C3443552404727A6666A1B3340
(58 rows)
Following figure shows the visualised output of the above query. The lines
highlighted by red colour show the area from where the hospital can be reached
within 10 minutes of walking at the speed of 1 m/s
.
It is noticable from the output figure that some of the roads which are near to the hospital are not highlighted. For example, to roads in the north of the hospital. This is because the only one edge per road vertex was selected by the query.
3.4.4. Exercise 15: Generalising the served roads¶
The edges which are near to to hospital should also be selected in the roads served
as the hospital also serves those buildings. The following query takes the query
from previous section as a subquery
and selects all the edges from roads_ways
that have the same source
and target
to that of subquery
(Line 14).
WITH
subquery AS (
SELECT edge, source, target, agg_cost AS minutes, the_geom
FROM pgr_drivingDistance(
'edges',
(
SELECT closest_vertex(poly_geom)
FROM buildings.buildings_ways
WHERE tag_id = '318'
), 10, FALSE
) AS results
JOIN roads.roads_ways AS r ON (edge = gid)
),
connected_edges AS (
SELECT r.gid, r.source, r.target, length_m/60, r.the_geom
FROM subquery AS s JOIN roads.roads_ways AS r
ON ((s.source = r.source OR s.source = r.target))
)
SELECT * FROM subquery
UNION ALL
SELECT * FROM connected_edges;
edge | source | target | minutes | the_geom
------+--------+--------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
60 | 158 | 36 | 1.4493409579441923 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
473 | 158 | 312 | 2.235454572765966 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
238 | 99 | 145 | 2.427530387055349 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
471 | 312 | 311 | 2.791877105220287 | 0102000020E6100000020000008D83A6808F355240FDAF29464C1B33400A01AF858F3552400D8D2782381B3340
20 | 36 | 13 | 3.904719927544691 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
181 | 145 | 109 | 3.970347591636453 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
260 | 157 | 158 | 4.022391725856522 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
237 | 293 | 145 | 4.742001982159778 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
214 | 109 | 132 | 5.419338196723229 | 0102000020E610000002000000A7EA1ED95C3552408E07B6A5691B3340EF6BC94F4F3552402351C3126A1B3340
342 | 157 | 213 | 5.745772843183961 | 0102000020E610000002000000F716201B923552402B6A300DC31B33406CE22E0CA235524018320D79BA1B3340
258 | 373 | 157 | 5.847982266772537 | 0102000020E61000000200000008F2A265933552404BA544B7031C3340F716201B923552402B6A300DC31B3340
550 | 371 | 373 | 5.883682975657497 | 0102000020E610000002000000505CE9109335524063B9008F031C334008F2A265933552404BA544B7031C3340
541 | 213 | 364 | 5.920892571153665 | 0102000020E6100000020000006CE22E0CA235524018320D79BA1B33409472ADACA3355240D18547D1B91B3340
232 | 35 | 142 | 5.9654226216040325 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A3340C9C9C4AD823552402DEC6987BF1A3340
233 | 142 | 143 | 6.032675642646609 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A33407C8560B0763552405D00755EBE1A3340
63 | 13 | 37 | 6.1003822505499805 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B334074942820923552407ACAC573111C3340
58 | 159 | 35 | 6.144599533793317 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
62 | 93 | 37 | 6.304422409275661 | 0102000020E6100000020000003D0565BF933552400D648742151C334074942820923552407ACAC573111C3340
444 | 112 | 293 | 6.308663595709409 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B33405E21BDF26A3552405F3AF768BB1B3340
209 | 132 | 129 | 6.548259057855224 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
552 | 373 | 374 | 6.84446984590517 | 0102000020E61000000200000008F2A265933552404BA544B7031C334062D68BA19C35524020E6DC37081C3340
352 | 37 | 223 | 7.045889619296911 | 0102000020E61000000200000074942820923552407ACAC573111C33409162804493355240B62BF4C1321C3340
154 | 227 | 93 | 7.135132752331723 | 0102000020E6100000020000000DAC3EB2943552408146448C321C33403D0565BF933552400D648742151C3340
235 | 145 | 144 | 7.238631814440762 | 0102000020E6100000020000006499D9426B3552407D40A033691B33402EF944436B3552409389004CBE1A3340
549 | 374 | 372 | 7.303451912672058 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C3340073763E2A035524090DD054A0A1C3340
155 | 111 | 93 | 7.447294838371644 | 0102000020E6100000020000004489963C9E3552408C9A54D91C1C33403D0565BF933552400D648742151C3340
353 | 223 | 224 | 7.564428934555067 | 0102000020E6100000020000009162804493355240B62BF4C1321C334067B224E593355240608EC305451C3340
356 | 228 | 227 | 7.675480664240274 | 0102000020E6100000020000005A5DF34F95355240B3446799451C33400DAC3EB2943552408146448C321C3340
203 | 129 | 125 | 7.696121163974513 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B334024C9BD0A3A355240947C36BC6A1B3340
216 | 132 | 133 | 7.8661007266761604 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33401FC253234F35524061B0D128131B3340
281 | 142 | 169 | 7.9353372537217 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A334099A14CFE82355240D8E77B574E1A3340
463 | 112 | 306 | 8.086671350735413 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B334036D6B4415C3552407A820E04121C3340
64 | 224 | 38 | 8.137454092472144 | 0102000020E61000000200000067B224E593355240608EC305451C3340139A249694355240BB7B2535591C3340
357 | 82 | 228 | 8.306556003352037 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
256 | 169 | 156 | 8.58959315335783 | 0102000020E61000000200000099A14CFE82355240D8E77B574E1A3340BA79F4D0883552405F6EE646471A3340
184 | 144 | 110 | 8.762659091477754 | 0102000020E6100000020000002EF944436B3552409389004CBE1A33400C546B065D3552404B958334BE1A3340
59 | 155 | 35 | 8.764606484713976 | 0102000020E610000002000000E13F82878E355240E807BF1E5D1A334040BE840A8E3552403E6BC889C01A3340
215 | 107 | 132 | 8.789683360449464 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B3340EF6BC94F4F3552402351C3126A1B3340
1 | 374 | 375 | 8.79495518042191 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C33409F81470A9B355240C0571932C31B3340
129 | 306 | 79 | 8.821989181019118 | 0102000020E61000000200000036D6B4415C3552407A820E04121C3340066685225D355240EF1417EC2B1C3340
279 | 143 | 168 | 8.843362432173562 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A3340ABD84E4A773552401950148D5A1A3340
252 | 159 | 153 | 8.969520574761862 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A33403982AF4390355240D25B97755A1A3340
211 | 129 | 130 | 8.985428444539977 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B33406C25749744355240FC5AF2D3131B3340
167 | 125 | 101 | 9.125993464704955 | 0102000020E61000000200000024C9BD0A3A355240947C36BC6A1B33409C1320AF2C355240D5C7E8256B1B3340
39 | 38 | 24 | 9.24489420855162 | 0102000020E610000002000000139A249694355240BB7B2535591C3340AE896077953552404D6C9967801C3340
282 | 156 | 170 | 9.24563628712511 | 0102000020E610000002000000BA79F4D0883552405F6EE646471A3340346CEFAE8E35524009CB338A401A3340
130 | 79 | 80 | 9.30869841715778 | 0102000020E610000002000000066685225D355240EF1417EC2B1C334070E591E45E355240F99FFCDD3B1C3340
133 | 92 | 82 | 9.405641379586184 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340420180089635524054AA44D95B1C3340
283 | 11 | 170 | 9.411980337025426 | 0102000020E61000000200000064C279828E3552402FB9F6AA3A1A3340346CEFAE8E35524009CB338A401A3340
262 | 170 | 160 | 9.451766455246934 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A33409EEBFB7090355240CF48298D3D1A3340
397 | 82 | 260 | 9.601885897573753 | 0102000020E610000006000000420180089635524054AA44D95B1C334059E1F148973552408E88731E5F1C3340B7F75F9D99355240ABC9F89C601C334027005A4E9D355240742090F0621C33401F20A9CF9F35524056777643641C3340CB8E43B3A1355240C00D8C17661C3340
534 | 160 | 359 | 9.602713754619254 | 0102000020E6100000030000009EEBFB7090355240CF48298D3D1A3340F8D73C5890355240E868554B3A1A33409EBDE94C903552404EF27E37381A3340
213 | 306 | 131 | 9.680181862851645 | 0102000020E61000000200000036D6B4415C3552407A820E04121C334037ED180D4F355240A277CF262C1C3340
277 | 144 | 167 | 9.741611163118138 | 0102000020E6100000020000002EF944436B3552409389004CBE1A334016139B8F6B35524055E70764651A3340
210 | 128 | 129 | 9.892410474579446 | 0102000020E6100000020000006CB2463D4435524075E1AC2DE11B33403CCFE9C3443552404727A6666A1B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
61 | 35 | 36 | 4.7311518302823 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A33404636A15A8E355240838D4699681B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
20 | 36 | 13 | 2.5756870174568083 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
60 | 158 | 36 | 0.1203080478563094 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
260 | 157 | 158 | 2.5730507679123296 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
60 | 158 | 36 | 0.1203080478563094 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
473 | 158 | 312 | 0.786113614821774 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
260 | 157 | 158 | 2.5730507679123296 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
60 | 158 | 36 | 0.1203080478563094 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
473 | 158 | 312 | 0.786113614821774 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
238 | 99 | 145 | 1.1480376358182636 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
234 | 99 | 143 | 4.800252749039815 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33407C8560B0763552405D00755EBE1A3340
473 | 158 | 312 | 0.786113614821774 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
471 | 312 | 311 | 0.5564225324543212 | 0102000020E6100000020000008D83A6808F355240FDAF29464C1B33400A01AF858F3552400D8D2782381B3340
472 | 312 | 311 | 2.7411680621005954 | 0102000020E6100000060000008D83A6808F355240FDAF29464C1B33400A80F10C9A3552401A25F95B4C1B334022ABB6F699355240407AD4A93C1B334022ABB6F69935524076EFF2BE3B1B33408E1C8E5399355240AD985B6B391B33400A01AF858F3552400D8D2782381B3340
61 | 35 | 36 | 4.7311518302823 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A33404636A15A8E355240838D4699681B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
20 | 36 | 13 | 2.5756870174568083 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
60 | 158 | 36 | 0.1203080478563094 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
238 | 99 | 145 | 1.1480376358182636 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
235 | 145 | 144 | 4.811101427385413 | 0102000020E6100000020000006499D9426B3552407D40A033691B33402EF944436B3552409389004CBE1A3340
181 | 145 | 109 | 1.5428172045811037 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
237 | 293 | 145 | 2.3144715951044286 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
259 | 13 | 157 | 0.13910148366610456 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B3340F716201B923552402B6A300DC31B3340
342 | 157 | 213 | 1.723381117327439 | 0102000020E610000002000000F716201B923552402B6A300DC31B33406CE22E0CA235524018320D79BA1B3340
260 | 157 | 158 | 2.5730507679123296 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
258 | 373 | 157 | 1.8255905409160154 | 0102000020E61000000200000008F2A265933552404BA544B7031C3340F716201B923552402B6A300DC31B3340
444 | 112 | 293 | 1.5666616135496316 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B33405E21BDF26A3552405F3AF768BB1B3340
237 | 293 | 145 | 2.3144715951044286 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
19 | 293 | 13 | 4.065869900604024 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
464 | 109 | 306 | 4.740167131311484 | 0102000020E610000003000000A7EA1ED95C3552408E07B6A5691B33403649E29B5C3552409558631A971B334036D6B4415C3552407A820E04121C3340
214 | 109 | 132 | 1.4489906050867754 | 0102000020E610000002000000A7EA1ED95C3552408E07B6A5691B3340EF6BC94F4F3552402351C3126A1B3340
182 | 110 | 109 | 4.826266681040104 | 0102000020E6100000020000000C546B065D3552404B958334BE1A3340A7EA1ED95C3552408E07B6A5691B3340
181 | 145 | 109 | 1.5428172045811037 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
259 | 13 | 157 | 0.13910148366610456 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B3340F716201B923552402B6A300DC31B3340
342 | 157 | 213 | 1.723381117327439 | 0102000020E610000002000000F716201B923552402B6A300DC31B33406CE22E0CA235524018320D79BA1B3340
260 | 157 | 158 | 2.5730507679123296 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
258 | 373 | 157 | 1.8255905409160154 | 0102000020E61000000200000008F2A265933552404BA544B7031C3340F716201B923552402B6A300DC31B3340
551 | 93 | 373 | 0.49530085030973936 | 0102000020E6100000020000003D0565BF933552400D648742151C334008F2A265933552404BA544B7031C3340
550 | 371 | 373 | 0.035700708884960096 | 0102000020E610000002000000505CE9109335524063B9008F031C334008F2A265933552404BA544B7031C3340
258 | 373 | 157 | 1.8255905409160154 | 0102000020E61000000200000008F2A265933552404BA544B7031C3340F716201B923552402B6A300DC31B3340
552 | 373 | 374 | 0.9964875791326322 | 0102000020E61000000200000008F2A265933552404BA544B7031C334062D68BA19C35524020E6DC37081C3340
550 | 371 | 373 | 0.035700708884960096 | 0102000020E610000002000000505CE9109335524063B9008F031C334008F2A265933552404BA544B7031C3340
342 | 157 | 213 | 1.723381117327439 | 0102000020E610000002000000F716201B923552402B6A300DC31B33406CE22E0CA235524018320D79BA1B3340
541 | 213 | 364 | 0.1751197279697037 | 0102000020E6100000020000006CE22E0CA235524018320D79BA1B33409472ADACA3355240D18547D1B91B3340
340 | 213 | 212 | 11.632818723864188 | 0102000020E6100000060000006CE22E0CA235524018320D79BA1B3340F89B9A159B355240853474FDCC1A33408DFF4CCE9A35524034B511F4BC1A33404A35FD239D3552402301593E811A3340CC69053C9F3552401CA732D6481A33408A181B5FA035524090EFF730201A3340
61 | 35 | 36 | 4.7311518302823 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A33404636A15A8E355240838D4699681B3340
232 | 35 | 142 | 1.2165702630128858 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A3340C9C9C4AD823552402DEC6987BF1A3340
59 | 155 | 35 | 2.7991838631099433 | 0102000020E610000002000000E13F82878E355240E807BF1E5D1A334040BE840A8E3552403E6BC889C01A3340
58 | 159 | 35 | 0.17917691218928386 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
232 | 35 | 142 | 1.2165702630128858 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A3340C9C9C4AD823552402DEC6987BF1A3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
281 | 142 | 169 | 3.1864848951305538 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A334099A14CFE82355240D8E77B574E1A3340
233 | 142 | 143 | 1.2838232840554622 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A33407C8560B0763552405D00755EBE1A3340
63 | 13 | 37 | 2.19566232300529 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B334074942820923552407ACAC573111C3340
259 | 13 | 157 | 0.13910148366610456 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B3340F716201B923552402B6A300DC31B3340
20 | 36 | 13 | 2.5756870174568083 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
19 | 293 | 13 | 4.065869900604024 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
58 | 159 | 35 | 0.17917691218928386 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
252 | 159 | 153 | 2.8249210409685466 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A33403982AF4390355240D25B97755A1A3340
261 | 311 | 159 | 3.4267239172165445 | 0102000020E6100000020000000A01AF858F3552400D8D2782381B3340B73066A68F3552409E4B27C8BE1A3340
551 | 93 | 373 | 0.49530085030973936 | 0102000020E6100000020000003D0565BF933552400D648742151C334008F2A265933552404BA544B7031C3340
62 | 93 | 37 | 0.2040401587256805 | 0102000020E6100000020000003D0565BF933552400D648742151C334074942820923552407ACAC573111C3340
155 | 111 | 93 | 1.142872429095983 | 0102000020E6100000020000004489963C9E3552408C9A54D91C1C33403D0565BF933552400D648742151C3340
154 | 227 | 93 | 0.8307103430560616 | 0102000020E6100000020000000DAC3EB2943552408146448C321C33403D0565BF933552400D648742151C3340
185 | 37 | 112 | 4.302234144936451 | 0102000020E61000000200000074942820923552407ACAC573111C3340947C36BC6A35524084F57F0EF31B3340
444 | 112 | 293 | 1.5666616135496316 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B33405E21BDF26A3552405F3AF768BB1B3340
463 | 112 | 306 | 1.7780077550260047 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B334036D6B4415C3552407A820E04121C3340
215 | 107 | 132 | 3.370345163726236 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B3340EF6BC94F4F3552402351C3126A1B3340
214 | 109 | 132 | 1.4489906050867754 | 0102000020E610000002000000A7EA1ED95C3552408E07B6A5691B3340EF6BC94F4F3552402351C3126A1B3340
209 | 132 | 129 | 1.1289208611319963 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
216 | 132 | 133 | 2.446762529952932 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33401FC253234F35524061B0D128131B3340
551 | 93 | 373 | 0.49530085030973936 | 0102000020E6100000020000003D0565BF933552400D648742151C334008F2A265933552404BA544B7031C3340
550 | 371 | 373 | 0.035700708884960096 | 0102000020E610000002000000505CE9109335524063B9008F031C334008F2A265933552404BA544B7031C3340
258 | 373 | 157 | 1.8255905409160154 | 0102000020E61000000200000008F2A265933552404BA544B7031C3340F716201B923552402B6A300DC31B3340
552 | 373 | 374 | 0.9964875791326322 | 0102000020E61000000200000008F2A265933552404BA544B7031C334062D68BA19C35524020E6DC37081C3340
63 | 13 | 37 | 2.19566232300529 | 0102000020E610000002000000E63B9DD0903552402B22799DC31B334074942820923552407ACAC573111C3340
352 | 37 | 223 | 0.9455073687469304 | 0102000020E61000000200000074942820923552407ACAC573111C33409162804493355240B62BF4C1321C3340
185 | 37 | 112 | 4.302234144936451 | 0102000020E61000000200000074942820923552407ACAC573111C3340947C36BC6A35524084F57F0EF31B3340
62 | 93 | 37 | 0.2040401587256805 | 0102000020E6100000020000003D0565BF933552400D648742151C334074942820923552407ACAC573111C3340
154 | 227 | 93 | 0.8307103430560616 | 0102000020E6100000020000000DAC3EB2943552408146448C321C33403D0565BF933552400D648742151C3340
356 | 228 | 227 | 0.5403479119085514 | 0102000020E6100000020000005A5DF34F95355240B3446799451C33400DAC3EB2943552408146448C321C3340
238 | 99 | 145 | 1.1480376358182636 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
235 | 145 | 144 | 4.811101427385413 | 0102000020E6100000020000006499D9426B3552407D40A033691B33402EF944436B3552409389004CBE1A3340
181 | 145 | 109 | 1.5428172045811037 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
237 | 293 | 145 | 2.3144715951044286 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
552 | 373 | 374 | 0.9964875791326322 | 0102000020E61000000200000008F2A265933552404BA544B7031C334062D68BA19C35524020E6DC37081C3340
549 | 374 | 372 | 0.45898206676688796 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C3340073763E2A035524090DD054A0A1C3340
1 | 374 | 375 | 1.9504853345167399 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C33409F81470A9B355240C0571932C31B3340
155 | 111 | 93 | 1.142872429095983 | 0102000020E6100000020000004489963C9E3552408C9A54D91C1C33403D0565BF933552400D648742151C3340
352 | 37 | 223 | 0.9455073687469304 | 0102000020E61000000200000074942820923552407ACAC573111C33409162804493355240B62BF4C1321C3340
353 | 223 | 224 | 0.518539315258155 | 0102000020E6100000020000009162804493355240B62BF4C1321C334067B224E593355240608EC305451C3340
357 | 82 | 228 | 0.6310753391117633 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
356 | 228 | 227 | 0.5403479119085514 | 0102000020E6100000020000005A5DF34F95355240B3446799451C33400DAC3EB2943552408146448C321C3340
210 | 128 | 129 | 3.3441514167242206 | 0102000020E6100000020000006CB2463D4435524075E1AC2DE11B33403CCFE9C3443552404727A6666A1B3340
211 | 129 | 130 | 2.4371693866847517 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B33406C25749744355240FC5AF2D3131B3340
203 | 129 | 125 | 1.1478621061192884 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B334024C9BD0A3A355240947C36BC6A1B3340
209 | 132 | 129 | 1.1289208611319963 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
215 | 107 | 132 | 3.370345163726236 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B3340EF6BC94F4F3552402351C3126A1B3340
214 | 109 | 132 | 1.4489906050867754 | 0102000020E610000002000000A7EA1ED95C3552408E07B6A5691B3340EF6BC94F4F3552402351C3126A1B3340
209 | 132 | 129 | 1.1289208611319963 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
216 | 132 | 133 | 2.446762529952932 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33401FC253234F35524061B0D128131B3340
232 | 35 | 142 | 1.2165702630128858 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A3340C9C9C4AD823552402DEC6987BF1A3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
281 | 142 | 169 | 3.1864848951305538 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A334099A14CFE82355240D8E77B574E1A3340
233 | 142 | 143 | 1.2838232840554622 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A33407C8560B0763552405D00755EBE1A3340
185 | 37 | 112 | 4.302234144936451 | 0102000020E61000000200000074942820923552407ACAC573111C3340947C36BC6A35524084F57F0EF31B3340
444 | 112 | 293 | 1.5666616135496316 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B33405E21BDF26A3552405F3AF768BB1B3340
463 | 112 | 306 | 1.7780077550260047 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B334036D6B4415C3552407A820E04121C3340
64 | 224 | 38 | 0.573025157917078 | 0102000020E61000000200000067B224E593355240608EC305451C3340139A249694355240BB7B2535591C3340
353 | 223 | 224 | 0.518539315258155 | 0102000020E6100000020000009162804493355240B62BF4C1321C334067B224E593355240608EC305451C3340
397 | 82 | 260 | 1.2953298942217164 | 0102000020E610000006000000420180089635524054AA44D95B1C334059E1F148973552408E88731E5F1C3340B7F75F9D99355240ABC9F89C601C334027005A4E9D355240742090F0621C33401F20A9CF9F35524056777643641C3340CB8E43B3A1355240C00D8C17661C3340
65 | 82 | 38 | 0.17177573693744302 | 0102000020E610000002000000420180089635524054AA44D95B1C3340139A249694355240BB7B2535591C3340
357 | 82 | 228 | 0.6310753391117633 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
133 | 92 | 82 | 1.0990853762341473 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340420180089635524054AA44D95B1C3340
281 | 142 | 169 | 3.1864848951305538 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A334099A14CFE82355240D8E77B574E1A3340
280 | 168 | 169 | 1.2990234855081648 | 0102000020E610000002000000ABD84E4A773552401950148D5A1A334099A14CFE82355240D8E77B574E1A3340
256 | 169 | 156 | 0.65425589963613 | 0102000020E61000000200000099A14CFE82355240D8E77B574E1A3340BA79F4D0883552405F6EE646471A3340
236 | 143 | 144 | 1.2230800365242556 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A33402EF944436B3552409389004CBE1A3340
184 | 144 | 110 | 1.5240272770369918 | 0102000020E6100000020000002EF944436B3552409389004CBE1A33400C546B065D3552404B958334BE1A3340
277 | 144 | 167 | 2.5029793486773766 | 0102000020E6100000020000002EF944436B3552409389004CBE1A334016139B8F6B35524055E70764651A3340
235 | 145 | 144 | 4.811101427385413 | 0102000020E6100000020000006499D9426B3552407D40A033691B33402EF944436B3552409389004CBE1A3340
257 | 155 | 156 | 0.9381669123114619 | 0102000020E610000005000000E13F82878E355240E807BF1E5D1A33404C3E2C8A8D3552401E2CADD0511A33407D3B2E4F8C3552401A530B804C1A334012BC218D8A355240937B702D491A3340BA79F4D0883552405F6EE646471A3340
59 | 155 | 35 | 2.7991838631099433 | 0102000020E610000002000000E13F82878E355240E807BF1E5D1A334040BE840A8E3552403E6BC889C01A3340
255 | 170 | 155 | 0.8047246919827096 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A3340E13F82878E355240E807BF1E5D1A3340
208 | 107 | 128 | 1.1289984189827782 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B33406CB2463D4435524075E1AC2DE11B3340
215 | 107 | 132 | 3.370345163726236 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B3340EF6BC94F4F3552402351C3126A1B3340
178 | 131 | 107 | 2.0937735887027458 | 0102000020E61000000200000037ED180D4F355240A277CF262C1C33401F4F26C94E3552406F9406C8E11B3340
552 | 373 | 374 | 0.9964875791326322 | 0102000020E61000000200000008F2A265933552404BA544B7031C334062D68BA19C35524020E6DC37081C3340
549 | 374 | 372 | 0.45898206676688796 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C3340073763E2A035524090DD054A0A1C3340
1 | 374 | 375 | 1.9504853345167399 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C33409F81470A9B355240C0571932C31B3340
464 | 109 | 306 | 4.740167131311484 | 0102000020E610000003000000A7EA1ED95C3552408E07B6A5691B33403649E29B5C3552409558631A971B334036D6B4415C3552407A820E04121C3340
463 | 112 | 306 | 1.7780077550260047 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B334036D6B4415C3552407A820E04121C3340
213 | 306 | 131 | 1.5935105121162316 | 0102000020E61000000200000036D6B4415C3552407A820E04121C334037ED180D4F355240A277CF262C1C3340
129 | 306 | 79 | 0.7353178302837047 | 0102000020E61000000200000036D6B4415C3552407A820E04121C3340066685225D355240EF1417EC2B1C3340
234 | 99 | 143 | 4.800252749039815 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33407C8560B0763552405D00755EBE1A3340
233 | 142 | 143 | 1.2838232840554622 | 0102000020E610000002000000C9C9C4AD823552402DEC6987BF1A33407C8560B0763552405D00755EBE1A3340
236 | 143 | 144 | 1.2230800365242556 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A33402EF944436B3552409389004CBE1A3340
279 | 143 | 168 | 2.810686789526953 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A3340ABD84E4A773552401950148D5A1A3340
58 | 159 | 35 | 0.17917691218928386 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
252 | 159 | 153 | 2.8249210409685466 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A33403982AF4390355240D25B97755A1A3340
261 | 311 | 159 | 3.4267239172165445 | 0102000020E6100000020000000A01AF858F3552400D8D2782381B3340B73066A68F3552409E4B27C8BE1A3340
210 | 128 | 129 | 3.3441514167242206 | 0102000020E6100000020000006CB2463D4435524075E1AC2DE11B33403CCFE9C3443552404727A6666A1B3340
211 | 129 | 130 | 2.4371693866847517 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B33406C25749744355240FC5AF2D3131B3340
203 | 129 | 125 | 1.1478621061192884 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B334024C9BD0A3A355240947C36BC6A1B3340
209 | 132 | 129 | 1.1289208611319963 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
204 | 124 | 125 | 3.317664655932036 | 0102000020E61000000200000083E8FF0B3A355240CF2CAE96E01B334024C9BD0A3A355240947C36BC6A1B3340
167 | 125 | 101 | 1.429872300730441 | 0102000020E61000000200000024C9BD0A3A355240947C36BC6A1B33409C1320AF2C355240D5C7E8256B1B3340
205 | 125 | 126 | 2.427949297893071 | 0102000020E61000000200000024C9BD0A3A355240947C36BC6A1B3340539275383A3552406D86657D141B3340
203 | 129 | 125 | 1.1478621061192884 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B334024C9BD0A3A355240947C36BC6A1B3340
64 | 224 | 38 | 0.573025157917078 | 0102000020E61000000200000067B224E593355240608EC305451C3340139A249694355240BB7B2535591C3340
131 | 38 | 81 | 3.219256987972808 | 0102000020E610000002000000139A249694355240BB7B2535591C3340DB00C7F9763552401EFB592C451C3340
39 | 38 | 24 | 1.1074401160794767 | 0102000020E610000002000000139A249694355240BB7B2535591C3340AE896077953552404D6C9967801C3340
65 | 82 | 38 | 0.17177573693744302 | 0102000020E610000002000000420180089635524054AA44D95B1C3340139A249694355240BB7B2535591C3340
257 | 155 | 156 | 0.9381669123114619 | 0102000020E610000005000000E13F82878E355240E807BF1E5D1A33404C3E2C8A8D3552401E2CADD0511A33407D3B2E4F8C3552401A530B804C1A334012BC218D8A355240937B702D491A3340BA79F4D0883552405F6EE646471A3340
282 | 156 | 170 | 0.6560431337672807 | 0102000020E610000002000000BA79F4D0883552405F6EE646471A3340346CEFAE8E35524009CB338A401A3340
256 | 169 | 156 | 0.65425589963613 | 0102000020E61000000200000099A14CFE82355240D8E77B574E1A3340BA79F4D0883552405F6EE646471A3340
130 | 79 | 80 | 0.4867092361386616 | 0102000020E610000002000000066685225D355240EF1417EC2B1C334070E591E45E355240F99FFCDD3B1C3340
129 | 306 | 79 | 0.7353178302837047 | 0102000020E61000000200000036D6B4415C3552407A820E04121C3340066685225D355240EF1417EC2B1C3340
153 | 24 | 92 | 0.17659307505962935 | 0102000020E610000002000000AE896077953552404D6C9967801C3340C5C5F6FF96355240C3C4D5B7821C3340
152 | 91 | 92 | 1.3139255848998028 | 0102000020E610000004000000F48EAE2D97355240EDFFD258B11C3340C438245A97355240BD72BD6DA61C334012047E4397355240F83F76CD9A1C3340C5C5F6FF96355240C3C4D5B7821C3340
480 | 92 | 316 | 0.960106691178696 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340DE8C3FF69F355240168FE623841C3340
133 | 92 | 82 | 1.0990853762341473 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340420180089635524054AA44D95B1C3340
366 | 11 | 236 | 5.513763409806261 | 0102000020E61000000200000064C279828E3552402FB9F6AA3A1A3340711C78B55C355240FE405EB46C1A3340
283 | 11 | 170 | 0.16634404990031487 | 0102000020E61000000200000064C279828E3552402FB9F6AA3A1A3340346CEFAE8E35524009CB338A401A3340
17 | 359 | 11 | 0.2037292601140293 | 0102000020E6100000020000009EBDE94C903552404EF27E37381A334064C279828E3552402FB9F6AA3A1A3340
283 | 11 | 170 | 0.16634404990031487 | 0102000020E61000000200000064C279828E3552402FB9F6AA3A1A3340346CEFAE8E35524009CB338A401A3340
282 | 156 | 170 | 0.6560431337672807 | 0102000020E610000002000000BA79F4D0883552405F6EE646471A3340346CEFAE8E35524009CB338A401A3340
255 | 170 | 155 | 0.8047246919827096 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A3340E13F82878E355240E807BF1E5D1A3340
262 | 170 | 160 | 0.2061301681218233 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A33409EEBFB7090355240CF48298D3D1A3340
397 | 82 | 260 | 1.2953298942217164 | 0102000020E610000006000000420180089635524054AA44D95B1C334059E1F148973552408E88731E5F1C3340B7F75F9D99355240ABC9F89C601C334027005A4E9D355240742090F0621C33401F20A9CF9F35524056777643641C3340CB8E43B3A1355240C00D8C17661C3340
65 | 82 | 38 | 0.17177573693744302 | 0102000020E610000002000000420180089635524054AA44D95B1C3340139A249694355240BB7B2535591C3340
357 | 82 | 228 | 0.6310753391117633 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
133 | 92 | 82 | 1.0990853762341473 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340420180089635524054AA44D95B1C3340
263 | 153 | 160 | 0.8140006470576503 | 0102000020E6100000020000003982AF4390355240D25B97755A1A33409EEBFB7090355240CF48298D3D1A3340
253 | 160 | 154 | 0.6184639371754016 | 0102000020E6100000020000009EEBFB7090355240CF48298D3D1A3340EF8FF7AA953552403375B22F341A3340
534 | 160 | 359 | 0.15094729937231907 | 0102000020E6100000030000009EEBFB7090355240CF48298D3D1A3340F8D73C5890355240E868554B3A1A33409EBDE94C903552404EF27E37381A3340
262 | 170 | 160 | 0.2061301681218233 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A33409EEBFB7090355240CF48298D3D1A3340
464 | 109 | 306 | 4.740167131311484 | 0102000020E610000003000000A7EA1ED95C3552408E07B6A5691B33403649E29B5C3552409558631A971B334036D6B4415C3552407A820E04121C3340
463 | 112 | 306 | 1.7780077550260047 | 0102000020E610000002000000947C36BC6A35524084F57F0EF31B334036D6B4415C3552407A820E04121C3340
213 | 306 | 131 | 1.5935105121162316 | 0102000020E61000000200000036D6B4415C3552407A820E04121C334037ED180D4F355240A277CF262C1C3340
129 | 306 | 79 | 0.7353178302837047 | 0102000020E61000000200000036D6B4415C3552407A820E04121C3340066685225D355240EF1417EC2B1C3340
236 | 143 | 144 | 1.2230800365242556 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A33402EF944436B3552409389004CBE1A3340
184 | 144 | 110 | 1.5240272770369918 | 0102000020E6100000020000002EF944436B3552409389004CBE1A33400C546B065D3552404B958334BE1A3340
277 | 144 | 167 | 2.5029793486773766 | 0102000020E6100000020000002EF944436B3552409389004CBE1A334016139B8F6B35524055E70764651A3340
235 | 145 | 144 | 4.811101427385413 | 0102000020E6100000020000006499D9426B3552407D40A033691B33402EF944436B3552409389004CBE1A3340
208 | 107 | 128 | 1.1289984189827782 | 0102000020E6100000020000001F4F26C94E3552406F9406C8E11B33406CB2463D4435524075E1AC2DE11B3340
207 | 127 | 128 | 2.752701179837276 | 0102000020E610000002000000D1915CFE43355240191648F5421C33406CB2463D4435524075E1AC2DE11B3340
210 | 128 | 129 | 3.3441514167242206 | 0102000020E6100000020000006CB2463D4435524075E1AC2DE11B33403CCFE9C3443552404727A6666A1B3340
201 | 128 | 124 | 1.091116836520284 | 0102000020E6100000020000006CB2463D4435524075E1AC2DE11B334083E8FF0B3A355240CF2CAE96E01B3340
(255 rows)
Following figure shows the visualised output of the above query. Lines
highlighted in yellow
show the generalised the roads served. This gives a better
estimate of the areas from where the hospital can be reached by a particular speed.
3.5. Calculating the total population served by the hospital¶
Now the next step is to estimate the dependant population. Official source of
population is Census conducted by the government. But for this exercise, population
will be estimated from the area
as well as the category
of the building.
This area will be stored in the nearest roads. Following steps explain this
process in detail.
3.5.1. Exercise 16: Finding the nearest roads of the buildings¶
To store the population of buildings in the roads, nearest road to a building is to be found. Follow the steps given below to complete this task.
Create Function for finding the closest edge.
1
2CREATE OR REPLACE FUNCTION closest_edge(geom GEOMETRY)
3RETURNS BIGINT AS
4$BODY$
5 SELECT gid FROM roads_ways ORDER BY geom <-> the_geom LIMIT 1;
6$BODY$
7LANGUAGE SQL;
8
CREATE FUNCTION
Add a column in
buildings_ways
for storing the id of closest edge
1ALTER TABLE buildings_ways
2ADD COLUMN edge_id INTEGER;
ALTER TABLE
Store the edge id of the closest edge in the new column of
buildings_ways
1UPDATE buildings_ways SET edge_id = closest_edge(poly_geom);
UPDATE 1049
3.5.2. Exercise 17: Storing the population in the roads¶
After finding the nearest road, the sum of population of all the nearest buildings is stored in the population column of the roads table. Following image shows the visualised output where the blue colour labels shows the population stored in roads.
Follow the steps given below to complete this task.
Add a column in
roads_ways
for storing population
ALTER TABLE roads_ways ADD COLUMN population INTEGER;
ALTER TABLE
Update the roads with the sum of population of buildings closest to it
1UPDATE roads_ways SET population = SUM
2FROM (
3 SELECT edge_id, SUM(population)
4 FROM buildings_ways GROUP BY edge_id
5 )
6AS subquery
7WHERE gid = edge_id;
UPDATE 265
Verify is the population is stored using the following query.
1SELECT population FROM roads_ways WHERE gid = 441;
population
------------
(1 row)
3.5.3. Exercise 18: Find total population served by the hospital¶
Final step is to find the total population served by the hospital based on travel time.
WITH
subquery AS (
SELECT source, target
FROM pgr_drivingDistance(
'edges',
(
SELECT closest_vertex(poly_geom)
FROM buildings.buildings_ways
WHERE tag_id = '318'
), 10, FALSE
)
AS results
JOIN roads.roads_ways AS r ON (edge = gid)
),
connected_edges AS (
SELECT DISTINCT gid, population
FROM subquery AS s JOIN roads.roads_ways AS r
ON (
(s.source = r.source OR s.source = r.target) OR
(s.target = r.source OR s.target = r.target)
)
)
SELECT SUM(population) FROM connected_edges;
sum
--------
109194
(1 row)