PostGIS in Action (3rd)
Where the adventure begins

PostGIS in Action: (ed. #1) Chapter 10 - Enhancing SQL with add-ons

PostGIS In Action 3rd Edition Chapters    PostGIS In Action 2nd Edition Chapters    1st Edition Chapters
Download chapter code and data

This chapter covers common server-side open source add-on tools that are used to enhance the functionality of PostGIS. These tools run directly in the PostgreSQL server database and can be called using SQL.

Items in this chapter are:

  • Tiger Geocoder -

    UPDATE In PostGIS 2.0, we have packaged an updated Tiger version that works with Tiger 2010 data. It is also now incorporated in the official PostGIS documentation: Extras Tiger Geocoder section and in addition sports a reverse geocoder and revised to not use deprecated functions. You can find it in the extras folder of the postgis tar ball and also directly from PostGIS subversion repository.

    The tiger geocoder packaged with PostGIS 1.5 and below is a bit old and doesn't handle the new US census data ESRI shapefile format first introduced in Tiger 2007 set. We encourage you to use the newer one currently available via a Stephen Frost's GIT repository Please note that the load scripts packaged require PostgreSQL 8.3+ to use. We will be going over the new version of the geocoder that has been improved on by Steve and his friends.

    You can download a GIT client from If you are familiar with TortoiseSVN and are on windows, TortoiseGIT might be the easiest to work with for you.

    We have modified Steve's version slightly to work with the Tiger 2009 data and also added a slightly different loader approach. Our version of the code can be downloaded from Tiger geocoder 2009 with additional loader scripts.

    The loader scripts currently in GIT are Linux centric. We will be demonstrating a table-driven custom tiger loader that will work both on Windows and Linux using table data driven techniques that use SQL to generate OS specific command line scripts. The select statement will look something like

    SELECT loader_generate_script(ARRAY['MA','ME'], 'windows');

    and will generate a suitable command line script that will download data from census for selected states, extract, and load data into a PostGIS db with suitable geocoder table structures.

  • PgRouting - loading up and doing route queries
  • PLPython -

    Main focus is Writing PostgreSQL stored functions in Python and wrapping the beauty of python in SQL.

    We'll cover how to enable PL/Python in a database, some quick PL/Python functions including one showing how to load data from an Excel file that works for both windows and Linux.

  • PL-R - writing PostgreSQL stored functions in R. R is the open source equivalent of things like SAS and is descended from the AT&T S language.

    In this chapter we'll cover enabling PL/R in your PostgreSQL database, loading R packages, quick R concepts, some spatial examples using rgdal and sp R packages as well as generating graphical plots with PL/R functions. Loading PostGIS data into sp Spatial DataFrames and plotting spatial data.

If you are interested in the R statistical environment don't miss out on this R 4 page cheat-sheet reference card covering the most commonly used elements of R.

You may also want to check-out the upcoming Manning book, R in Action which is also scheduled for release around the same time as PostGIS in Action and that can be purchased in E-book MEAP format now. First chapter is a free download. The R in Action author, Robert Kabacoff, manages a site chuck full of short R recipes Quick-R

Another good book and free e-book, fairly inexpensive hard-copy is A Practical Guide to Geostatistical Mapping by Tomislav Hengl This is a book with lots of R geostatistics and GRASS examples.

As of this writing, PL/R now works with the EnterpriseDb PostgreSQL installer. We are immensely greatful to Joe Conway for getting this working so that windows users aren't left out in the cold when we discuss this in this chapter. Windows Installer for 8.3 and 8.4 is now available No installer yet , but you can download the plr.dll and plr.sql files for PostgreSQL 8.4 on windows. Details in this thread PL/R in VC 2005++ compiled PostgreSQL

To get PL/R to work under windows -- you need to

  1. Install R -as of this writing R 2.10.1 is the latest, but the PL/R dll will work with any version of R from 2.5.0+
  2. Set environment variable R_HOME to C:/Program Files .../R/R-versionhere
  3. Add R-..version/bin folder to system path variable
  4. Copy the plr.dll into your PostgreSQL../lib folder
  5. Restart your PostgreSQL service
  6. Install the plr.sql in whatever databases you want to run R in

For a quick bite on what you can do with PL/R - check out Joe Conway's PgDay 2009 PL/R presentation.

PgRouting -- here are some useful tutorials on the subject

Python Geospatial Development
Python Geospatial Development
Save 40% off on Third Edition of PostGIS In Action and other Manning published books Offer ends August 19th, 2022
PostGIS in Action,3rd edition pgRouting: a Practical Guide SQL In a Nutshell, 4th Edition