rpostgis is back – Adrián Cidre

1 History

The rpostgis package was born around 2016, and since then, more than 20 versions have been released to CRAN (the official R repository). However, the geospatial ecosystem in R has gone through important changes during the last years (check this issue), including the retirement of the packages {rgeos}, {rgdal}, and {maptools}. These packages as well and sp and raster are being replaced by the packages sf (for vectorial operation), terra (for both raster and vectorial data), and stars (datacubes).

These changes in the geospatial ecosystem affected many other packages depending on the retired packages, including rpostgis. The authors of the package noted that they lacked time to continue the maintenance of the package, and that’s where I offered myself to maintain the package. During the last weeks I have been working on the migration of the package to the new geospatial ecosystem, and on October \(15^{th}\) the package was published in CRAN.

2 Package updates

The package updates haven’t been published in the website of the package (rpostgis). This is because I am working in the migration of the package to my GitHub repository, and then I will publish the website again probably through GitHub pages or my website. However, the main new features are:

Functions pg* migrated to packages sf/terra. Therefore, the inputs/outputs of these functions will be sf objects, SpatVector and SpatRaster objects by default. However, to maintain compatibility with recent versions, a new argument returnclass have been added to the pg* family of functions.
pgGetRast and pgWriteRast include now a progress bar to inform the user about the estimated remaining time. These progress bars can be disabled through the new function argument progress = FALSE.
The extension postgis_raster can be installed using the function pgPostGIS() using the new argument raster = TRUE.
The function pgInsert has been deprecated in favour of the function pgWriteGeom to keep naming consistency with other functions of the package.

3 Package notes

As many changes have been committed, it is possible that some unexpected errors may arise. I am working to detect these unexpected errors and commit them to the development version. You can submit any issue you find in the GitHub repository. To install the package run the following code:

pak::pak("rpostgis")

Caution

Note that you need to have installed the package pak

4 Write/Read vectorial data

The package is used as an interface between R and PostgreSQL with a PostGIS extension. Actually, the PostGIS extension can be installed using the function rpostgis::pgPostGIS(). First of all, we need to connect to our PostgreSQL server using the RPostgres or the RPostgreSQL drivers. A basic connection with a local database could be achieved as follows:

conn <- RPostgres::dbConnect(
  drv      = RPostgres::Postgres(),
  host     = "localhost",
  dbname   = "my_database",
  port     = 5432,
  user     = "adrian_cidre",
  password = "awesomepassword"
)

We can then check if the connection was successful, and install the necessary extensions (remember also to load the package). If we just set the connection into the function, it will install PostGIS automatically but not other extensions:

library(rpostgis)
pgPostGIS(conn)

[1] TRUE

First, let’s create fictional data:

# Load necessary packages
library(sf)
library(tidyverse)

# Create fictional tibble
pts_tbl <- tibble(
  x = seq(-8.3, -7.3, length.out = 50),
  y = seq(42.4, 43, length.out = 50),
  z = rnorm(50)
)

# Convert to sf
pts_sf <- st_as_sf(pts_tbl, coords = c("x", "y"), crs = 4326)

So basically, I created a sf object with POINT geometry. Now let’s imagine this is awesome data we want to save in our PostGIS database. We can use the function pgWriteGeom(conn, name, data.obj). These are the three arguments that you always must specify:

pgWriteGeom(
  conn     = conn,
  name     = c("public", "awesome_points"),
  data.obj = pts_sf
)

[1] TRUE

We are exporting to our connection the data object pts_sf, to the public schema, and setting its name to awesome_points. The function returns TRUE when the transfer was successful. If we don’t trust it, we can use the next function to list all the vectorial data in our database:

pgListGeom(conn)

  schema_name     table_name geom_column geometry_type     type
1      public awesome_points        geom         POINT GEOMETRY

Excellent! Now let’s imagine that after some time we want to get back to this file. How can we get it back into R? So, that’s pretty easy! We can use the pgGetGeom(conn, name) as easy as this:

# Get the points
awesome_pts_sf <- pgGetGeom(
  conn = conn,
  name = "awesome_points"
)

# Check it
head(awesome_pts_sf)

Simple feature collection with 6 features and 1 field
Geometry type: POINT
Dimension:     XY
Bounding box:  xmin: -8.3 ymin: 42.4 xmax: -8.197959 ymax: 42.46122
Geodetic CRS:  +proj=longlat +datum=WGS84 +no_defs 
           z                       geom
1  0.8632266          POINT (-8.3 42.4)
2 -0.8488456 POINT (-8.279592 42.41224)
3 -0.7214244 POINT (-8.259184 42.42449)
4  0.2316108 POINT (-8.238776 42.43673)
5  0.0213917 POINT (-8.218367 42.44898)
6  2.2377554 POINT (-8.197959 42.46122)

We have our object back into R! You may have noticed that I used name = "awesome_points" instead of name = c("public", "awesome_points"). This is because when we do not specify the schema, by default, the function will use the public schema both for reading and writing.

5 Conclusion

So you have seen the basic and most typical use of the rpostgis package for vectorial data. In future posts, I will share an example reading/writing raster data.

If you liked the package and this tutorial, please feel free to share it with your colleagues.

Warning

Oh my God! And do not forget to close your connection!!!

dbDisconnect(conn)