5. Ciudades y comunidades sostenibles ¶

“Ciudades y Comunidades Sostenibles” es el 11º Objetivo de Desarrollo Sostenible que aspira a hacer que las ciudades sean «inclusivas, seguras, resilientes» y «sostenibles». El mundo se está urbanizando cada vez más. Desde 2007, más de la mitad de la población mundial vive en ciudades. Esto hace que sea muy importante que las ciudades permanezcan alertas cuando existe la posibilidad de desastres como inundaciones. La administración local debe saber si su ciudad se va a ver afectada por las lluvias que ocurren en sus proximidades para que puedan alertar a los ciudadanos. Este ejercicio resolverá uno de estos problemas.

Objetivo de Desarrollo Sostenible 11: Ciudades y Comunidades Sostenibles

“Fuente de la imagen <https://sdgs.un.org/goals/goal11>`__

5.1. Problema: Ciudad afectada por la lluvia o no?¶

Declaración del problema

Determinar las áreas donde si llueve afectará a una ciudad/pueblo

Idea central

Si llueve en las cercanías de un río que conecta la ciudad, la ciudad se verá afectada por las lluvias.

Enfoque

Eligir una ciudad
Obtener los ríos (segmentos)
Crear componentes fluviales
Crear una zona límite alrededor de la ciudad
Encontrar los componentes que intersectan la zona limitada
Encontrar las zonas de lluvia

5.2. Eligir una ciudad ¶

Para este ejercicio, se elige la ciudad de Munshigang de Bangladesh. Esta ciudad tiene múltiples ríos en su proximidad, lo que la convierte en un lugar adecuado para demostrar este ejercicio. El ejercicio tratará de encontrar las zonas, donde si llueve la ciudad se verá afectada. Para definir la ubicación de esta ciudad y usarla en pasos posteriores, crear una tabla para almacenar el nombre junto con los valores de latitud y longitud de la ubicación de la ciudad. Esto almacena la ciudad como un punto.

5.2.1. Ejercicio 1: Crear un punto para la ciudad ¶

CREATE TABLE city_vertex (id BIGINT, name TEXT, geom geometry);
INSERT INTO city_vertex(id, name, geom) VALUES (
5,'Munshigang', ST_SetSRID(ST_Point(89.1967,22.2675),4326));

Ejercicio: 1 (Capítulo: ODS 11)

Los valores de latitud y longitud se convierten en geometry usando ST_Point que devuelve un punto con los valores de coordenadas X e Y dados. ST_SetSRID se utiliza para establecer el SRID (Identificado de referencia Espacial) en la geometría del punto en 4326.

5.3. Preprocesamiento de datos de vías navegables ¶

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.

5.3.1. Establecer la ruta de búsqueda de las vías fluviales ¶

El primer paso en el preprocesamiento es establecer la ruta de búsqueda para los datos de Waterways. La ruta de búsqueda es una lista de esquemas que ayuda al sistema a determinar cómo se va a importar una tabla en particular.

5.3.1.1. Ejercicio 2: Inspección de los esquemas ¶

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

\dn

   List of schemas
   Name    |  Owner
-----------+----------
 public    | postgres
 waterway  | <user-name>
(2 rows)

Los nombres de esquema son waterway` and public. El propietario depende de quién tenga los derechos de la base de datos.

5.3.1.2. Ejercicio 3: 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 las tablas mediante esta opción.

5.3.1.3. Ejercicio 4: Arreglar la ruta de búsqueda ¶

En este caso, la ruta de búsqueda de la tabla de carreteras se establece en el esquema waterways . La siguiente consulta se utiliza para corregir la ruta de búsqueda

SET search_path TO waterways,public;
SHOW search_path;

    search_path
-------------------
 waterways, public
(1 row)

5.3.1.4. Ejercicio 5: Enumerar tablas ¶

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

\dt

                     List of relations
  Schema   |            Name             | Type  |  Owner
-----------+-----------------------------+-------+---------
 public    | spatial_ref_sys             | table | <user-name>
 waterways | configuration               | table | user
 waterways | waterways_pointsofinterest  | table | user
 waterways | waterways_ways              | table | user
 waterways | waterways_ways_vertices_pgr | table | user
(5 rows)

5.3.1.5. Ejercicio 6: Contar el número de vías navegables ¶

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.

SELECT count(*) FROM waterways_ways;

Ejercicio: 6 (Capítulo: ODS 11)

5.3.2. Ejercicio 7: Eliminar los ríos que están en pantanos ¶

Este ejercicio se centra solo en las zonas del continente, donde si llueve la ciudad se ve afectada. Por lo tanto, los ríos que están allí en el área del pantano tienen que ser eliminados de la tabla waterways_ways .

DELETE FROM waterways_ways 
WHERE osm_id 
IN (721133202, 908102930, 749173392, 652172284, 126774195, 720395312);

Ejercicio: 7 (Capítulo: ODS 11)

5.4. `pgr_connectedComponents` para el preprocesamiento de vías navegables ¶

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

5.5. Ejercicio 8: Obtener los componentes conectados de las vías fluviales ¶

Como los ríos en los datos no tienen un solo segmento, es decir, múltiples segmentos forman un río, es importante encontrar los segmentos conectados y almacenar la información en la tabla waterways_ways. Esto ayudará a identificar cuáles segmentos pertenecen a un río. Primero se encuentran los componentes conectados y luego se almacenan en una nueva columna llamada component.

La función pgRouting pgr_connectedComponents se utiliza para completar esta tarea. Se crea una subconsulta para averiguar todos los componentes conectados. Después de eso, la columna component se actualiza utilizando los resultados obtenidos de la subconsulta. Esto ayuda a almacenar el identificador del componente en la tabla waterways_ways_vertices_pgr. La siguiente consulta utiliza este resultado y almacena el identificador del componente en la tabla waterways_ways (bordes). 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 waterways_ways_vertices_pgr
ADD COLUMN component INTEGER;

ALTER TABLE waterways_ways
ADD COLUMN component INTEGER;

Obtener los componentes conectados de las vías fluviales

UPDATE waterways_ways_vertices_pgr SET component = subquery.component
FROM (SELECT * FROM pgr_connectedComponents(
'SELECT gid AS id, source, target, cost, reverse_cost FROM waterways_ways')
) AS subquery
WHERE id = node;

UPDATE waterways_ways SET component=a.component FROM (
SELECT id, component FROM waterways_ways_vertices_pgr
) AS a  
WHERE id = source;

Con el identificador del componente almacenado tanto en elas tablas de vertices y de segmentos de las vías fluviales, se procede al siguiente paso.

Ejercicio: 8 (Capítulo: ODS 11)

5.6. Ejercicio 9: Crear una zona límite alrededor de la ciudad ¶

Crear una zona límite alrededor de la ciudad para definir un área, dentro de la cual se encontraría la intersección de los ríos. ST_Buffer se utiliza para crear este búfer. Siga los pasos que se indican a continuación para completar esta tarea.

Agregar columna para almacenar la geometría de zona límite

ALTER TABLE waterways.city_vertex
ADD COLUMN city_buffer geometry;

Almacenamiento de la geometría de zona límite

UPDATE waterways.city_vertex 
SET city_buffer = ST_Buffer((geom),0.005) 
WHERE  name = 'Munshigang';

Visualización de los resultados de la operación de búfer

SELECT city_buffer FROM waterways.city_vertex;

Ejercicio: 9 (Capítulo: ODS 11)

5.6.1. Ejercicio 10: Crear una función que obtenga la zona límite de la ciudad ¶

Se puede crear una función para la misma tarea. Esto será de ayuda cuando la tabla tenga más de una ciudad.

CREATE OR REPLACE FUNCTION get_city_buffer(city_id INTEGER)
RETURNS geometry AS
$BODY$                                                                         
SELECT city_buffer FROM city_vertex WHERE id = city_id;
$BODY$
LANGUAGE SQL;

5.7. Ejercicio 11: Encontrar los componentes que intersectan la zona limitada ¶

El siguiente paso es encontrar los componentes de las vías fluviales que se encuentran dentro de la zona límite de la ciudad. Estos son los flujos de agua que afectarán a la ciudad cuando llueva a su alrededor. Esto se hace usando ST_Intersects. Tenga en cuenta que la función “get_city_buffer se utiliza en la consulta a continuación.

SELECT DISTINCT component
FROM waterways.city_vertex, waterways.waterways_ways
WHERE ST_Intersects(the_geom, get_city_buffer(5));

Los resultados muestran los distintos números de componentes que se encuentran dentro de la zona límite de la ciudad. El siguiente paso es obtener todos los bordes que tienen esos componentes.

Ejercicio: 11 (Capítulo: ODS 11)

5.8. Ejercicio 12: Obtener las zonas de lluvia ¶

Este es el paso final del ejercicio. En esta, se está encontrando la zona donde si llueve, la ciudad se vería afectada, también se puede llamar como “”zona de lluvia””. Para esto, crear una zona límite alrededor de los componentes del río. Primero, agregue columnas llamadas rain_zone en waterways_ways para almacenar la geometría de las zonas de lluvia. Luego, buscar la zona para cada segmento que intersecta el área de la zona límite ST_Buffer y actualizar la columna rain_zone. Siguir los pasos que se indican a continuación para completar esta tarea.

Agregar columna para almacenar la geometría de zona límite

ALTER TABLE waterways_ways
ADD COLUMN rain_zone geometry;

Almacenamiento de la geometría de zona límite

UPDATE waterways.waterways_ways 
SET rain_zone = ST_Buffer((the_geom),0.005) 
WHERE ST_Intersects(the_geom, get_city_buffer(5));

Esto nos dará la zona requerida, donde si llueve, la ciudad se verá afectada.

Ejercicio: 12 (Capítulo: ODS 11)

5.9. Ejercicio 13: Crear una unión de zonas de lluvia ¶

Los polígonos múltiples que se obtienen también se pueden fusionar usando “ST_Union. Esto dará un solo polígono como salida.

SELECT ST_Union(rain_zone) AS Combined_Rain_Zone
FROM waterways_ways;

Ejercicio: 13 (Capítulo: ODS 11)