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:
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 http://www.snowman.net/git/tiger_geocoder/. 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 http://git-scm.com/tools. 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.
SELECT loader_generate_script(ARRAY['MA','ME'], 'windows');
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, 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
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
