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

../_images/sdg11_output.png

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

1CREATE TABLE city_vertex (id BIGINT, name TEXT, geom geometry);
2INSERT INTO city_vertex(id, name, geom) VALUES (
35,'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

El primer paso es preprocesar los datos obtenidos de Datos para los Objetivos de Desarrollo Sostenible. En esta sección se trabajará el grafo que se va a utilizar para el procesamiento. Mientras se construye el grafo, se deben inspeccionar los datos para determinar si hay datos no válidos. Este es un paso muy importante para garantizar que los datos tengan la calidad requerida. pgRouting también se puede utilizar para realizar algunos ajustes de datos. Esto se discutirá en secciones posteriores.

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.

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

1DELETE FROM waterways_ways 
2WHERE osm_id 
3IN (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.

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

1ALTER TABLE waterways_ways_vertices_pgr
2ADD COLUMN component INTEGER;
3
4ALTER TABLE waterways_ways
5ADD COLUMN component INTEGER;
  1. Obtener los componentes conectados de las vías fluviales

 1UPDATE waterways_ways_vertices_pgr SET component = subquery.component
 2FROM (SELECT * FROM pgr_connectedComponents(
 3'SELECT gid AS id, source, target, cost, reverse_cost FROM waterways_ways')
 4) AS subquery
 5WHERE id = node;
 6
 7UPDATE waterways_ways SET component=a.component FROM (
 8SELECT id, component FROM waterways_ways_vertices_pgr
 9) AS a  
10WHERE 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.

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

1ALTER TABLE waterways.city_vertex
2ADD COLUMN city_buffer geometry;
  1. Almacenamiento de la geometría de zona límite

1UPDATE waterways.city_vertex 
2SET city_buffer = ST_Buffer((geom),0.005) 
3WHERE  name = 'Munshigang';
  1. Visualización de los resultados de la operación de búfer

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

1CREATE OR REPLACE FUNCTION get_city_buffer(city_id INTEGER)
2RETURNS geometry AS
3$BODY$                                                                         
4SELECT city_buffer FROM city_vertex WHERE id = city_id;
5$BODY$
6LANGUAGE 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.

1SELECT DISTINCT component
2FROM waterways.city_vertex, waterways.waterways_ways
3WHERE 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.

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

1ALTER TABLE waterways_ways
2ADD COLUMN rain_zone geometry;
  1. Almacenamiento de la geometría de zona límite

1UPDATE waterways.waterways_ways 
2SET rain_zone = ST_Buffer((the_geom),0.005) 
3WHERE 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.

1SELECT ST_Union(rain_zone) AS Combined_Rain_Zone
2FROM waterways_ways;

Ejercicio: 13 (Capítulo: ODS 11)