4. Energía asequible y limpia

“Energía asequible y limpia” es el 7º Objetivo de Desarrollo Sostenible 11. Aspira a garantizar el acceso a una energía «asequible, fiable, sostenible» y «moderna» para todos. Hoy en día, la energía renovable está logrando ganancias impresionantes en el sector eléctrico. A medida que se construyan más y más nuevos asentamientos, se desarrollará una nueva red de distribución de electricidad. La distribución de electricidad es una infraestructura muy costosa. Encontrar el camino óptimo para colocar esta infraestructura es crucial para mantener la asequibilidad de la electricidad para todos. Este ejercicio se centra en encontrar esta ruta / red óptima para colocar el equipo de distribución de electricidad.

Objetivo de Desarrollo Sostenible 7: Energía asequible y limpia

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

4.1. Problema: Optimización de la red de distribución eléctrica

Declaración del problema

Determinar la menor longitud del camino para la colocación de equipo de distribución de electricidad de tal manera que cada edificio sea atendido

../_images/sdg7_output.png

Idea central

Es posible que las líneas eléctricas no estén en todas las carreteras de la ciudad. En una red de carreteras compleja de una ciudad, la red se puede optimizar para una longitud menor, de modo que las líneas eléctricas lleguen a todas las localidades de la ciudad. Menor longitud conduce a una mayor rentabilidad, lo que resulta en electricidad asequible.

Enfoque

  • Extraer componentes conectados de carreteras

  • Usar pgRouting para encontrar el árbol de expansión mínimo

  • Comparar la longitud total de las carreteras y el árbol de expansión mínimo

4.2. Preprocesamiento de datos de carreteras

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.

4.2.1. Establecer la ruta de búsqueda de carreteras

El primer paso en el preprocesamiento es establecer la ruta de búsqueda para los datos de Carreteras. 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.

4.2.1.1. Ejercicio 1: Inspección de los esquemas actuales

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

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

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

4.2.1.2. Ejercicio 2: Inspeccion de la ruta de búsqueda actual

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.

4.2.1.3. Ejercicio 3: Arreglo de la ruta de búsqueda

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

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

4.2.2. Ejercicio 4: Enumerar las 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>
 roads     | configuration               | table | user
 roads     | roads_pointsofinterest      | table | user
 roads     | roads_ways                  | table | user
 roads     | roads_ways_vertices_pgr     | table | user
(5 rows)

4.2.3. Ejercicio 5: Contar el número de carreteras

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;

Ejercicio: 5 (Capítulo: ODS 7)

4.3. pgr_connectedComponents para el preprocesamiento de carreteras

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

4.4. Extraer componentes conectados de carreteras

Similar to Buena salud y bienestar, the disconnected roads have to be removed from their network to get appropriate results.

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

4.4.1. Ejercicio 6: 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 en 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		) 
8AS subquery
9WHERE 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.


if done before: Ejercicio: 10 (Capítulo: ODS 3) if not done before: Ejercicio: 6 (Capítulo: ODS 7)

4.4.2. Ejercicio 7: 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 != (
 9	SELECT max(count) FROM subquery
10);

if done before: Ejercicio: 11 (Capítulo: ODS 3) if not done before: Ejercicio: 7 (Capítulo: ODS 7)

4.4.3. Ejercicio 8: Encontrar los vértices de la carretera de estos componentes

Encontar los vértices de la carretera si estos componentes pertenecen a los 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 7.

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

if done before: Ejercicio: 12 (Capítulo: ODS 3) if not done before: Ejercicio: 8 (Capítulo: ODS 7)

4.4.4. Ejercicio 9: 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 != (
11				SELECT max(count) FROM subquery
12				)
13	)
14	SELECT id FROM roads_ways_vertices_pgr 
15	WHERE component IN (SELECT * FROM to_remove)
16);
  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	),
 7	to_remove AS (
 8		SELECT component FROM subquery 
 9		WHERE count != (SELECT max(count) FROM subquery)
10		)
11	DELETE FROM roads_ways_vertices_pgr 
12	WHERE component IN (SELECT * FROM to_remove
13);

if done before: Ejercicio: 13 (Capítulo: ODS 3) if not done before: Ejercicio: 9 (Capítulo: ODS 7)

4.5. pgr_kruskalDFS

Para el siguiente paso se utilizará pgr_kruskalDFS. El algoritmo de Kruskal se utiliza para obtener el árbol de expansión mínimo con el orden de búsqueda de profundidad primero. Un árbol de expansión mínimo (MST) es un subconjunto de aristas de un grafo conectado no dirigido que conecta todos los vértices juntos, sin ningún ciclo, tal que la suma de los pesos de las aristas sea lo más pequeña posible.

Firmas

pgr_kruskalDFS(Edges SQL, Root vid [, max_depth])
pgr_kruskalDFS(Edges SQL, Root vids [, max_depth])

RETURNS SET OF (seq, depth, start_vid, node, edge, cost, agg_cost)

Un solo vértice

pgr_kruskalDFS(Edges SQL, Root vid [, max_depth])

RETURNS SET OF (seq, depth, start_vid, node, edge, cost, agg_cost)

Múltiples vértices

pgr_kruskalDFS(Edges SQL, Root vids [, max_depth])

RETURNS SET OF (seq, depth, start_vid, node, edge, cost, agg_cost)

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

4.6. Ejercicio 10: Encontrar el árbol de expansión mínimo

La red de carreteras tiene un bosque de expansión mínimo que es una unión de los árboles de expansión mínimos para sus componentes conectados. Este bosque de expansión mínimo es la red óptima de componentes de distribución de electricidad.

Para completar esta tarea, ejecute la consulta siguiente.

1SELECT source,target,edge, r.the_geom
2FROM pgr_kruskalDFS(
3    'SELECT gid AS id, source, target, cost, reverse_cost, the_geom 
4    FROM roads.roads_ways ORDER BY id',
5    91), 
6roads.roads_ways AS r
7WHERE edge = r.gid
8LIMIT 10;

La siguiente consulta dará los resultados con el vértice de origen, el vértice de destino, el identificador del segmento, y el costo agregado.

1SELECT source,target,edge,agg_cost
2FROM pgr_kruskalDFS(
3    'SELECT gid AS id, source, target, cost, reverse_cost, the_geom 
4    FROM roads.roads_ways 
5    ORDER BY id',91), 
6roads.roads_ways AS r
7WHERE edge = r.gid 
8ORDER BY agg_cost
9LIMIT 10;

Nota

LIMIT 10 muestra las primeras 10 filas de la salida.


Ejercicio: 10 (Capítulo: ODS 7)

4.7. Comparación entre longitudes totales y óptimas

Las longitudes totales de la red y el árbol de expansión mínimo se pueden comparar para ver la diferencia entre ambos. Para hacerlo, siga los pasos a continuación:

4.7.1. Ejercicio 11: Calcular la longitud total del material requerido en km

Calcular la longitud total del árbol de expansión mínimo, que es una estimación de la longitud total del material requerido.

 1SELECT SUM(length_m)/1000 
 2FROM (
 3	SELECT source,target,edge,agg_cost,r.length_m             
 4	FROM pgr_kruskalDFS(
 5		'SELECT gid AS id, source, target, cost, reverse_cost, the_geom 
 6		FROM roads.roads_ways 
 7		ORDER BY id',91), 
 8	roads.roads_ways AS r
 9	WHERE edge = r.gid 
10	ORDER BY agg_cost) 
11AS subquery;

Nota

(length_m)/1000 se utiliza para encontrar la longitud en kilómetros


Ejercicio: 11 (Capítulo: ODS 7)

4.7.2. Ejercicio 12: Calcular la longitud total de las carreteras

Calcular la longitud total de la red de carreteras del área dada.

1SELECT SUM(length_m)/1000 FROM roads_ways;

Nota

(length_m)/1000 se utiliza para encontrar la longitud en kilómetros

Para esta área estamos obteniendo los siguientes resultados:

  • Longitud total de la carretera: 55.68 km

  • Longitud óptima de la red: “29.89 km

La longitud del árbol de expansión mínima es aproximadamente la mitad de la longitud de la red total de carreteras.


Ejercicio: 12 (Capítulo: ODS 7)

4.8. Otras posibles extensiones al ejercicio

  • Encontrar la red óptima de carreteras de tal manera que llegue a todos los edificios

  • Encontrar el número y la ubicación óptimos de los transformadores de electricidad