How to pull a shapefile into R from PostGIS database (in Windows)?

Question

I'm currently working on pulling data from a PostGIS database for the first time to map in R and need some help. The database contains a shapefile of countries/regions of the world. I am able to pull the attributes into R as a table through the dbReadTable function from the package RPostgreSQL. This is great for manipulating the data (adding, merging, subsetting, etc.), but ultimately, I still need to be able to actually map this bad boy, so I need the data to be spatial in some way (side note: I plan on mapping with ggplot2, so my data needs to be suitable for that). Here's the tiny bit of code I've successfully used to pull in the data as a table/data frame:

#load in required libraries
library(RPostgreSQL)

#connect to database
drv <- dbDriver("PostgreSQL")
DB <- dbConnect(drv, dbname = "dbname", user = "user", 
                      host = "host", 
                      password = "password", port = "5432")
#pull in shapefile data
world.shp <- dbReadTable(DB, "ne_10m_admin_1_states_provinces")

Again, I have no problems connecting to the database. That said, I need to pull the data in as a shapefile rather than as a data frame. This question seems to be what I'm looking for, but the accepted/top solution confuses me; specifically, I don't know how to tackle this bit of code to pull in the shapefile from the database:

require(rgdal)
dsn="PG:dbname='gis'"

Similarly, I've found various solutions for Ubuntu, but only this one for Windows, which spits out errors for me.

Databases like PostgreSQL do not store shapefiles for geometry operation -- they store tables, with a geometry type column. Once loaded, the relationship between the two is severed. Most GIS software can plot geometries directly from PostgreSQL. — Vince, Jan 08 '16 at 20:08
So there's no way to bring the shapefile into R? I've done it in QGIS, but I'm trying to do all of my mapping for this project in R. — Lauren, Jan 08 '16 at 20:26
It's *not* a shapefile. You should be researching how to get a PostGIS table into R, or how to generate a shapefile in R from a table. If you think about the problem as pulling a shapefile through the database then you won't be using the right search terms. — Vince, Jan 08 '16 at 20:35
rdal is normally the best way to read from database sources, but the optional driver for PostgreSQL is often not compiled (e.g. in Windows); see ogrDrivers() if it is listed, or not. You could write shapefiles from the database, which can be read with rgdal. — Mike T, Jan 10 '16 at 22:07
@Vince, thanks for the clarification. I'm clearly new to PostGIS. Because I was able to pull it straight from the database into QGIS, I assumed that it was still considered a shapefile. I suppose I should ask a new updated question? — Lauren, Jan 11 '16 at 15:39

score 5 · Answer 1 · edited Dec 27 '17 at 21:04

At the University of Florida, David Bucklin and I have released a rpostgis package that provides bi-directional transfer between PostGIS and R for vector and raster data. The package does not rely on GDAL (and rgdal), and should be platform independent.

Given that you already have a functional connection DB established through RPostgreSQL, you can import PostGIS raster data type into R using the function pgGetGeom, for instance:

library(rpostgis)
world.shp <- pgGetGeom(DB, "ne_10m_admin_1_states_provinces")

If the table is not in the public schema (which would be a good idea anyway), you will need to declare it using c("schema", "table") instead. The function assumes that the geometries are stored in the column "geom" by default, but you can change that with the argument geom.

How to pull a shapefile into R from PostGIS database (in Windows)?

1 Answers1