How-to Install a Spatial Database (PostGIS) and use it from within R and QGIS

This post describes the benefits and process of installing and using a spatial database within R and QGIS. The spatial database that we will be using is PostGIS a package that provides Spatial Objects to Postgre’s ultra-fast and ultra-scalable database environment.

Benefits

Postgre SQL is a fantastic database environment to learn because it uses standard compliant SQL and it provides enterprise performance features for free such as:

  • unlimited database size!
  • 32 Terabyte maximum table size!
  • 1 GB maximum field size!

PostGIS is good to learn because it allows you to perform spatial queries on large data sets using a powerful spatial query language. A nice introduction to SQL and spatial SQL for Postgre can be found here.

Installing Postgre SQL & PostGIS

  1. Install Postgre SQL from the following location –  http://www.postgresql.org/download/windows/
  2. Install PostGIS from the following location http://download.osgeo.org/postgis/windows/pg94/

A useful tutorial for installing Postgre SQL & PostGIS can be found here but the process is pretty self-explanatory if you have some familiarity with databases and/or GIS.

Creating Your First PostGIS Spatial Database

  1. Open pgAdminIII from within the Postgre start menu folder
  2. Login to your PostgreSQL database by entering in the admin password you set on installation
  3. Create a new database by right clicking on databases and clicking new database – fill in the name (e.g. gisdb) and the owner then click ok
  4. Select the database that you created and add postgis as an extension so that it becomes a spatial database

Loading Shape Data into Your PostGIS Database

PostGIS comes with a very easy to use shape file importer which makes it trivial to populate your spatial database

  1. Open the PostGIS Shapefile Import/Export Manager
  2. Connect to your database by clicking on View Connection details and entering the username, password and database name
  3. Click Addfile and select the shapefile that you want to convert into a table

Accessing Your Data Using QGIS

QGIS is a freely available Geographic Information System that may be used for manipulating and visualising spatial data. Its a tool I like to use for visualisation prior to taking my data in R. Using a spatial database rather than shapefiles allows me to interact with the data much more seemlessly than constantly exporting files between the two.

To view your data held in PostGIS in QGIS perform the following:

  1. Click Layer -> Add Layer -> PostGIS
  2. Create a New PostGIS connection
  3. Connect to the PostGIS connectino
  4. Select the data of interest
  5. Click Add
  6. Select Coordinate Reference System

Accessing Your Data Using R

Accessing the data from within R is straight forward once you have developed an appreciation of SQL. (Unfortunately for Windows users it is not possible to seemlessly interact with the data using rgdal library due to driver compilation issues. However it can be accessed relatively seemlessly using RPostgreSQL library – many thanks to  for his neat post which shows a work around using readWKT.)

I provide the following function below to illustrate how to read a table from PostGIS and convert it to a SpatialPolygonsDataFrame. This code is based on Lee’s post but I’ve removed the for-loop with mapply to improve performance 🙂

library(RPostgreSQL)
library(rgeos)
library(sp)

dbname <- "gisdb"
host <- "127.0.0.1"
user <- "user"
password <- "password"
table <- "msoa_within_50kbristol"

getTable <- function ( table )
{
	# Load data from the PostGIS server
	conn = dbConnect(
	dbDriver("PostgreSQL"), dbname=dbname, host=host, port=5432, 
	user=user, password=password
	)
	
	strSQL = "
		SELECT ST_AsText(geom) AS wkt_geometry , *
		FROM msoa_within_50kbristol
		"
  
	dfQuery = dbGetQuery(conn, strSQL)
	dfQuery$geom <- NULL
	row.names(dfQuery) = dfQuery$gid
	 
	# Create spatial polygons
	rWKT <- function (var1 , var2 ) { return (readWKT ( var1 , var2) @polygons) }
	spL <- mapply( rWKT , dfQuery$wkt_geometry ,  dfQuery$gid )
	spTemp <- SpatialPolygons( spL )
	 
	# Create SpatialPolygonsDataFrame, drop WKT field from attributes
	spdf = SpatialPolygonsDataFrame(spTemp, dfQuery[-1])
	
	return (spdf)
}


bristol <- getTable( table )
plot(bristol)
head ( bristol@data )

Learn How to Perform Spatial Queries

A nice introduction to SQL and spatial SQL for Postgre can be found here but to get you started I’ve created some basic and hopefully  self-explanatory spatial queries to get you started.

Union of geometry containing the word Bristol in column msoa11nm

SELECT ST_Union(geom) FROM msoa_within_50kbristol WHERE msoa11nm LIKE '%Bristol%'

1k Buffer around geometry containing the word Bristol in column msoa11nm

SELECT ST_BUFFER(geom , 1000) FROM msoa_within_50kbristol WHERE msoa11nm LIKE '%Bristol%'
Advertisements

One thought on “How-to Install a Spatial Database (PostGIS) and use it from within R and QGIS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s