Connecting to PostGIS from R

In this post, we will connect to spatial datasets loaded in PostGIS from within R.

First I have set environmental variables to connect to PostgreSQL in my environment. These are as follows

export PGUSER=nyc
export PGPASSWORD=nyc
export PGDATABASE=nyc

Next, we will launch R, and load the required library, rgdal. If you don't have rgdal installed, please refer to initial steps of my earlier post.


Now lets get started

# 05a_connect_ped_postgis.r
# Load required library
library(rgdal)

This loads the library

Next we read the PostgreSQL connection parameters from the shell environment using Sys.getenv command

#Set PostgreSQL connection parameters from the environment
pguser<-Sys.getenv('PGUSER')
pgpassword<-Sys.getenv('PGPASSWORD')
pgdatabase<-Sys.getenv('PGDATABASE')

Next we create the database connection by concatenating all variables into a connection string using the paste function in R , and we print to confirm we have it right.

#Make connection to PostGIS database
dsn<-paste("PG:dbname='",pgdatabase,"' user='",pguser,"' password='",pgpassword,"'",sep='')
dsn

Now we can use the ogrListLayers command to make sure we can see our previously loaded data.

ogrListLayers(dsn)

The command makes a connection to the database and lists all layers registered with PostGIS. Now we can also look up the details for a specific layer of our choice using ogrInfo function

ogrInfo(dsn, "nystaging.fatality")

Here is the complete listing..

# 05a_connect_ped_postgis.r
# Load required library
library(rgdal)

#Set PostgreSQL connection parameters from the environment
pguser<-Sys.getenv('PGUSER')
pgpassword<-Sys.getenv('PGPASSWORD')
pgdatabase<-Sys.getenv('PGDATABASE')

#Make connection to PostGIS database
dsn<-paste("PG:dbname='",pgdatabase,"' user='",pguser,"' password='",pgpassword,"'",sep='')
dsn
ogrListLayers(dsn)
ogrInfo(dsn, "nystaging.fatality")