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.
Índice
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)
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)
Let’s connect!
Date de alta en nuestra newsletter y te enviaremos GRATIS el ebook que te ayudará a impulsar tu perfil GIS:
Vitaminas MappingGIS
Tan solo una vez al mes recibirás las últimas novedades del sector GIS y de nuestros cursos
Tutora del curso de R y SIG. Grado en Ingeniería en tecnologías de la información y en Geomática y topografía. Máster en Ingeniería y geoinformación. Echa un vistazo a todos nuestros cursos de SIG online.
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.
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
Hola Marcelo,
La función que necesitas es dbWriteTable() del paquete RPostgres, que ejecuta varias instrucciones SQL que crean/sobreescriben una
tabla de PostgreSQL y la llenan con valores.
Puedes echar un vistazo a los argumentos que hay que utilizar en la documentación oficial (https://cran.r-project.org/web/packages/RPostgres/RPostgres.pdf).
Un saludo.