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.
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
- Install Postgre SQL from the following location – http://www.postgresql.org/download/windows/
- 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
- Open pgAdminIII from within the Postgre start menu folder
- Login to your PostgreSQL database by entering in the admin password you set on installation
- 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
- 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
- Open the PostGIS Shapefile Import/Export Manager
- Connect to your database by clicking on View Connection details and entering the username, password and database name
- 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:
- Click Layer -> Add Layer -> PostGIS
- Create a New PostGIS connection
- Connect to the PostGIS connectino
- Select the data of interest
- Click Add
- 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 🙂
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
1k Buffer around geometry containing the word Bristol in column msoa11nm