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.

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

-- Counting the number of Edges of roads
SELECT COUNT(*) FROM roads_ways;

-- Counting the number of Vertices of roads
SELECT COUNT(*) FROM roads_ways_vertices_pgr;

-- Counting the number of buildings 
SELECT COUNT(*) FROM buildings_ways;

Ejercicio: 5 (Capítulo: SDG 3)

La siguiente imagen muestra las carreteras y edificios visualizados.

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

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

DELETE FROM buildings_ways 
WHERE ST_NumPoints(the_geom) < 4 
OR 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.

UPDATE buildings_ways 
SET 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.

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

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

ALTER TABLE roads_ways_vertices_pgr
ADD COLUMN component INTEGER;

Actualice la columna component en roads_ways_vertices_pgr con el número de componente

UPDATE roads_ways_vertices_pgr 
SET component = subquery.component 
FROM (
	SELECT * FROM pgr_connectedComponents(
		'SELECT gid AS id, source, target, cost, reverse_cost 
		FROM roads_ways'
			)
		) 
AS subquery
WHERE 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.

WITH
subquery AS (
	SELECT component, COUNT(*) 
	FROM roads_ways_vertices_pgr 
	GROUP BY component
	)
SELECT component FROM subquery 
WHERE 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.

WITH
subquery AS (
	SELECT component, COUNT(*) 
	FROM roads_ways_vertices_pgr 
	GROUP BY component
	),
to_remove AS (
	SELECT component FROM subquery 
	WHERE COUNT != (SELECT max(COUNT) FROM subquery)
	)
SELECT id FROM roads_ways_vertices_pgr 
WHERE 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 ¶

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.

DELETE FROM roads_ways WHERE source IN (
	WITH
	subquery AS (
		SELECT component, COUNT(*) 
		FROM roads_ways_vertices_pgr 
		GROUP BY component
		),
	to_remove AS (
		SELECT component FROM subquery 
		WHERE COUNT != (SELECT max(COUNT) FROM subquery)
		)
	SELECT id FROM roads_ways_vertices_pgr 
	WHERE component IN (SELECT * FROM to_remove)
);

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.

WITH
subquery AS (
	SELECT component, COUNT(*) 
	FROM roads_ways_vertices_pgr 
	GROUP BY component
	),
to_remove AS (
	SELECT component FROM subquery 
	WHERE COUNT != (SELECT max(COUNT) FROM subquery)
	)
DELETE FROM roads_ways_vertices_pgr 
WHERE 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.

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_ways_vertices_pgr ORDER BY geom <-> the_geom LIMIT 1;
$BODY$
LANGUAGE 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

SELECT gid,source,target,agg_cost,r.the_geom
FROM pgr_drivingDistance(
        'SELECT gid as id,source,target, length_m/60 AS cost,length_m/60 AS reverse_cost 
        FROM roads.roads_ways',
        (SELECT closest_vertex(poly_geom) 
        FROM buildings.buildings_ways 
        WHERE tag_id = '318'
        ), 10, FALSE
      ), roads.roads_ways AS r
WHERE edge = r.gid 
LIMIT 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.

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

WITH subquery AS (
SELECT r.gid, edge,source,target,agg_cost,r.the_geom 
FROM pgr_drivingDistance(
        'SELECT gid as id,source,target, length_m/60 AS cost, length_m/60 AS reverse_cost 
        FROM roads.roads_ways',
        (SELECT closest_vertex(poly_geom) 
        FROM buildings.buildings_ways 
        WHERE tag_id = '318'
        ), 10, FALSE
      ),roads.roads_ways AS r
WHERE edge = r.gid)
SELECT r.gid, s.source, s.target, s.agg_cost,r.the_geom 
FROM subquery AS s, roads.roads_ways AS r 
WHERE r.source = s.source OR r.target = s.target
ORDER BY r.gid
LIMIT 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.

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.

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;

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;

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

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

CREATE OR REPLACE FUNCTION closest_edge(geom GEOMETRY)
RETURNS BIGINT AS
$BODY$
SELECT gid FROM roads_ways ORDER BY geom <-> the_geom LIMIT 1;
$BODY$
LANGUAGE SQL;

Agregue una columna en buildings_ways para almacenar el identificador del borde más cercano

ALTER TABLE buildings_ways
ADD COLUMN edge_id INTEGER;

Almacene el identificador de borde del borde más cercano en la nueva columna de buildings_ways

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

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

Agregue una columna en roads_ways para almacenar la población

ALTER TABLE roads_ways
ADD COLUMN population INTEGER;

Actualizar los caminos con la suma de población de edificios más cercanos a ellos

UPDATE roads_ways SET population = SUM
FROM (
	SELECT edge_id, SUM(population) 
	FROM buildings_ways GROUP BY edge_id
	) 
AS subquery 
WHERE gid = edge_id;                                                                                                                       

Comprobar si la población se almacenó mediante la siguiente consulta.

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

WITH subquery
AS (
	WITH subquery AS (
	SELECT r.gid,source,target,agg_cost, r.population,r.the_geom 
	FROM pgr_drivingDistance(
		    'SELECT gid as id,source,target, length_m/60 AS cost, length_m/60 AS reverse_cost 
		    FROM roads.roads_ways',
		    (SELECT closest_vertex(poly_geom) 
		    FROM buildings.buildings_ways 
		    WHERE tag_id = '318'
		    ), 10, FALSE
		  ),roads.roads_ways AS r
	WHERE edge = r.gid)
	SELECT r.gid, r.the_geom, s.population
	FROM subquery AS s,roads.roads_ways AS r 
	WHERE r.source = s.source OR r.target = s.target
	)
SELECT SUM(population) FROM subquery;

Ejercicio: 20 (Capítulo: ODS 3)