Curso Práctico de POSTGIS y POSTGRESQL. Bases de Datos Espaciales

En el mundo actual impulsado por la información, la capacidad de almacenar, gestionar y analizar datos con componentes espaciales se ha vuelto esencial para una amplia gama de aplicaciones, desde planificación urbana y gestión del medio ambiente hasta análisis de negocios y logística. En este contexto, dos tecnologías destacan de manera significativa: PostGIS y PostgreSQL.

PostgreSQL: La Base de Datos Robusta y Versátil

PostgreSQL es un sistema de gestión de bases de datos relacional de código abierto que se destaca por su capacidad para manejar datos estructurados y no estructurados de manera eficiente y confiable. Su arquitectura sólida y escalable lo convierte en una opción popular para una variedad de aplicaciones empresariales y de desarrollo.

Una característica distintiva de PostgreSQL es su extensibilidad. A través de la incorporación de extensiones, se puede ampliar su funcionalidad básica para adaptarse a diferentes necesidades. Y aquí es donde entra en juego PostGIS.

PostGIS: Enriqueciendo PostgreSQL con Capacidades Espaciales

PostGIS es una extensión espacial para PostgreSQL que agrega soporte para objetos geoespaciales y operaciones geoespaciales avanzadas. Esta combinación potente convierte a PostgreSQL en una base de datos geoespacial completa y permite almacenar y manipular datos que tienen una componente espacial. Puede manejar tipos de datos como puntos, líneas, polígonos y más, junto con funciones para cálculos de distancia, intersecciones, uniones espaciales y otras operaciones cruciales en el ámbito geoespacial.

La integración de PostGIS con PostgreSQL ofrece una sinergia única. Los usuarios pueden aprovechar las ventajas de una base de datos relacional confiable junto con la capacidad de realizar análisis espaciales y consultas geoespaciales de manera eficiente. Esto es especialmente valioso en aplicaciones que requieren la combinación de información geográfica y atributos tradicionales.

Conclusión

La combinación de PostGIS y PostgreSQL se ha establecido como una solución de referencia en el campo de la gestión de datos espaciales. Esta poderosa alianza proporciona a los desarrolladores, analistas y profesionales geoespaciales las herramientas necesarias para crear aplicaciones innovadoras y análisis precisos. Ya sea en el ámbito de la planificación urbana, la navegación, el análisis de ubicación o cualquier otra aplicación que involucre datos espaciales, PostGIS y PostgreSQL ofrecen una base sólida para la gestión y exploración efectiva de la dimensión espacial de la información.

• Conocer qué son las bases de datos y los datos espaciales, así como aquellos aspectos necesarios para entender su gestión.
• Aprender a diseñar una Base de Datos para que sea gestionada de un modo eficiente por los gestores.
• Instalar la base de datos PostgreSQL y su extensión de análisis espacial PostGIS.
• Aprender el manejo de PostgreSQL mediante la consola de comandos y la interfaz gráfica pgAdmin.
• Conocer el lenguaje SQL y practicar sus principales órdenes a la hora de consultar, seleccionar y manipular las tablas de una base de datos.

1. eoría de bases de datos
   1. Bases de datos
   2. Definiciones. Ventajas del uso de una base de datos
   3. Modelos de bases de datos
   4. Base de datos jerárquica. Base de datos en red. Base de datos relacional. Base de datos orientada a objetos.
   5. Bases de datos relacionales
   6. Terminología de las bases de datos relaciones. Características de las tablas relacionales. Integridad.
   7. Sistemas de Gestión de Bases de Datos (SGBD).
   8. Funciones que debe cumplir SGBD. Arquitectura de los SGBD.
   9. Diseño de una Base de Datos
   10. Diseño lógico. Modelo Entidad – Relación. Diseño lógico. Normalización. Diseño físico.
   11. Ejercicios tutorizados: E01. Creación de una base de datos usando la línea de comandos. E02. Diseño lógico y normalización. E03. Creación de modelos E-R. E04. Creación de un modelo E-R e importación a la base de datos en PostgreSQL.
   12. Prácticas de evaluación tutorizadas: P01. Cuestionario: Teoría de Bases de Datos. P02. Diseño del Modelo Entidad – Relación de un Parque Natural.
2. PostgreSQL. Lenguaje SQL
   1. Lenguaje SQL
   2. Definición. PostgreSQL.
   3. Tipos de datos en PostgreSQL.
   4. Clientes de PostgreSQL: psql y pgAdmin
   5. Psql. PgAdmin 4.
   6. Creación y manipulación de tablas
   7. Creación de una tabla. Modificación de una tabla. Borrado de una tabla.
   8. Dominios
   9. Creación de un dominio. Modificación de un dominio. Borrado de un dominio.
   10. Restricciones
   11. Restricción de valor no nulo. Restricción de unicidad. Restricción de clave primaria. Restricción general (CHECK). Restricción de clave foránea.
   12. Consultas y manipulación de datos.
   13. Inserción de registros. Borrado de registros. Actualización de registros. Consultas elementales sobre una tabla.
   14. Secuencias
   15. Creación de una secuencia. Alteración de una secuencia. Borrado de una secuencia.
   16. Funciones
   17. Funciones agregadas. Funciones de valor.
   18. Ejercicios tutorizados: E01. Creación de tablas y restricciones en PostgreSQL. Inserción de registros en las tablas. E02. Consultas y manipulación de registros.
   19. Prácticas de evaluación tutorizadas: P03. Creación de una base de datos catastral
3. PostgreSQL. Consultas avanzadas de SQL
   1. Subconsultas
   2. Con el predicado IN. Con los predicados ALL, ANY y SOME. Con el predicado EXISTS. Subconsulta como expresión.
   3. Consultas entre varias tablas
   4. Operadores conjuntistas: Union, Except e Intersect. Concatenación cruzada (Cross Join). Concatenación interna (Inner Join). Concatenación externa (Righ Join, Left Join, Full Join). Concatenación de más de dos tablas. Cláusula WITH.
   5. Consulta y manipulación de datos avanzada
   6. Creación de una tabla nueva. Inserción de registros en una tabla ya existente.
   7. Vistas y vistas materializadas
   8. Vistas. Vistas materializadas.
   9. Índices.
   10. Ejercicios tutorizados: E01. Subconsultas. E02. Consultas entre varias tablas. E03. Trabajo con una vista.
   11. Prácticas de evaluación tutorizadas: P04. Consulta de datos catastrales.
4. PL/PGSQL: Programación de proyectos almacenados y disparadores
   1. Definición de proceso almacenado
   2. El lenguaje PL / pgSQL. Estructura de PL / pgSQL. Sintaxis de un procedimiento almacenado en PostgreSQL. Declaración de parámetros. Declaración y asignación de variables. Condicionales. Case. Iteraciones. Retorno de resultados. Uso de sentencias SQL.
   3. Disparadores
   4. Funciones disparadoras. Creación de un disparador
   5. Ejercicios tutorizados: E01. Creación de funciones y disparadores.
   6. Prácticas de evaluación tutorizadas: P05. Desarrollo de Procesos Almacenados y Disparadores en una Base de Datos Catastral
5. Administración de Bases de Datos
   1. Administración de usuarios y permisos en PostgreSQL
   2. Creación, alteración y eliminación de roles de usuario. Grupos de roles. Uso de pgAdmin 4.
   3. Copias de seguridad y migración de una Base de Datos Espacial
   4. Backups mediante volcado.. Backups de todas las bases de datos del servidor.. Uso de pgAdmin 4.. Migración de una base de datos espacial.
   5. Conexión entre Bases de Datos
   6. Extensión dblink. Extensión Foreign Data Wrapper (FDW).
   7. Ejercicios tutorizados: E01. Creación de usuarios y roles. E02. Creación y restauración de copias de seguridad. E03. Conexión entre bases de datos. E04. Conexión a una base de datos externa mediante FDW.
   8. Prácticas de evaluación tutorizadas: P06. Cuestionario. Administración de Bases de Datos
6. Introducción a los SIG y las Bases de Datos Espaciales
   1. Introducción a los SIG
   2. La definición de SIG. ¿Para qué sirven los Sistemas de Información Geográfica?. Los componentes básicos de un SIG. Principios generales de los SIG.
   3. Bases de datos espaciales
   4. ¿Qué son las Bases de Datos Espaciales?. Evolución del uso de las Bases de Datos en los SIG.
   5. Fundamentos de Geodesia y Cartografía. Códigos EPSG
   6. Conceptos básicos de Geodesia. Sistemas de coordenadas. El código EPSG.
   7. Prácticas de evaluación tutorizadas: P07. Cuestionario: Introducción a los SIG y a las bases de datos espaciales
7. Fundamentos de PostGIS y Bases de Datos Espaciales. Integración con QGIS
   1. Creación de una base de datos espacial
   2. PostGIS. Crear Bases de Datos Espaciales. Creación de una tabla espacial. Metadatos de los atributos de geometría.
   3. Gestión de geometría en PostGIS
   4. Definición de los formatos WKT y WKB. Geometrías soportadas por PostGIS. Funciones básicas para el manejo de geometría. Diferencias entre el tipo geometry y el tipo geography.
   5. Importación y exportación de shapefile a PostGIS
   6. Mediante PSQL. Mediante el complemento “PostGIS Shapefile and DBF Loader Exporter”.
   7. Integración de QGIS y PostGIS para Análisis Espacial.
   8. Conexión a la base de datos desde QGIS. Importación con el complemento DB Manager de QGIS: shapefile y geojson. Importación mediante el comando OGR2OGR: geopackage y KML. Uso del complemento DB Manager de QGIS. Edición de capas desde QGIS. Almacenamiento de simbología de QGIS en PostGIS
   9. Análisis de Relaciones Espaciales y el Estándar OGC DE-9IM
   10. Definición de Interior, Contorno y Exterior de una geometría.. Matriz DE-9IM. Predicados espaciales. Programa JTS Builder.
   11. Mejora del rendimiento de las consultas con índices espaciales
   12. Cómo funcionan los Índices Espaciales. Creación y utilización de Índices Espaciales.
   13. Ejercicios tutorizados: E01. Habilitar la extensión PostGIS en nuestra base de datos. Creación de la columna Geometría. E02. Carga de archivos shapefile en PostGIS. E03. Primera consulta espacial. Visualización en QGIS. E04. Creación y carga de datos espaciales desde QGIS. E05. Relaciones espaciales con JTS Builder. E06. Índices espaciales.
   14. Prácticas de evaluación tutorizadas: P08. Carga de los datos cartográficos.
8. PostGIS. Operaciones de análisis espacial
   1. Descripción de los operadores espaciales
   2. Tipos de geometrías devueltas. Homogeneización de las geometrías devueltas.
   3. Superposición: Intersección, borrado, superposición, identidad y actualización
   4. Intersección. Borrado. Superposición. Identidad. Actualización.
   5. Extracción: Recorte y selección
   6. Recorte. Selección.
   7. Proximidad: Áreas de influencia (buffer) y selección según la distancia
   8. Área de influencia. Selección según distancia.
   9. Concatenación espacial y adición
   10. Concatenación espacial. Adición.
   11. Generalización: Disolución y simplificación de geometrías
   12. Disolución. Simplificación de geometrías
   13. Transformación entre sistemas de referencia
   14. Corrección de un sistema de referencia mal asignado.
   15. Conversiones geométricas
   16. Conversiones entre multigeometrías y geometrías simples. Conversión entre tipos de geometrías.
   17. Ejercicios tutorizados: E01. Operadores espaciales con JTS Builder. E02. Operaciones de superposición. E03. Operación de recorte. E04. Operaciones de proximidad. E05. Operaciones de concatenación espacial y adición. E06. Operaciones de generalización. E07. Reproyección de una capa. E08. Corrección de un Sistema de Referencia mal definido. E09. Conversiones entre geometrías. E10 Análisis de ubicación de una nueva tienda.
   18. Prácticas de evaluación tutorizadas: P09. Obtención de incidencias catastrales.
9. PostGIS Ráster
   1. Extensión ráster.
   2. Tipo ráster: conceptos y propiedades.
   3. Importación de ficheros ráster
   4. Funciones ráster
   5. Funciones de acceso a las propiedades del ráster. Funciones sobre bandas ráster. Funciones estadísticas sobre bandas. Funciones de valores de celdas. Funciones de coordenadas. Funciones de vectorización y rasterización. Funciones de relación espacial.
   6. Ejercicios tutorizados: E01. Procesamiento de datos ráster en PostGIS.
   7. Prácticas de evaluación tutorizadas: P10. PostGIS Raster.
10. Ïntroducción a pgRouting
    1. ¿Qué es pgRouting?
    2. Conceptos básicos de enrutamiento. Red, grafo y topología. Estructura arco-nodo. Definición de coste. Grafos ponderados y no ponderados. Grafos directos e indirectos. Funciones de pgRouting
    3. Creación de una Base de Datos para el cálculo de rutas óptimas
    4. Importación de datos vectoriales a la base de datos. Establecimiento de la topología de redes. Importación de archivos shapefile. Importación de datos desde Open Street Map. Uso de OSM2PO
    5. Cálculo de caminos más cortos
    6. Definición de coste y sentido de la circulación. Función Dijkstra. Función A* (A Star). Turn Restricted Shortest Path
    7. Obtención de múltiples rutas. Algoritmo pgr_ksp
    8. Definición de rutas con puntos de interés ajenos a la red
    9. Definición de los puntos. Función pgr_findCloseEdges. Rutas a través de estos puntos de interés. Función pgr_withPoints
    10. Obtención de rutas entre varios puntos
    11. Traveling Salesman Problem. Recorrido entre puntos en un orden preestablecido: pgr_dijstraVia y pgr_withPointsVia
    12. Notificación de una red no topológica
    13. Detección de errores topológicos. Corrección de errores topológicos
    14. Ejercicios tutorizados: E01. Análisis de rutas óptimas en Alicante
    15. Prácticas de evaluación tutorizadas: P11. Planificación de rutas turísticas y de transporte urbano