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 del equipo de distribución de electricidad de tal manera que cada edificio sea atendido.

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 ¶

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

4.2.1.3. Ejercicio 3: Arreglar 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 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.

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

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.

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

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.

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

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.

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

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 ¶

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

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.

SELECT source,target,edge, r.the_geom
FROM pgr_kruskalDFS(
    'SELECT gid AS id, source, target, cost, reverse_cost, the_geom 
    FROM roads.roads_ways ORDER BY id',
    91), 
roads.roads_ways AS r
WHERE edge = r.gid
LIMIT 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.

SELECT source,target,edge,agg_cost
FROM pgr_kruskalDFS(
    'SELECT gid AS id, source, target, cost, reverse_cost, the_geom 
    FROM roads.roads_ways 
    ORDER BY id',91), 
roads.roads_ways AS r
WHERE edge = r.gid 
ORDER BY agg_cost
LIMIT 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.

SELECT SUM(length_m)/1000 
FROM (
	SELECT source,target,edge,agg_cost,r.length_m             
	FROM pgr_kruskalDFS(
		'SELECT gid AS id, source, target, cost, reverse_cost, the_geom 
		FROM roads.roads_ways 
		ORDER BY id',91), 
	roads.roads_ways AS r
	WHERE edge = r.gid 
	ORDER BY agg_cost) 
AS 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.

SELECT 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