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.

Sustainable Development Goal 3: Good Health and Well Being

Image Source

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.

../_images/roads_and_buildings.png

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.

  1. Adding a column for storing the area

ALTER TABLE buildings_ways ADD COLUMN area INTEGER;
  1. 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 and hospital.

    • 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.

  1. 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.

  1. 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.

../_images/remove_disconnected_roads.png

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.

../_images/finding_closest_vertex.png

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 for agg_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.

../_images/service_area.png

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.

../_images/generalised_service_area.png

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.

  1. 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
  1. Add a column in buildings_ways for storing the id of closest edge

1ALTER TABLE buildings_ways
2ADD COLUMN edge_id INTEGER;
ALTER TABLE
  1. 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.

../_images/road_population.png

Follow the steps given below to complete this task.

  1. Add a column in roads_ways for storing population

ALTER TABLE roads_ways ADD COLUMN population INTEGER;
ALTER TABLE
  1. 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
  1. 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)