Análisis espacial en QGIS - Parte 1 SQL y calculadora de expresiones

---

Por: Samuel Mesa
Fecha: 11 de noviembre de 2023
Objetivo: Conocer las herramientas disponibles de QGIS Deskop para llevar a cabo el análisis espacial.
Datos: Para el desarrollo de los ejercios se utiliza los datos correspondientes a los datos de accidentalidad vial del SIMUR Secretaría Distrital de Movilidad de Bogotá.

---

Se tiene los siguientes datos correspondientes a la accidentalidad vial de la Localidad de Teusaquillo, desde el 2002 hasta el mes de junio de 2021.

El modelo ER establecido para los datos de acuerdo a los datos entregados del SIMUR:

1. Extraer los datos correspondientes a la localidad

Debido a que los datos entregados de las tablas actor_vial, vehiculo, via y causa contiene todos los registros de Bogotá, es necesario usar algunas herramientas adicionales a QGIS Desktop para realizar este proceso. En QGIS el complemento DB Manager es poco eficiente para un gran número de registros en las tablas.

Se recomienda la descarga del Software de Spatialite GUI desde el siguiente enlace Web: http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-prev/, a la fecha correspode a spatialite_gui-NG-5.0.0-win-amd64.7z. Esta herramienta permite realizar otras tareas adicionales a las ofrecidas por QGIS Desktop.

Una vez se descomprime el achivo ejecutar spatialite_gui.exe para eecutar el programa, la interfaz es sencilla y comprende:

(1) Panel de exploración y gestión de la base de datos: capas, tablas, vistas
(2) Instrucciones SQL
(3) Resultado de la instrucción SQL
(4) Ejecutar la instrucción SQL

1.1. Conectar la base de datos GeoPackage

Desde Menu –> Connecting an existing SQLite DB realizar la conexión a la base SIMUR_ACCIDENTALIDAD.gpkg

1.2. Cargar tablas CSV

Desde Menu -> Advances -> Virtual CSV/TXT se realiza la carga de forma temporal cada tabla CSV par el cual se requiera hacer selección en el contexto de la zona de estudio. Para el ejemplo se antecede el carácter v para especificar que se trata de una tabla virtual.

Recuerde escoger los parámetros correctos para los archivos CSV entregados:

Explore los datos entregados haciendo clic derecho sobre la tabla cargada vvehiculo y luego en la opción Query table o simplemente ingresando la instrucción SQL de SELECT

1.3. Realizar la selección de los datos de la zona de estudio.

Para seleccionar los dats correspondientes de vehículo en la localidad de Teusaquillo, simplemente realice un INNER JOIN entre la tabla accidente y vvehiculo, como sigue:

CREATE VIEW vvvehiculo AS 
SELECT a.* FROM vvehiculo a
INNER JOIN accidente b 
  ON a.formulario = b.formulario;

Refrescar las tablas y luego sobre la vista creada vvvehiculo, hacer clic derecho y guardar como xlsx:

Luego realice el cargue desde QGIS a la base de datos, usando las herramientas correspondiente de Rehacer campos con los tipos de datos correctos en la base de datos. Realizar el mismo proceso para las tablas de actor vial, causa y via.

Nota: Una vez se exporten las vistas en formatos xlsx es necesrio borrar las tablas virtuales y vistas, antes de importar la tabla en la base de datos.

2. Consultas de análisis espacial

Predicados espaciales

Modelo matriz DE-9IM (Dimensionally Extended 9 intersection Matrix).

• Documentación predicados espaciales: ESRI
• Documentación predicados espaciales PostGIS
• Lista de referencia Spatialite Referencia

2.1. Conocer el rango de los datos

Para el despliegue y análisis se recomienda los siguientes complementos:

• Data Plotly
• D3 Data Visualization
• Proveedor de R

Algunas de las consultas de selección:

• Crear y cargar la consulta de agrupación de gravedad de accidentes por año

SELECT ano_ocurrencia_acc, gravedad, count(*) As conteo
FROM accidente
WHERE ano_ocurrencia_acc > 2006
GROUP BY ano_ocurrencia_acc, gravedad

• Realizar el análisis de todos los accidentes con gravedad con muertes

SELECT * 
FROM actor_vial a
LEFT JOIN accidente b ON a.formulario = b.formulario
LEFT JOIN vehiculo c 
ON a.formulario =  c.formulario AND a.codigo_vehiculo = c.codigo_vehiculo
WHERE a.estado LIKE '%muerto%' OR a.muerte_posterior LIKE 's'
AND b.ano_ocurrencia_acc > 2006

En total se reportaron 207 accidentes con gravedad de muertes muertes, 2 no coincidentes

Podemos apoyarnos con algunos complementos para extraer algo de información a partir de las capas por ejemplo D3 Data Visualization para despliegues de reloj de tiempo

DataPlotly para el despliegue y análisis de los datos y gráficos

Total por año por gravedad de acciente con muertes

Total por condición de actor y clase vehículo

• Analisis por género

Para agregar una vista y sea reconocida en QGIS debe agregar las siguientes sentencias de SQL:

INSERT INTO gpkg_contents (table_name, identifier, data_type, srs_id) VALUES ( 'my_view', 'my_view', 'features', 4326)
INSERT INTO gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m) values ('my_view', 'my_geom', 'GEOMETRY', 4326, 0, 0)

Donde features tambien puede reemplazarse por attributes.

Referencias

• Cheatsheet 1
• SQL Joins