PostgreSQL/PostGIS is often my top pick when working with geodata in databases and I use it frequently with FME and QGIS, but until recently, not so much with R. How do you read and write geodata in PostgreSQL/PostGIS with R? It turns out, it´s not hard at all, and the script below will cover the basics.

This script will:

• Read a geometry table from PostGIS
• Process the data
• Validate the data
• Write the result to a new table in PostGIS

Code:

# Load libraries
library(dplyr)
library(mapview)
library(RPostgres)
library(sf)


# Create connection to PostgreSQL/PostGIS
con_pg = dbConnect(RPostgres::Postgres()
                 , host='localhost'
                 , port='5432'
                 , dbname='geodata'
                 , user='user'
                 , password='password')


# Create SQL query
qry_skane = "SELECT * FROM public.ak_riks WHERE lan_kod = '12'"


# Read table from PostgreSQL
skane_municipalities = st_read(con_pg, query=qry_skane)


# Render municipalities to make sure the data was read correctly
mapview(skane_municipalities)


# Create region from  municipalities
skane_region = st_union(skane_municipalities)
st_is_valid(skane_region)


# Write result to PostGIS
st_write(dsn = con_pg, obj = skane_region, layer = "skane_region", drop = FALSE)
dbDisconnect(con_pg)   # Close connection

Now, all of my GIS tools can access the /PostgreSQLPostGIS databases.