3. Buena salud y bienestar

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.

Objetivo de Desarrollo Sostenible 3: Buena Salud y Bienestar

“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. Preprocesamiento de datos de caminos y edificios

First step is to pre-process the data obtained from Datos para los Objetivos de Desarrollo Sostenible. This section will work the graph 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. This will be discussed in further sections.

3.2.1. Inspección de la estructura de la base de datos

First step in pre processing is to set the search path for Roads and Buildings data. Search path is a list of schemas helps the system determine how a particular table is to be imported. In this case, search path of roads table is set to roads and buildings schema. \dn is used to list down all the present schemas. SHOW search_path command shows the current search path. SET search_path is used to set the search path to roads and buildings. Finally, \dt is used to verify if the Schema have bees changed correctly. Following code snippets show the steps as well as the outputs.

3.2.1.1. Ejercicio 1: Inspección de los esquemas

Inspeccione los esquemas mostrando todos los esquemas actuales mediante el siguiente comando

\dn

El resultado del comando postgresql es:

   List of schemas
   Name    |  Owner
-----------+----------
 buildings | swapnil
 public    | postgres
 roads     | swapnil
(3 rows)

Los nombres de los esquemas son buildings` , roads and public. El propietario depende de quién tiene los derechos de la base de datos.

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;

El resultado del comando postgresql es:

   search_path
-----------------
 "$user", public
(1 row)

Esta es la ruta de búsqueda actual. No se puede acceder a las tablas mediante esta opción.

3.2.1.3. Ejercicio 3: Arreglar la ruta de búsqueda

En este caso, la ruta de búsqueda de la tabla de carreteras es la ruta de búsqueda a los esquemas roads` y buildings La siguiente consulta se utiliza para corregir la ruta de búsqueda

SET search_path TO roads,buildings,public;
SHOW search_path;
    search_path
-------------------
roads, buildings, public
(1 row)

3.2.1.4. Ejercicio 4: Enumerar tablas

Finalmente, dt` se utiliza para verificar si el esquema se ha cambiado correctamente.

\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 | swapnil
 roads     | configuration               | table | user
 roads     | roads_pointsofinterest      | table | user
 roads     | roads_ways                  | table | user
 roads     | roads_ways_vertices_pgr     | table | user
(8 rows)

3.2.1.5. Ejercicio 5: Contar el número de carreteras y edificios

La importancia de contar la información en este taller es asegurarse de que se utilizan los mismos datos y, en consecuencia, los resultados son los mismos. Además, algunas de las filas se pueden ver para comprender la estructura de la tabla y cómo se almacenan los datos en ella.

1-- Counting the number of Edges of roads
2SELECT COUNT(*) FROM roads_ways;
3
4-- Counting the number of Vertices of roads
5SELECT COUNT(*) FROM roads_ways_vertices_pgr;
6
7-- Counting the number of buildings 
8SELECT COUNT(*) FROM buildings_ways;

Ejercicio: 5 (Capítulo: SDG 3)

La siguiente imagen muestra las carreteras y edificios visualizados.

../_images/roads_and_buildings.png

3.2.2. Preprocesamiento de edificios

La tabla buildings_ways` contiene los edificios en forma de borde. Tienen que ser convertidos en polígonos para obtener el área.

3.2.2.1. Ejercicio 6: Añadir una columna espacial a la tabla

Agregue una columna espacial denominada poly_geom a la tabla buildings_ways para almacenar la geometría del polígono

1SELECT AddGeometryColumn('buildings','buildings_ways','poly_geom',4326,'POLYGON',2);

Ejercicio: 6 (Capítulo: ODS 3)

3.2.2.2. Ejercicio 7: Extracción de los polígonos con menos de 4 puntos

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.

1DELETE FROM buildings_ways 
2WHERE ST_NumPoints(the_geom) < 4 
3OR ST_IsClosed(the_geom) = FALSE;

Ejercicio: 7 (Capítulo: ODS 3)

3.2.2.3. Ejercicio 8: Creación de los polígonos

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);

Ejercicio: 8 (Capítulo: ODS 3)

3.2.2.4. Ejercicio 9: Cálculo del área

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.

  1. Agregar una columna para almacenar el área

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

1UPDATE buildings_ways 
2SET area = ST_Area(poly_geom::geography)::INTEGER;
3-- Process to discard disconnected roads

Ejercicio: 9 (Capítulo: ODS 3)

3.2.3. pgr_connectedComponents

Para el siguiente paso se utilizará pgr_connectedComponents. Se utiliza para encontrar los componentes conectados de un grafo no dirigido utilizando un enfoque basado en la búsqueda en profundidad.

Firmas

pgr_connectedComponents(edges_sql)

RETURNS SET OF (seq, component, node)
OR EMPTY SET

Para más información la documentation de pgr_connectedComponents puede se emcotrada en esta liga..

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

../_images/remove_disconnected_roads.png

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.2.4.1. Ejercicio 10: Buscar el identificador de componente para los vértices de carretera

El primer paso en preprocesamiento de carreteras es encontrar el identificador de componente conectado para los vértices de carretera. Siga los pasos que se indican a continuación para completar esta tarea.

  1. Agregue una columna denominada “component para almacenar el número de componente.

1ALTER TABLE roads_ways_vertices_pgr
2ADD COLUMN component INTEGER;
  1. Actualice la columna component en roads_ways_vertices_pgr con el número de componente

 1UPDATE roads_ways_vertices_pgr 
 2SET component = subquery.component 
 3FROM (
 4	SELECT * FROM pgr_connectedComponents(
 5		'SELECT gid AS id, source, target, cost, reverse_cost 
 6		FROM roads_ways'
 7			)
 8		) 
 9AS subquery
10WHERE id = node;

Esto almacenará el número del componente de cada segmento en la tabla. Ahora, la red de carreteras completamente conectada debería tener el recuento máximo en la tabla component.

SELECT component, count(*) FROM road_ways_vertices_pgr GROUP BY  component;

Ejercicio: 10 (Capítulo: ODS 3)

3.2.4.2. Ejercicio 11: Encontrar los componentes que deben eliminarse

Esta consulta selecciona todos los componentes que no son iguales al número de componente con el recuento máximo mediante una subconsulta que agrupa las filas en roads_ways_vertices_pgr por componentes.

1WITH
2subquery AS (
3	SELECT component, COUNT(*) 
4	FROM roads_ways_vertices_pgr 
5	GROUP BY component
6	)
7SELECT component FROM subquery 
8WHERE COUNT != (SELECT max(COUNT) FROM subquery);

Ejercicio: 11 (Capítulo: ODS 3)

3.2.4.3. Ejercicio 12: Encontrar los vértices de estos componentes

Encontar los vértices de los componentes que pertenecen al conjunto de componentes que se van a eliminar. La siguiente consulta selecciona todos los vértices de carretera que tienen el número de componente del ejercicio 11.

 1WITH
 2subquery AS (
 3	SELECT component, COUNT(*) 
 4	FROM roads_ways_vertices_pgr 
 5	GROUP BY component
 6	),
 7to_remove AS (
 8	SELECT component FROM subquery 
 9	WHERE COUNT != (SELECT max(COUNT) FROM subquery)
10	)
11SELECT id FROM roads_ways_vertices_pgr 
12WHERE component IN (SELECT * FROM to_remove);

Ejercicio: 12 (Capítulo: ODS 3)

3.2.4.4. Ejercicio 13: Eliminación de las aristas y vértices no deseados

  1. Eliminación de los segmentos no deseados

En la tabla “roads_ways` (tabla de segmentos) source y target tienen el id de los vértices desde donde comienza y termina lel segmento. Para eliminar todas las aristas desconectadas, la siguiente consulta toma el resultado de la consulta del paso 4 y elimina todas las aristas que tienen el mismo source` como el id.

 1DELETE FROM roads_ways WHERE source IN (
 2	WITH
 3	subquery AS (
 4		SELECT component, COUNT(*) 
 5		FROM roads_ways_vertices_pgr 
 6		GROUP BY component
 7		),
 8	to_remove AS (
 9		SELECT component FROM subquery 
10		WHERE COUNT != (SELECT max(COUNT) FROM subquery)
11		)
12	SELECT id FROM roads_ways_vertices_pgr 
13	WHERE component IN (SELECT * FROM to_remove)
14);
  1. Eliminación de vértices no utilizados

La siguiente consulta utiliza el resultado del paso 4 para eliminar los vértices de los segmentos desconectados.

 1WITH
 2subquery AS (
 3	SELECT component, COUNT(*) 
 4	FROM roads_ways_vertices_pgr 
 5	GROUP BY component
 6	),
 7to_remove AS (
 8	SELECT component FROM subquery 
 9	WHERE COUNT != (SELECT max(COUNT) FROM subquery)
10	)
11DELETE FROM roads_ways_vertices_pgr 
12WHERE component IN (SELECT * FROM to_remove);

Ejercicio: 13 (Capítulo: ODS 3)

3.3. 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.3.1. Ejercicio 14: Encontrar el vértice de la carretera más cercano

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.

../_images/finding_closest_vertex.png

La siguiente consulta crea una función para encontrar el vértice de camino más cercano.

1CREATE OR REPLACE FUNCTION closest_vertex(geom GEOMETRY)
2RETURNS BIGINT AS
3$BODY$
4SELECT id FROM roads_ways_vertices_pgr ORDER BY geom <-> the_geom LIMIT 1;
5$BODY$
6LANGUAGE SQL;

3.3.2. 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)

Uso de valores predeterminados

pgr_drivingDistance(edges_sql, start_vid, distance)
RETURNS SET OF (seq, node, edge, cost, agg_cost)

Vértice Único

pgr_drivingDistance(edges_sql, start_vid, distance [, directed])
RETURNS SET OF (seq, node, edge, cost, agg_cost)

Múltiples Vértices

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.3.3. Ejercicio 15: Encontrar las carreteras servidas usando pgr_drivingDistance

En este ejercicio, se calculan los segmentos atendidas en función del tiempo de viaje. Esto se puede calcular utilizando la función pgrdrivingDistance de pgRouting. El tiempo en minutos se considera como costo La columna agg_cost mostrara el tiempo total requerido para llegar al hospital.

Para la siguiente consulta,

  • En la línea 3, se supone que la velocidad de los peatones es de 1 m/s Como tiempo = distancia/velocidad, length_m / 1 m/s / 60 da el tiempo en minutos

  • In line 7, tag_id = '318' as 318 is the tag_id of hospital in the configuration file of buildings. Reference for Tag ID : Apéndice

  • En la línea 8, 10 se escribe durante 10 minutos, que es un umbral para agg_cost

  • En la línea 8, FALSE se escribe como la consulta es para el grafo no dirigido

 1SELECT gid,source,target,agg_cost,r.the_geom
 2FROM pgr_drivingDistance(
 3        'SELECT gid as id,source,target, length_m/60 AS cost,length_m/60 AS reverse_cost 
 4        FROM roads.roads_ways',
 5        (SELECT closest_vertex(poly_geom) 
 6        FROM buildings.buildings_ways 
 7        WHERE tag_id = '318'
 8        ), 10, FALSE
 9      ), roads.roads_ways AS r
10WHERE edge = r.gid 
11LIMIT 10;

Nota

LIMIT 10 muestra las primeras 10 filas de la salida.


Ejercicio: 15 (Capítulo: ODS 3)

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 evident 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. Next section will solve this issue by doing a small modification in the query.

../_images/service_area.png

3.3.4. Ejercicio 16: Generalización de las carreteras servidas

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

 1WITH subquery AS (
 2SELECT r.gid, edge,source,target,agg_cost,r.the_geom 
 3FROM pgr_drivingDistance(
 4        'SELECT gid as id,source,target, length_m/60 AS cost, length_m/60 AS reverse_cost 
 5        FROM roads.roads_ways',
 6        (SELECT closest_vertex(poly_geom) 
 7        FROM buildings.buildings_ways 
 8        WHERE tag_id = '318'
 9        ), 10, FALSE
10      ),roads.roads_ways AS r
11WHERE edge = r.gid)
12SELECT r.gid, s.source, s.target, s.agg_cost,r.the_geom 
13FROM subquery AS s, roads.roads_ways AS r 
14WHERE r.source = s.source OR r.target = s.target
15ORDER BY r.gid
16LIMIT 10;

Nota

LIMIT 10 muestra las primeras 10 filas de la salida.


Ejercicio: 16 (Capítulo: ODS 3)

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.

../_images/generalised_service_area.png

3.4. Cálculo de la población total atendida por el 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.4.1. Ejercicio 17: Estimación de la población de edificios

La población de un edificio se puede estimar por su área y su categoría. Los edificios de datos de OpenStreetMap se clasifican en varias categorías. Para este ejercicio, los edificios se clasifican en las siguientes clases:

  • Insignificante: La gente no vive en estos lugares. Pero el valor predeterminado es 1 debido a las personas sin hogar.

  • Muy escaso: La gente no vive en estos lugares. Pero el valor predeterminado es 2 porque puede haber personas vigilando el lugar.

  • Escaso: Edificios con baja densidad de población. Además, teniendo en cuenta las universidades y el colegio porque los estudiantes viven allí.

  • Moderado: Un tipo de ubicación de vivienda de unidad familiar.

  • Denso: Un edificio residencial de tamaño mediano.

  • Muy denso: Un edificio residencial de gran tamaño.

Reference: 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.

  1. Cree una función para encontrar la población utilizando el factor y el área específicos de la clase.

 1CREATE OR REPLACE FUNCTION  population(tag_id INTEGER,area INTEGER)
 2RETURNS INTEGER AS 
 3$BODY$
 4DECLARE 
 5population INTEGER;
 6BEGIN 
 7  IF tag_id <= 100 THEN population = 1; -- Negligible
 8  ELSIF 100 < tag_id AND tag_id <= 200 THEN  population = GREATEST(2, area * 0.0002); -- Very Sparse
 9  ELSIF 200 < tag_id AND tag_id <= 300 THEN  population = GREATEST(3, area * 0.002); -- Sparse
10  ELSIF 300 < tag_id AND tag_id <= 400 THEN population = GREATEST(5,  area * 0.05); -- Moderate
11  ELSIF 400 < tag_id AND tag_id <= 500  THEN population = GREATEST(7, area * 0.7); -- Dense
12  ELSIF tag_id > 500  THEN population = GREATEST(10,area * 1); -- Very Dense
13  ELSE population = 1;
14  END IF;
15  RETURN population;
16END;
17$BODY$
18LANGUAGE plpgsql;

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.

  1. Agregue una columna para almacenar la población en buildings_ways

1ALTER TABLE buildings_ways
2ADD COLUMN population INTEGER;

3. Use the population function to store the population in the new column created in the building_ways.

1UPDATE buildings_ways 
2SET population = population(tag_id,area)::INTEGER;

Ejercicio: 17 (Capítulo: ODS 3)

3.4.2. Ejercicio 18: Encontrar las carreteras más cercanas para almacenar a la población

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.

  1. Crear función para encontrar el segmento más cercano.

1CREATE OR REPLACE FUNCTION closest_edge(geom GEOMETRY)
2RETURNS BIGINT AS
3$BODY$
4SELECT gid FROM roads_ways ORDER BY geom <-> the_geom LIMIT 1;
5$BODY$
6LANGUAGE SQL;
  1. Agregue una columna en buildings_ways para almacenar el identificador del borde más cercano

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

Ejercicio: 18 (Capítulo: ODS 3)

3.4.3. Ejercicio 19: Almacenar la población en las carreteras

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.

../_images/road_population.png

Siga los pasos que se indican a continuación para completar esta tarea.

  1. Agregue una columna en roads_ways para almacenar la población

1ALTER TABLE roads_ways
2ADD COLUMN population INTEGER;
  1. 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;                                                                                                                       
  1. Comprobar si la población se almacenó mediante la siguiente consulta.

1SELECT population FROM roads_ways WHERE gid = 441;

Ejercicio: 19 (Capítulo: ODS 3)

3.4.4. Ejercicio 20: Encontrar la población total

Final step is to find the total population served by the hospital based on travel-time. Use the query from Exercise 16: Generalising the served roads as a subquery to get all the edges in the roads served. Note that s.population is added in line 14 which gives the population. After getting the population for each edge/road, use sum() to get the total population which is dependant on the hospital.

 1WITH subquery
 2AS (
 3	WITH subquery AS (
 4	SELECT r.gid,source,target,agg_cost, r.population,r.the_geom 
 5	FROM pgr_drivingDistance(
 6		    'SELECT gid as id,source,target, length_m/60 AS cost, length_m/60 AS reverse_cost 
 7		    FROM roads.roads_ways',
 8		    (SELECT closest_vertex(poly_geom) 
 9		    FROM buildings.buildings_ways 
10		    WHERE tag_id = '318'
11		    ), 10, FALSE
12		  ),roads.roads_ways AS r
13	WHERE edge = r.gid)
14	SELECT r.gid, r.the_geom, s.population
15	FROM subquery AS s,roads.roads_ways AS r 
16	WHERE r.source = s.source OR r.target = s.target
17	)
18SELECT SUM(population) FROM subquery;

Ejercicio: 20 (Capítulo: ODS 3)