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 Lee Hachadoorian 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