3. Buena salud y bienestar¶
Buena salud y bienestar es el tercer Objetivo de Desarrollo Sostenible que aspira garantizar la salud y el bienestar para todos, incluido un compromiso audaz para poner fin a epidemias como el SIDA, la tuberculosis, la malaria y otras enfermedades transmisibles para 2030. También pretende lograr la cobertura sanitaria universal y proporcionar acceso a medicamentos y vacunas seguros y eficaces para todos. Apoyar la investigación y el desarrollo de vacunas es una parte esencial de este proceso, así como ampliar el acceso a medicamentos asequibles. Los hospitales son una parte muy importante de una infraestructura sanitaria que funcione bien. Se requiere una planificación adecuada para la distribución óptima de la población de una zona a sus hospitales. Por lo tanto, es muy importante estimar el número de personas dependientes que viven cerca del hospital para una mejor planificación que, en última instancia, ayudaría a lograr la cobertura universal de los servicios de salud. Este capítulo se centrará en resolver uno de esos problemas.
“Fuente de la imagen <https://sdgs.un.org/goals/goal3>`__
3.1. Problema: Estimación de la población atendida por los hospitales¶
Declaración del problema
Determinar la población atendida por un hospital en función del tiempo de viaje
Idea central
La población que reside a lo largo de las carreteras que llegan a un hospital dentro de un tiempo determinado depende de ese hospital.
Enfoque
Para preparar un conjunto de datos con:
Segmentos: Carreteras
Polígonos: Edificios con población
Encuentre el tiempo de viaje basado en los caminos atendidos
Estimación de la población de edificios
Encontrar el camino más cercano a los edificios
Almacenar la suma de la población de los edificios más cercanos en la tabla de carreteras
Encontar la suma de la población en todas los segmentos de los caminos atendidos
3.2. Uso básico de PostreSQL¶
3.2.1. Preparación de la zona de trabajo¶
El search_path es una variable que determina el orden en el que se buscan objetos en los esquemas de la base de datos.
Configurando el search_path con los valores adecuados, se puede evitar anteponer el nombre del esquema a las tablas.
3.2.1.1. Ejercicio 1: Inspección de los esquemas¶
Inspeccione los esquemas mostrando todos los esquemas actuales mediante el siguiente comando
\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. Ejercicio 2: Inspeccionar la ruta de búsqueda¶
Mostrar la ruta de búsqueda actual mediante la siguiente consulta.
SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
Esta es la ruta de búsqueda actual. No se puede acceder a tablas de otros esquemas con esta ruta.
3.2.1.3. Ejercicio 3: Arreglar la ruta de búsqueda¶
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
Revisar de nuevo la ruta de búsqueda
SHOW search_path;
search_path
--------------------------------------------
roads, buildings, public, contrib, postgis
(1 row)
3.2.1.4. Ejercicio 4: Enumerar tablas¶
Con \dt se enumeran las tablas mostrando el esquema y el propietario
\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. Preparación de datos de calles y edificios¶
First step is to prepare the data obtained from Datos para los Objetivos de Desarrollo Sostenible.
En esta sección se trabajará el grafo y los datos que se va a utilizar para el procesamiento. Al construir el grafo, los datos se deben inspeccionar para determinar si hay datos no válidos. Este es un paso muy importante para asegurarse de que los datos sean de la calidad requerida. pgRouting también se puede utilizar para realizar algunos ajustes de datos.
3.3.1. Ejercicio 5: Contar el número de calles y edificios¶
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)
La siguiente imagen muestra las carreteras y edificios visualizados.
3.3.2. Preprocesamiento de edificios¶
La tabla buildings_ways` contiene los edificios en forma tipo LINGSTING. Tienen que ser convertidos a polígonos para obtener el área, ya que el área va a ser usada para estimar la población que vive en el edificio.
3.3.2.1. Exercise 6: Removing columns¶
Se pueden eliminar columnas de una tabla. En este caso, en lugar de crear una vista, las columnas que no están relacionadas con un concepto de edificio se eliminan de 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¶
Agregue una columna espacial denominada poly_geom a la tabla buildings_ways para almacenar la geometría del polígono
SELECT AddGeometryColumn('buildings','buildings_ways','poly_geom',4326,'POLYGON',2);
addgeometrycolumn
-------------------------------------------------------------------
buildings.buildings_ways.poly_geom SRID:4326 TYPE:POLYGON DIMS:2
(1 row)
Inspección de la tabla:
\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 se utiliza para encontrar el número de puntos en una geometría. Además, los polígonos con menos de 3 puntos/vértices no se consideran polígonos válidos en PostgreSQL. Por lo tanto, los edificios que tienen menos de 3 vértices deben eliminarse. Siguir los pasos que se indican a continuación para completar esta tarea.
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 se utiliza para hacer los polígonos. Este paso almacena la geometría de los polígonos en la columna poly_geom que se creó anteriormente.
1UPDATE buildings_ways
2SET poly_geom = ST_MakePolygon(the_geom);
UPDATE 1049
3.3.2.5. Exercise 10: Calculating the area¶
Después de obtener la geometría del polígono, el siguiente paso es encontrar el área de los polígonos. Siga los pasos que se indican a continuación para completar esta tarea.
Agregar una columna para almacenar el área
ALTER TABLE buildings_ways ADD COLUMN area INTEGER;
Almacenamiento del área en la nueva columna
ST_Area se utiliza para calcular el área de los polígonos. El área se almacena en la nueva columna
UPDATE buildings_ways
SET area = ST_Area(poly_geom::geography)::INTEGER;
UPDATE 1049
3.3.2.6. Exercise 11: Estimating the population¶
Debido a la falta de datos censales, este ejercicio completará y estimará la población que vive en los edificios, basándose en el área del edificio y el tipo de uso que se le da.
Los edificios de los datos de OpenStreetMap son clasificados en varias categorías.
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)
Para este ejercicio, la población se fijará de la siguiente manera:
Insignificante:
La gente no vive en estos lugares.
Población: 1 persona
Puede haber gente vigilando el lugar.
Muy escasa:
retail,commercial,schoolLa gente no vive en estos lugares.
Población: Al menos 2 personas.
Porque puede haber gente vigilando el lugar.
Escaso:
Edificios con baja densidad de población, como en
university.Población: Al menos 3 personas.
Porque puede haber gente vigilando el lugar.
Los estudiantes podrían vivir allí.
Moderado:
Lugares donde la gente puede estar viviendo temporalmente, como
hotelyhospital.Población: Al menos 5 personas.
Denso:
Un edificio residencial de tamaño mediano.
Población: Al menos 7 personas.
Muy denso:
Un edificio residencial de gran tamaño, como
apartments.Población: Al menos 10 personas.
Referencia: Apéndice
Este factor específico de la clase se multiplica con el área de cada edificio para obtener la población. Siga los pasos que se indican a continuación para completar esta tarea.
Cree una función para encontrar la población utilizando el factor y el área específicos de la clase.
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
Nota
Todas estas son estimaciones basadas en esta área en particular. Se pueden hacer funciones más complicadas que consideren la altura de los apartamentos pero el diseño de una función va a depender de la disponibilidad de los datos. Por ejemplo, el uso de datos del censo puede lograr una estimación más precisa.
Agregue una columna para almacenar la población en
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. Preprocesamiento de carreteras¶
Los algoritmos de pgRouting solo son útiles cuando la red de carreteras pertenece a un solo grafo (o todas las carreteras están conectadas entre sí). Por lo tanto, las carreteras desconectadas deben eliminarse de su red para obtener resultados adecuados. Esta imagen da un ejemplo de los bordes desconectados.
Por ejemplo, en la figura anterior, las carreteras con la etiqueta “119 están desconectadas de la red. Por lo tanto, tendrán el mismo número de componente conectado. Pero el recuento de este número será menor número de red totalmente conectada. Se eliminarán todas las aristas con el número de componente con un recuento inferior al máximo
Siga los pasos que se indican a continuación para completar esta tarea.
3.3.3.1. Exercise 12: Remove disconnected components¶
Para eliminar los componentes desconectados en la red de carreteras, se utilizarán las siguientes funciones de pgRouting, discutidas en Vistas de grafos:
pgr_extractVerticespgr_connectedComponents
Crear una tabla de vértices.
SELECT * INTO roads.roads_vertices
FROM pgr_extractVertices(
'SELECT gid AS id, source, target
FROM roads.roads_ways ORDER BY id');
Llenar las columnas x, y y geom.
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
Añadir una columna component en las tablas de aristas y vértices.
ALTER TABLE roads_ways ADD COLUMN component BIGINT;
ALTER TABLE roads_vertices ADD COLUMN component BIGINT;
Llenar la columna component de la tabla de vértices.
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
Llenar la columna component de la tabla de aristas.
UPDATE roads_ways SET component = v.component
FROM (SELECT id, component FROM roads_vertices) AS v
WHERE source = v.id;
UPDATE 529
Obtener el número del componente con mayor número de aristas.
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)
Eliminar las aristas que no pertenecen al componente más conectado.
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
Eliminar los vértices que no pertenecen al componente más conectado.
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. Encontrar los caminos atendidos por los hospitales¶
Después de preprocesar los datos, el siguiente paso es encontrar el área atendida por el hospital. Esta área se puede calcular desde la entrada del hospital o desde cualquier punto de la carretera cerca del hospital. En este ejercicio se calcula a partir del vértice de carretera más cercano. pgr_drivingDistance se utilizará para encontrar las carreteras atendidas. Los pasos a seguir son:
Encontrar el vértice de camino más cercano
Encontrar los caminos atendidos
Generalización de los caminos atendidos
3.4.1. pgr_drivingDistance¶
Para el siguiente paso se utilizará pgr_drivingDistance. Esto devuelve la distancia de conducción desde un nodo de inicio. Utiliza el algoritmo de Dijkstra para extraer todos los nodos que tienen costos menores o iguales a la distancia de valor. Los segmentos que se extraen se ajustan al árbol de expansión correspondiente.
Firmas
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)
Para más información la documentation de pgr_drivingDistance Documentation puede se emcotrada en esta liga..
3.4.2. Exercise 13: Find the closest road vertex¶
Hay múltiples vértices de camonos cerca del hospital. Crear una función para encontrar el vértice del camino geográficamente más cercano. La función closest_vertex toma la geometría de otra tabla como entrada y da el vértice del vértice más cercano como salida comparando geom de ambas tablas.
La siguiente consulta crea una función para encontrar el vértice de camino más cercano.
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
Probar la función
SELECT closest_vertex(poly_geom) FROM buildings_ways;
closest_vertex
----------------
120
120
112
234
293
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
45
281
16
45
15
46
14
257
62
62
14
206
84
84
206
206
84
206
14
84
84
84
278
84
63
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
311
105
116
130
144
342
115
173
133
143
144
144
311
133
173
340
311
311
99
311
146
145
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
94
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
280
280
279
280
279
44
44
94
94
62
206
62
131
62
62
366
48
317
10
340
126
238
129
338
106
124
103
103
103
103
124
103
131
128
119
289
138
23
39
6
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
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
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
132
133
133
130
133
144
143
97
106
106
103
103
103
128
127
127
124
124
129
129
129
125
125
132
109
109
130
130
130
133
84
84
206
206
326
168
(1049 rows)
3.4.3. Exercise 14: Finding the served roads using pgr_drivingDistance¶
Problema
Encuentrar los caminos que están a menos de 10 minutos a pie de los hospitales. Utilizar 1 m/s como velocidad a pie.
Solución
En este ejercicio, las calles servidas se calculan en base a un tiempo de recorrido a pie de 1 m/s, utilizando la función pgrdrivingDistance de la extensión pgRouting.
El tiempo en minutos se considera como
cost.el grafo es no dirigido.
Preparar una consulta
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
Para la siguiente consulta,
Se utiliza la declaración preparada.
La velocidad del peatón se fija en
1 m/s.Como
tiempo=distancia/velocidad,length_m/1 m/s/60da el tiempo en minutos.
tag_id = '318'as 318 is the value for hospital in the configuration table of the buildings.10por 10 minutos, que es un umbral paraagg_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.
Es notorio que algunas de las calles cercanas al hospital no están resaltadas. Por ejemplo, a carreteras del norte del hospital. Esto se debe a que la consulta seleccionó el único segmento por vértice de la carretera.
3.4.4. Exercise 15: Generalising the served roads¶
Los segmentos que están cerca del hospital también deben seleccionarse en las carreteras atendidas, ya que el hospital también sirve a esos edificios. La siguiente consulta toma la consulta de la sección anterior como una “”subconsulta”” y selecciona todas las aristas de roads_ways que tienen el mismo “source` y target a la de “”subconsulta”” (Línea 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
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
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
234 | 99 | 143 | 4.800252749039815 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33407C8560B0763552405D00755EBE1A3340
238 | 99 | 145 | 1.1480376358182636 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
473 | 158 | 312 | 0.786113614821774 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
472 | 312 | 311 | 2.7411680621005954 | 0102000020E6100000060000008D83A6808F355240FDAF29464C1B33400A80F10C9A3552401A25F95B4C1B334022ABB6F699355240407AD4A93C1B334022ABB6F69935524076EFF2BE3B1B33408E1C8E5399355240AD985B6B391B33400A01AF858F3552400D8D2782381B3340
471 | 312 | 311 | 0.5564225324543212 | 0102000020E6100000020000008D83A6808F355240FDAF29464C1B33400A01AF858F3552400D8D2782381B3340
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
19 | 293 | 13 | 4.065869900604024 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
237 | 293 | 145 | 2.3144715951044286 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
163 | 36 | 98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
164 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
231 | 98 | 142 | 4.748852358591146 | 0102000020E61000000200000064A82FF0813552409540EF32681B3340C9C9C4AD823552402DEC6987BF1A3340
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
340 | 213 | 212 | 11.632818723864188 | 0102000020E6100000060000006CE22E0CA235524018320D79BA1B3340F89B9A159B355240853474FDCC1A33408DFF4CCE9A35524034B511F4BC1A33404A35FD239D3552402301593E811A3340CC69053C9F3552401CA732D6481A33408A181B5FA035524090EFF730201A3340
541 | 213 | 364 | 0.1751197279697037 | 0102000020E6100000020000006CE22E0CA235524018320D79BA1B33409472ADACA3355240D18547D1B91B3340
232 | 35 | 142 | 1.2165702630128858 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A3340C9C9C4AD823552402DEC6987BF1A3340
61 | 35 | 36 | 4.7311518302823 | 0102000020E61000000200000040BE840A8E3552403E6BC889C01A33404636A15A8E355240838D4699681B3340
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
62 | 93 | 37 | 0.2040401587256805 | 0102000020E6100000020000003D0565BF933552400D648742151C334074942820923552407ACAC573111C3340
551 | 93 | 373 | 0.49530085030973936 | 0102000020E6100000020000003D0565BF933552400D648742151C334008F2A265933552404BA544B7031C3340
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
216 | 132 | 133 | 2.446762529952932 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33401FC253234F35524061B0D128131B3340
209 | 132 | 129 | 1.1289208611319963 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
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
185 | 37 | 112 | 4.302234144936451 | 0102000020E61000000200000074942820923552407ACAC573111C3340947C36BC6A35524084F57F0EF31B3340
352 | 37 | 223 | 0.9455073687469304 | 0102000020E61000000200000074942820923552407ACAC573111C33409162804493355240B62BF4C1321C3340
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
1 | 374 | 375 | 1.9504853345167399 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C33409F81470A9B355240C0571932C31B3340
549 | 374 | 372 | 0.45898206676688796 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C3340073763E2A035524090DD054A0A1C3340
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
216 | 132 | 133 | 2.446762529952932 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33401FC253234F35524061B0D128131B3340
209 | 132 | 129 | 1.1289208611319963 | 0102000020E610000002000000EF6BC94F4F3552402351C3126A1B33403CCFE9C3443552404727A6666A1B3340
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
357 | 82 | 228 | 0.6310753391117633 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
65 | 82 | 38 | 0.17177573693744302 | 0102000020E610000002000000420180089635524054AA44D95B1C3340139A249694355240BB7B2535591C3340
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
277 | 144 | 167 | 2.5029793486773766 | 0102000020E6100000020000002EF944436B3552409389004CBE1A334016139B8F6B35524055E70764651A3340
184 | 144 | 110 | 1.5240272770369918 | 0102000020E6100000020000002EF944436B3552409389004CBE1A33400C546B065D3552404B958334BE1A3340
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
1 | 374 | 375 | 1.9504853345167399 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C33409F81470A9B355240C0571932C31B3340
549 | 374 | 372 | 0.45898206676688796 | 0102000020E61000000200000062D68BA19C35524020E6DC37081C3340073763E2A035524090DD054A0A1C3340
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
279 | 143 | 168 | 2.810686789526953 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A3340ABD84E4A773552401950148D5A1A3340
236 | 143 | 144 | 1.2230800365242556 | 0102000020E6100000020000007C8560B0763552405D00755EBE1A33402EF944436B3552409389004CBE1A3340
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
205 | 125 | 126 | 2.427949297893071 | 0102000020E61000000200000024C9BD0A3A355240947C36BC6A1B3340539275383A3552406D86657D141B3340
167 | 125 | 101 | 1.429872300730441 | 0102000020E61000000200000024C9BD0A3A355240947C36BC6A1B33409C1320AF2C355240D5C7E8256B1B3340
203 | 129 | 125 | 1.1478621061192884 | 0102000020E6100000020000003CCFE9C3443552404727A6666A1B334024C9BD0A3A355240947C36BC6A1B3340
64 | 224 | 38 | 0.573025157917078 | 0102000020E61000000200000067B224E593355240608EC305451C3340139A249694355240BB7B2535591C3340
39 | 38 | 24 | 1.1074401160794767 | 0102000020E610000002000000139A249694355240BB7B2535591C3340AE896077953552404D6C9967801C3340
131 | 38 | 81 | 3.219256987972808 | 0102000020E610000002000000139A249694355240BB7B2535591C3340DB00C7F9763552401EFB592C451C3340
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
262 | 170 | 160 | 0.2061301681218233 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A33409EEBFB7090355240CF48298D3D1A3340
255 | 170 | 155 | 0.8047246919827096 | 0102000020E610000002000000346CEFAE8E35524009CB338A401A3340E13F82878E355240E807BF1E5D1A3340
397 | 82 | 260 | 1.2953298942217164 | 0102000020E610000006000000420180089635524054AA44D95B1C334059E1F148973552408E88731E5F1C3340B7F75F9D99355240ABC9F89C601C334027005A4E9D355240742090F0621C33401F20A9CF9F35524056777643641C3340CB8E43B3A1355240C00D8C17661C3340
357 | 82 | 228 | 0.6310753391117633 | 0102000020E610000002000000420180089635524054AA44D95B1C33405A5DF34F95355240B3446799451C3340
65 | 82 | 38 | 0.17177573693744302 | 0102000020E610000002000000420180089635524054AA44D95B1C3340139A249694355240BB7B2535591C3340
133 | 92 | 82 | 1.0990853762341473 | 0102000020E610000002000000C5C5F6FF96355240C3C4D5B7821C3340420180089635524054AA44D95B1C3340
263 | 153 | 160 | 0.8140006470576503 | 0102000020E6100000020000003982AF4390355240D25B97755A1A33409EEBFB7090355240CF48298D3D1A3340
534 | 160 | 359 | 0.15094729937231907 | 0102000020E6100000030000009EEBFB7090355240CF48298D3D1A3340F8D73C5890355240E868554B3A1A33409EBDE94C903552404EF27E37381A3340
253 | 160 | 154 | 0.6184639371754016 | 0102000020E6100000020000009EEBFB7090355240CF48298D3D1A3340EF8FF7AA953552403375B22F341A3340
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
277 | 144 | 167 | 2.5029793486773766 | 0102000020E6100000020000002EF944436B3552409389004CBE1A334016139B8F6B35524055E70764651A3340
184 | 144 | 110 | 1.5240272770369918 | 0102000020E6100000020000002EF944436B3552409389004CBE1A33400C546B065D3552404B958334BE1A3340
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)
La siguiente figura muestra el resultado visualizado de la consulta anterior. Las líneas resaltadas en «amarillo» muestran el «generalizado de las carreteras servidas». Esto da una mejor estimación de las áreas desde donde se puede llegar al hospital a una velocidad particular.
3.5. Cálculo de la población total atendida por el hospital¶
Ahora el siguiente paso es estimar la población dependiente. La fuente oficial de población es el censo realizado por el gobierno. Pero para este ejercicio, la población se estimará tanto a partir del area como del category del edificio. Esta zona se almacenará en las carreteras más cercanas. Los siguientes pasos explican este proceso en detalle.
3.5.1. Exercise 16: Finding the nearest roads of the buildings¶
Para almacenar la población de edificios en las carreteras, se encuentra la carretera más cercana a un edificio. Siga los pasos que se indican a continuación para completar esta tarea.
Crear función para encontrar el segmento más cercano.
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
Agregue una columna en
buildings_wayspara almacenar el identificador del borde más cercano
1ALTER TABLE buildings_ways
2ADD COLUMN edge_id INTEGER;
ALTER TABLE
Almacene el identificador de borde del borde más cercano en la nueva columna de
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¶
Después de encontrar la carretera más cercana, la suma de la población de todos los edificios más cercanos se almacena en la columna de población de la tabla de carreteras. La siguiente imagen muestra la salida visualizada donde las etiquetas de color azul muestran la población almacenada en las carreteras.
Siga los pasos que se indican a continuación para completar esta tarea.
Agregue una columna en
roads_wayspara almacenar la población
ALTER TABLE roads_ways ADD COLUMN population INTEGER;
ALTER TABLE
Actualizar los caminos con la suma de población de edificios más cercanos a ellos
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
Comprobar si la población se almacenó mediante la siguiente consulta.
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)