---
langs: es
tags: QGIS
title: Análisis espacial en QGIS - Parte 1 SQL y calculadora de expresiones
description: Análisis espacial en QGIS - Parte 1 SQL y calculadora de expresiones
---
# 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](http://sig.simur.gov.co/arcgis/rest/services/Accidentalidad/WSAcidentalidad_Publico/FeatureServer) 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](http://sig.simur.gov.co/arcgis/rest/services/Accidentalidad/WSAcidentalidad_Publico/FeatureServer/relationships):
```mermaid
erDiagram
ACCIDENTE {
string formulario
int codigo_accidente
datetime fecha_ocurrencia
string gravedad
string clase_acc
double longitud
double latitud
string localidad
}
VEHICULO {
string formulario
int codigo_vehiculo
string clase
string servicio
string modalidad
string enfuga
string codigo
}
ACTOR_VIAL {
string formulario
int codigo_accidente
int codigo_accidentado
int codigo_vehiculo
int codigo_victima
string genero
string estado
string condicion
string muerte_posterior
date fecha_posterior_muerte
int edad
}
CAUSA {
string formulario
int codigo_accidente
int codigo_vehiculo
int codigo_causa
string nombre
string tipo
string tipo_causa
string codigo
}
VIA {
string formulario
int codigo_accidente
string estado
string material
string condiciones
string iluminacion
string codigo
}
ACCIDENTE ||--|{ VEHICULO : contiene
VEHICULO ||--|{ ACTOR_VIAL : involucrado
ACCIDENTE ||--o{ CAUSA : contiene
ACCIDENTE ||--o{ VIA : contiene
VEHICULO ||--o{ CAUSA : contiene
```
## 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:
```sql=
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](https://desktop.arcgis.com/es/arcmap/latest/manage-data/using-sql-with-gdbs/relational-functions-for-st-geometry.htm)
* Documentación predicados espaciales [PostGIS](http://postgis.net/workshops/postgis-intro/spatial_relationships.html)
* Lista de referencia Spatialite [Referencia](http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html)
### 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
```sql=
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
```sql=
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:
```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](https://pbs.twimg.com/media/E_uirTGWEAIU2Gr?format=jpg&name=large)
* [SQL Joins](https://pbs.twimg.com/media/EXF28CgWkAABpiU?format=jpg&name=900x900)
Sign in with Wallet
Connect another wallet