Cómo integrar PostgreSQL – PostGIS en R

La combinación de PostgreSQL y R permite aprovechar el poder y la eficiencia de PostgreSQL y la gran funcionalidad analítica de R. La unión de ambos permite a los usuarios la realización de análisis espaciales muy potentes.

El objetivo de este post es mostrar cómo podemos integrar PostgreSQL y PostGIS en R a través de RStudio.

integracion PostgreSQL PostGIS en R

Configuración de PostgreSQL

El primer paso es asegurarnos de que tenemos todos los programas necesarios instalados. Además de R y RStudio, será necesario instalar PostgreSQL. Se puede obtener la última versión disponible aquí.

En este post, las versiones utilizadas son:

R

3.6.1

RStudio

1.2.5033

PostgreSQL

12

Para el tutorial, se ha creado mediante PgAdmin4 una base de datos PostgreSQL con la extensión PostGIS y se le han añadido una tabla denominada roads con datos espaciales.

createdb diana
psql diana
psql (12.1)
Type "help" for help.

diana=# create extension postgis
diana-#

Conexión a una base de datos PostgreSQL – PostGIS desde R

Una vez se esté configurado correctamente PostgreSQL, se puede empezar a trabajar desde RStudio. Para ello, se deben instalar una serie de paquetes: DBI, RPostgres y sf.

# 1.0 Instalación y carga de paquetes
install.packages("RPostgres")
install.packages("DBI")
install.packages("sf")
library(DBI)
library(RPostgres)
library(sf)
  • El paquete DBI proporciona funciones para el acceso a varios Sistemas de Gestión de Bases de Datos. Actualmente, da soporte para PostreSQL, MariaDB, SQLite, GoogleBigQuery y MySQL.
  • El paquete RPostgres introduce una interfaz compatible con DBI para gestionar bases de datos postgres y la extensión espacial PostGIS en R.
  • El paquete sf para la representación de objetos geográficos en R. Para más información pueden consultarse post anteriores.

Para poder establecer una conexión es necesario conocer seis parámetros:

  • Controlador: nombre del driver para la conexión.  Se puede obtener más información en la sección de Controladores.
  • Servidor: la ruta de red al servidor de la base de datos.
  • Base de datos: el nombre de la base de datos a la que se quiere establecer la conexión.
  • Usuario: el ID de red del usuario o la cuenta local del servidor.
  • Contraseña: la contraseña de la cuenta.
  • Puerto: generalmente se establece en 5432.
# 2.0 Parámetros de conexión a PostgreSQL
dvr <- RPostgres::Postgres()
db <- 'diana'  ##Nombre de la BBDD
host_db <- 'localhost'
db_port <- '5432' 
db_user <- 'usuario'  ##Tu usuario
db_password <- 'contraseña' ##Tu contraseña 

# 3.0 Conexión
con <- dbConnect(dvr, dbname = db, host=host_db, port=db_port,
                 user=db_user, password=db_password)  

Listar tablas de una Base de Datos en R

El paquete DBI incluye la función dbListTables(conexión) que permite listar todas las tablas que contiene la base de datos a la que se ha realizado la conexión:

# 4.0 Listado de tablas de la Base de Datos
dbListTables(con)

El resultado que se muestra en consola es:

> dbListTables(con)
[1] "geography_columns" "geometry_columns"  "spatial_ref_sys"   "roads"

Se mostrarán todas las tablas que contiene la base de datos en cuestión.

Con la función st_read del paquete sf, podemos «leer» y crear una variable con los datos de la tabla de la BD que deseemos en R, para realizar cualquier tipo de análisis:

# 5.0 Lectura de una tabla
roads <- st_read(con, layer = "roads")
print(roads)

Obteniendo por consola:

> roads <- st_read(con, layer = "roads")
> print(roads)
Simple feature collection with 6 features and 2 fields
geometry type:  LINESTRING
dimension:      XYZ
bbox:           xmin: 189141 ymin: 224148 xmax: 198231 ymax: 268322
epsg (SRID):    NA
proj4string:    NA
  road_id  road_name                     roads_geom
1       1    Jeff Rd LINESTRING Z (191232 243118...
2       2 Geordie Rd LINESTRING Z (189141 244158...
3       3    Paul St LINESTRING Z (192783 228138...
4       4 Graeme Ave LINESTRING Z (189412 252431...
5       5   Phil Tce LINESTRING Z (190131 224148...
6       6  Dave Cres LINESTRING Z (198231 263418...

Lanzar consultas desde RStudio

Existen una serie de funciones que nos permiten lanzar consultas directamente contra la base de datos y visualizar sus resultados:

  • dbSendQuery(conexión, consulta) para lanzar consultas.
  • dbFetch(resultado, n) para obtener los nelementos (filas) del conjunto de resultados.
  • dbClearResult(resultado) para limpiar los resultados obtenidos de una consulta.
# 6.0 Query --> Selección de todos los elementos de la tabla
res <- dbSendQuery(con, "SELECT road_id, road_name FROM roads where road_name LIKE \'Jeff Rd\'")
dbFetch(res)
dbClearResult(res)
> dbFetch(res)
  road_id road_name
1       1   Jeff Rd
> dbClearResult(res)
# 7.0 Query --> Borrado de un elemento de la tabla 
res <- dbSendQuery(con, "DELETE FROM roads WHERE id = 6") 
dbClearResult(res)

hacerqueris

Se puede lanzar cualquier consulta que se lanzaría directamente desde PgAdmin o desde otro gestor de base de datos.

Cerrar conexión con la base de datos

Para finalizar, es importante asegcerrar la conexión a la base de datos. Para ello, se emplea la función dbDisconnect(conexión).

# 8.0 Cerrar conexión dbDisconnect(con)

# 8.0 Cerrar conexión 
dbDisconnect(con)

Código completo

El código completo del tutorial es el siguiente:

## CONEXIÓN ENTRE POSTGRESL Y R
# 1.0 Instalación y carga de paquetes
installed.packages("DBI","RPostgres", "sf")
library(DBI)
library(RPostgres)
library(sf)

# 2.0 Parámetros de conexión a PostgreSQL
dvr <- RPostgres::Postgres()
db <- 'diana'  ##Nombre de la BBDD
host_db <- 'localhost'
db_port <- '5432' 
db_user <- 'postgres'  ##Tu usuario
db_password <- 'postgres' ##Tu contraseña 

# 3.0 Conexión
con <- dbConnect(dvr, dbname = db, host=host_db, port=db_port,
                 user=db_user, password=db_password)  

# 4.0 Listado de tablas de la Base de Datos
dbListTables(con) 

# 5.0 Lectura de una tabla
roads <- st_read(con, layer = "roads")
print(roads)

# 6.0 Query --> Seleccion de todos los elementos de la tabla
res <- dbSendQuery(con, "SELECT road_id, road_name FROM roads where road_name LIKE \'Jeff Rd\'")
dbFetch(res)
dbClearResult(res)

# 7.0 Query --> Borrado de un elemento de la tabla
res <- dbSendQuery(con, "DELETE FROM roads WHERE road_id = 6")
dbClearResult(res)

# 8.0 Cerrar conexión
dbDisconnect(con)

 

3 comentarios en «Cómo integrar PostgreSQL – PostGIS en R»

  1. Buenos días,
    gracias por el post. ¿Es posible hacer una copia en local de mi base de datos desde R?
    Lo he intentado con dbSendUpdate(con, «CREATE DATABASE mydatabasecopy WITH owner=admin template=mydatabase;») pero no me deja porque me dice que hay 1 otra sesión utilizando la base de datos, pero si hago dbDisconnect(con) tampoco me deja porque me dice que no estoy conectada.
    Muchas gracias de antemano.

  2. Esta genial la muestra. Mi duda es como importar un dataframe que trabajas en R a Postgres ¿Se puede? que quede dentro de tu base de datos de Postgres

Los comentarios están cerrados.