Capítulo 5 Almacenar Información Geográfica en SpatiaLite, GeoPackage y PostGIS

En el capítulo 3, hemos obtenido información geográfica relevante para nuestro municipio. En el capítulo 4, recortamos los datos ráster y vectoriales obtenidos seleccionando exclusivamente el área que se ha considerado adecuada para nuestro municipio.

En este capítulo, como punto de partida, vamos a trabajar con datos obtenidos en el capítulo 4, tenemos los datos vectoriales en archivos Shapefile y los datos ráster en formato GeoTIFF. Todos los datos vectoriales usan el mismo SRC. Es imprescindible tener disponibles los datos obtenidos en el ejercicio 4.1 para poder continuar.

Almacenaremos una selección de estos datos en tres sistemas de gestión de bases de datos (SGBD): SpatiaLite, GeoPackage y PostGIS, basándonos en la funcionalidad que ofrecen QGIS y R. De esta forma, en actividades posteriores, podremos comparar el funcionamiento de estos tres sistemas para realizar distintas operaciones espaciales.

Los datos obtenidos en este capítulo deberán conservarse y estar disponibles para ser utilizados en las actividades que desarrollaremos en los capítulos siguientes.

Objetivos del capítulo

  • El objetivo principal es almacenar los datos obtenidos en varios SGBD espaciales, en concreto:
    • SpatiaLite,
    • GeoPackage y
    • PostGIS.
  • Estudiar cómo realizar las operaciones de importación de datos en cada caso.
  • Conocer las posibilidades que ofrecen cada uno de estos sistemas para importar los distintos tipos de datos.
  • Estudiar cómo se integran con QGIS y R.

En el resto de capítulo, en primer lugar, se listan las capas de trabajo que vamos a usar; a continuación, se presentan los tres SGBD donde almacenaremos los datos y los ejercicios a realizar: en el caso de PostGIS se explica cómo crear y configurar una base de datos (BD) y cómo salvarla; en el resto de apartados se explica cómo realizar las operaciones para almacenar los datos en las BD desde QGIS y desde R: solo se ha de realizar con uno de ellos; por último, se muestra cómo acceder a las BD desde el Administrador de BBDD de QGIS.

Las actividades propuestas para PostGIS se basan principalmente en Marquez (2015) y Obe and Hsu (2015).

5.1 Capas de trabajo

Los datos obtenidos en el capítulo 4 con los que vamos a trabajar son los siguientes:

  1. Capas vectoriales temáticas (4 capas \(\times\) 2).
  2. MDT (1 banda \(\times\) 2).
  3. Capas de CartoCiudad (2 capas \(\times\) 2).
  4. Uso/ocupación del suelo de CORINE Land Cover (2 capas \(\times\) 2).
  5. Bandas de satélite (11 a 13 bandas).

Las bandas de satélite solo se han recortado mediante el mínimo rectángulo que contiene al término municipal. Para el resto de capas tenemos dos versiones, obtenidas al recortar la capa original mediante:

  • el mínimo rectángulo que contiene al término municipal,
  • el polígono de la capa del término municipal.

5.2 BD SpatiaLite, GeoPackage y PostGIS

5.2.1 SpatiaLite

SpatiaLite es un SGBD basado en SQLite con funciones espaciales añadidas48. Se puede trabajar desde una interfaz gráfica llamada SpatiaLite-gui pero usaremos exclusivamente QGIS y R. Las capas vectoriales se pueden almacenar directamente en SpatiaLite. Hay propuestas para almacenar capas ráster utilizando SpatiaLite49 pero, en este momento, no se pueden utilizar fácilmente en nuestro contexto, por lo que no las vamos a considerar. Nos limitaremos a almacenar las capas vectoriales.

Las BD SpatiaLite se pueden crear tanto desde QGIS como desde R.

Ejercicio 5.1 Para cada punto del apartado 5.1, almacena todas las capas vectoriales en una BD SpatiaLite cuyo nombre tenga como prefijo el nombre del municipio y tu usuario de correo de la UGR (p.e., en mi caso lanjaron-jsamos-). El nombre de las capas en la BD deberá coincidir con el nombre de los archivos en formato shapefile de los que proceden.

Para documentar la realización del ejercicio, para cada BD (habrá 3), incluye un apartado en el documento y captura una pantalla donde se muestre su contenido (la lista de capas y la previsualización, en el Administrador de BBDD de QGIS, de una de ellas, tal y como se explica en el apartado 5.5, independientemente de que la actividad se realice con QGIS o R).

5.2.2 GeoPackage

Ya hemos usado el formato GeoPackage en el apartado 3.2.1, para almacenar las capas del término municipal y mínimo rectángulo que lo contiene. Este formato está basado en una BD SQLite y permite almacenar tanto capas vectoriales como ráster50. Para el caso de ráster, un GeoPackage puede contener más de una banda dependiendo del tipo de datos del ráster. No es solo un formato de almacenamiento de información geográfica, sino que también podemos usar las funcionalidades de la BD.

Las BD GeoPackage se pueden crear tanto desde QGIS como desde R.

Ejercicio 5.2 Para cada punto del apartado 5.1, almacena todas las capas en una BD GeoPackage cuyo nombre tenga como prefijo el nombre del municipio y tu usuario de correo de la UGR (p.e., en mi caso lanjaron-jsamos-). El nombre de las capas en la BD deberá coincidir con el nombre de los archivos de los que proceden.

Para documentar la realización del ejercicio, para cada BD (habrá 5), incluye un apartado en el documento y captura una pantalla donde se muestre su contenido (la lista de capas y la previsualización, en el Administrador de BBDD de QGIS, de una de ellas, tal y como se explica en el apartado 5.5, independientemente de que la actividad se realice con QGIS o R).

5.2.3 PostGIS

PostgreSQL es un sistema de gestión de SGBD relacional de código abierto. PostGIS es una extensión de PostgreSQL que permite definir datos y consultas espaciales51. Mediante PostGIS podemos almacenar directamente capas vectoriales en PostgreSQL, también permite almacenar datos ráster pero transformándolos previamente mediante una utilidad (raster2pgsql) para obtener un archivo SQL. Mediante otra extensión para PostGIS, llamada PostGIS Raster, también se pueden almacenar directamente capas ráster.

Antes de trabajar con QGIS o R, tenemos que crear una BD PostGIS que soporte tanto datos vectoriales como datos ráster. A continuación se explica cómo hacerlo, también cómo salvar la BD si, por ejemplo, queremos guardar nuestro trabajo para continuar en otro ordenador.

5.2.3.1 Instalar la extensión PostGIS Raster

La extensión PostGIS Raster se incluye en las nuevas versiones de la extensión PostGIS. Si hemos realizado una instalación nueva y hemos tomado la última versión de PostGIS, ya tendremos PostGIS Raster. La versión de PostGIS instalada en la imagen de las aulas de prácticas que usamos en la máquina virtual no incluye esta extensión. En cursos anteriores habíamos usado la utilidad raster2pgsql para obtener un archivo SQL a partir de un ráster y así poder insertarlo en la BD. Este curso vamos a actualizar PostGIS para poder usar PostGIS Raster, para ello necesitamos realizar los pasos siguientes:

  1. Desinstalamos la versión de PostGIS instalada: ejecutamos el archivo C:/Program Files/PostgreSQL/9.6/uninstall-postgis-bundle-pg96x64-2.4.4-1.exe

  2. De http://download.osgeo.org/postgis/windows/pg96/ descargamos y ejecutamos postgis-bundle-pg96x64-setup-3.1.4-1.exe para instalar la última versión de PostGIS.

5.2.3.2 Crear una BD PostgreSQL con las extensiones PostGIS y PostGIS Raster

Para almacenar la información en PostGIS, en primer lugar, deberemos crear una BD y un esquema desde el administrador de PostgreSQL, pgAdmin 4.

Conectarse a PostgreSQL.

Figura 5.1: Conectarse a PostgreSQL.

Pulsando sobre la flecha a la izquierda de Servers, en la columna de la izquierda de la figura 5.1, se muestran los datos de los servidores disponibles y, si seguimos desplegando de la misma forma, las BD de cada servidor. Al conectarse a un servidor, solicita el usuario y la contraseña: en la instalación que estamos utilizando, los suministra el navegador. El usuario que usamos es el administrador, postgres, y la contraseña también es postgres.

Crear la BD.

Figura 5.2: Crear la BD.

Para crear una nueva BD, pulsamos sobre Create > Database… del menú contextual del apartado Databases (figura 5.2).

En la ventana que se abre, indicamos el nombre de la BD, se muestra el propietario de la BD, en este caso es el administrador cuyo usuario es postgres (no tenemos que cambiarlo) y pulsamos sobre el botón Save. Con esta operación tenemos una BD PostgreSQL.

Crear una extensión a la BD.

Figura 5.3: Crear una extensión a la BD.

Para tener una BD con soporte para información geográfica, debemos añadir una extensión PostGIS a la BD PostgreSQL que acabamos de crear. Esto lo hacemos desde el apartado Extensions, pulsando sobre Create > Extension… en el menú contextual (figura 5.4).

Añadir la extensión a la BD.

Figura 5.4: Añadir la extensión a la BD.

En la ventana de creación de extensiones, seleccionamos la extensión postgis y pulsamos sobre el botón Save (figura 5.4).

Para que la BD permita trabajar con datos ráster directamente, debemos añadir también la extensión postgis_raster (de la misma forma que acabamos de añadir la extensión postgis). Es decir, la BD ha de tener las extensiones postgis y postgis_raster, además del resto de extensiones que tenga.

5.2.3.3 Guardar en un archivo y restaurar una BD PostgreSQL

Si trabajamos con PostgreSQL en un ordenador y queremos llevar nuestro trabajo a otro (por ejemplo, si trabajamos en un ordenador del aula de prácticas), debemos hacer un backup de la BD52 para guardar nuestro trabajo.

El backup se puede realizar mediante el comando siguiente:

pg_dump -U postgres -W -F t nombre_de_la_bd > carpeta/nombre_del_archivo.tar

Este comando hace un backup de la BD nombre_de_la_bd cuyo usuario es postgres en el archivo TAR indicado.

Posteriormente, podemos recuperar la BD mediante el comando siguiente:

pg_restore -U postgres -c -d nombre_de_la_bd carpeta/nombre_del_archivo.tar

Ejercicio 5.3 Almacena todas las capas del apartado 5.1 en una BD PostGIS cuyo nombre sea el nombre del municipio y tu usuario de correo de la UGR (p.e., en mi caso lanjaron-jsamos). El nombre de las capas en la BD deberá coincidir con el nombre de los archivos de los que proceden.

Para documentar la realización del ejercicio, incluye un apartado en el documento y captura una pantalla donde se muestre su contenido (la lista de capas y la previsualización, en el Administrador de BBDD de QGIS, de una capa vectorial y otra capa ráster, tal y como se explica en el apartado 5.5, independientemente de que la actividad se realice con QGIS o R).

5.3 Almacenar información desde R

SpatiaLite y GeoPackage se presentan conjuntamente en un apartado porque la manera de trabajar con ellos es idéntica, excepto que para datos ráster solo trabajaremos con GeoPackage.

5.3.1 SpatiaLite y GeoPackage

5.3.1.1 Almacenar una capa vectorial

Para almacenar una capa vectorial en SpatiaLite o GeoPackage desde R usaremos la función st_write del paquete sf, tal y como se ha hecho en los apartados 3.2.1 (para GeoPackage) y 4.2.3.1 (para Shapefile). El formato se indica en la extensión del archivo que almacena las capas, este archivo implementa la BD: ha de tener la extensión .sqlite para SpatiaLite y .gpkg para GeoPackage.

st_write(obj = lanjaron_jsamos,
         dsn = "lanjaron-jsamos.sqlite",
         layer = "lanjaron-jsamos")

st_write(obj = lanjaron_jsamos,
         dsn = "lanjaron-jsamos.gpkg",
         layer = "lanjaron-jsamos")

5.3.1.2 Almacenar una capa ráster

Hasta ahora, para leer y almacenar capas ráster hemos usado el paquete raster. Este mismo paquete se puede usar para almacenar datos en formato GeoPackage pero, en los ejemplos que he estado haciendo, he encontrado la limitación de que en algunos casos solo permitía almacenar una banda ráster por archivo.

Utilizando el paquete stars para leer y guardar las bandas ráster, se pueden almacenar varias bandas en la misma BD implementada por un archivo GeoPackage.

library(stars)

l <- read_stars("landsat8/lanjaron-jsamos-LC08_L1TP_200034_20210716_20210721_01_T1_B1.tif")

t <- "lanjaron-jsamos-LC08_L1TP_200034_20210716_20210721_01_T1_B1"

write_stars(l, "lanjaron-jsamos-satelite-landsat8.gpkg", 
            driver = "GPKG",
            options = c(paste("RASTER_TABLE=", t, sep=""),
                        "APPEND_SUBDATASET=YES"))

5.3.2 PostGIS

A diferencia de SpatiaLite y GeoPackage, las BD PostGIS no están implementadas por un solo archivo. Por este motivo, en primer lugar debemos definir una conexión con la BD en el el SGBD PostGIS. A partir de ahí, se trata de forma similar a los otros SGBD que hemos visto.

5.3.2.1 Definir la conexión con la BD

Para definir la conexión usamos la función dbConnect del paquete DBI, también incluida en el paquete RPostgres que es el que usamos. Indicamos el nombre de la BD (en mi caso lanjaron_jsamos, que tenemos que haberla creado previamente), y los datos de nuestra instalación (en la instalación de la imagen de prácticas el usuario y la contraseña coinciden y son postgres).

Si queremos comprobar que se ha establecido bien la conexión, mediante la función dbListTables obtenemos una lista de las tablas de la BD.

library(RPostgres)

con <- dbConnect(RPostgres::Postgres(),
                 dbname = 'lanjaron_jsamos',
                 host = 'localhost',
                 port = '5432',
                 user = 'postgres',
                 password = 'postgres')

dbListTables(con)

5.3.2.2 Almacenar una capa vectorial

Para almacenar una capa vectorial en PostGIS desde R también usaremos la función st_write del paquete sf, tal y como se ha hecho para los otros SGBD. La diferencia es que ahora indicamos la conexión en lugar del archivo donde se implementa la BD.

st_write(obj = lanjaron_jsamos,
         dsn = con,
         layer = "lanjaron-jsamos")

5.3.2.3 Almacenar una capa ráster

Para almacenar las capas ráster, usaremos las funciones del paquete rpostgis para escribir en la BD y las funciones del paquete raster para leer las capas.

En la función pgWriteRast indicamos mediante un vector el esquema de la BD donde se almacena (las almacenaremos en el esquema public) y la tabla. Esta función no permite usar el carácter ‘-’ en el nombre de las tablas, por este motivo lo que podemos hacer es sustituirlo por ’_’, como se hace en el ejemplo siguiente mediante la función str_replace_all (especialmente útil si tenemos el nombre en una variable cuyos valores van cambiando).

library(raster)
library(rpostgis)

r <- brick("landsat8/lanjaron-jsamos-LC08_L1TP_200034_20210716_20210721_01_T1_B1.tif")

t <- "lanjaron-jsamos-LC08_L1TP_200034_20210716_20210721_01_T1_B1"
t <- stringr::str_replace_all(t, "-", "_")

pgWriteRast(con, c("public", t), raster = r)

5.3.2.4 Finalizar la conexión con la BD

Una vez hemos acabado de operar con la BD, no desconectamos de ella mediante la función dbDisconnect.

dbDisconnect(con)

5.4 Almacenar información desde QGIS

Podemos almacenar información en SpatiaLite y GeoPackage tratándolos como un formato de datos más o bien tratándolos como BD. En el caso de PostGIS, solo se puede tratar como BD.

5.4.1 Tratamiento como formato de datos

Guardar capa vectorial como…

Figura 5.5: Guardar capa vectorial como…

SpatiaLite y GeoPackage se pueden considerar en QGIS como un formato de almacenamiento de datos más. Podemos crear una BD y almacenar en ella varias capas vectoriales accediendo a la operación Guardar capa vectorial como…, desde el menú contextual de la capa o desde el menú Capa, y seleccionando el formato adecuado en cada caso (figura 5.5).

Para GeoPackage también podemos almacenar varias capas ráster en una BD directamente desde la operación Guardar capa ráster como…, también accesible desde el menú contextual de la capa o desde el menú Capa.

5.4.2 Tratamiento como BD

Podemos acceder a las BD de distinto tipo desde el Navegador de QGIS. En el menú contextual de cada tipo de BD podemos acceder a las opciones disponibles.

5.4.2.1 Conexión a la BD o creación de la BD

Para SpatiaLite y GeoPackage podemos conectarnos a una BD previamente existente o bien crear una nueva. En el caso de PostGIS, solo podemos conectarnos a BD. Una vez definamos una conexión a una BD podremos acceder a sus capas de la misma forma que si esta estuvieran almacenadas en archivos de tipo vectorial o ráster para trabajar con ellas en nuestro proyecto.

5.4.2.1.1 SpatiaLite
Opciones de las BD SpatiaLite en el Navegador.

Figura 5.6: Opciones de las BD SpatiaLite en el Navegador.

En el caso de SpatiaLite podemos conectarnos a una BD o bien crear una nueva (figura 5.6).

Para conectarse a una BD solo es necesario seleccionar el archivo que implementa la BD. De igual forma, para crear una nueva BD, es suficiente con indicar el nombre y ubicación del archivo que la implementará.

5.4.2.1.2 GeoPackage
Opciones de las BD SpatiaLite en el Navegador.

Figura 5.7: Opciones de las BD SpatiaLite en el Navegador.

En el caso de GeoPackage, funciona exactamente igual que SpatiaLite para conectarse a una BD. Sin embargo, para crear una nueva BD, es necesario definir una tabla para almacenar una capa, se ha de definir al menos el nombre y tipo de la capa, también se pueden definir campos (figura 5.7).

5.4.2.1.3 PostGIS
Opciones de las BD PostGIS en el Navegador.

Figura 5.8: Opciones de las BD PostGIS en el Navegador.

En el caso de PostGIS solo podemos realizar operaciones sobre una BD previamente existente (figura 5.8). En particular, para conectarnos con una BD seleccionamos la opción Conexión nueva…

Crear una nueva conexión a PostGIS.

Figura 5.9: Crear una nueva conexión a PostGIS.

En la ventana que se abre definimos los datos de la nueva conexión (figura 5.9). El nombre de la conexión puede ser cualquiera, no tiene que coincidir con el de la BD. Es recomendable pulsar sobre el botón Probar conexión, nos pedirá el usuario y contraseña (en la instalación de la máquina virtual ambos son postgres) y podremos comprobar si se puede conectar a la BD.

5.4.2.2 Añadir capas

5.4.2.2.1 Añadir capas vectoriales
Administrador de BBDD.

Figura 5.10: Administrador de BBDD.

Podemos añadir capas vectoriales desde el Administrador de BBDD. Accedemos a él pulsando sobre Base de datos > Administrador de bases de datos… Además de poder definir nuevas conexiones a BD, podemos importar datos vectoriales pulsando sobre Importar capa/archivo… en la barra de herramientas (figura 5.10).

5.4.2.2.2 Añadir capas ráster

Como BD podemos añadir capas ráster para PostGIS. Una posibilidad es usar la utilidad raster2pgsql para obtener un archivo SQL a partir de un ráster y así poder insertarlo en la BD. Otra es utilizar el complemento PostGIS Raster Import que, una vez instalado, es accesible desde la opción Base de datos > PostGIS Raster Import.

PostGIS Raster Import.

Figura 5.11: PostGIS Raster Import.

En la ventana que se abre (figura 5.11), seleccionamos la capa ráster a importar, la conexión de la BD (ha de estar definida previamente), el esquema de la BD (usamos el esquema public) y la tabla donde se almacenará la capa (si no existe la creará).

Es importante desmarcar la opción Create Raster Overviews porque, en caso contrario, además del ráster, añade varios más obtenidos a partir de él a distintas resoluciones.

5.5 Acceder y mostrar información de una BD desde el Administrador de BBDD de QGIS

Podemos usar el Administrador de BBDD de QGIS para acceder al contenido de las capas y mostrar una vista previa de estas sin necesidad de añadirlas a un proyecto.

Para poder acceder a una BD, debemos definir previamente una conexión a esta, como se describe en el apartado 5.4.2.1.

Accedemos al Administrador de BBDD pulsando sobre Base de datos > Administrador de bases de datos…

Vista previa en el Administrador de BBDD.

Figura 5.12: Vista previa en el Administrador de BBDD.

En el apartado Proveedores, en el lateral izquierdo de la ventana que se abre (figura 5.12), seleccionamos la BD desplegando el tipo correspondiente. En esa misma zona, para la BD, seleccionamos la tabla que queremos ver. En la hoja Vista previa, en el lateral derecho, se muestra la capa.

Bibliografía

Marquez, Angel. 2015. PostGIS Essentials. Packt Publishing.
Obe, Regina O., and Leo S. Hsu. 2015. PostGIS in Action (Second Edition). Manning.