Install PostGIS and TIGER Data in Ubuntu 12.04

Question

Could someone write a brief albeit dumbed down idiot's guide to installing postgis and loading national Tiger data on ubuntu? I've tried a few guides, namely, http://wiki.bitnami.com/@api/deki/pages/302/pdf, but I'm not having much luck. I apologize for the open ended nature of this question.

Answer 1

Since you have PostGIS 2.1.1 you're ahead of the game. Make sure you have wget installed, it is what will download the data from the Census FTP site.

Create a gisdata directory with:

sudo mkdir /gisdata

Use chown and chgrp commands to change the ownership and group of /gisdata so that your normal user can read and write to /gisdata.

Start psql and connect to your database. Once in psql use

\a

and

\t

so that the results of the query are formatted correctly.

I forgot this part initially! Before you can use the loader script you need to do some house cleaning. First thing is to make sure the tiger schema is in your search path. Next up check the values in tiger.loader_platform and tiger.loader_variables. These two tables control variables for the loader script like your username and password. I usually just edit them in PGAdmin. Next up you'll need to run a script that populates lookup tables and other bits of background goodness the geocoder will need. First set an output file:

\o nation_generator.sh

then run:

SELECT loader_generate_nation_script('sh'); 

Then exit psql and run the file:

sh ./nation_generator.sh

Then hop back into psql and type:

\o loader_script.sh

to output the results of the query to a textfile called loader_script.sql. Then execute the function that generates the loader script:

SELECT loader_generate_script(ARRAY['DC','RI'], 'sh');

This is the query whose output will be redirected to loader_script.sql. Replace 'DC' and 'RI' with the two letter abbreviations of the states you want to download.

Exit out of psql and run the script with this command:

sh ./loader_script.sh

This will download the files for the state(s) you select, unzip them, and import the data into your PostGIS database.

Answer 2

There are multiple answers on this site dealing with the installation of PostGIS. Here's one, and Nick Gauthier's solution is also excellent. To reiterate the ample advice therein...

Installation of PostGIS has gotten a lot easier in Ubuntu 12.04 and later. There are generally two ways to go about it: installation from a PPA and building from source. What follows are general shell scripts I use for each case.

Installing from a PPA... I use the ubuntugis-unstable PPA.

# Add Ubuntu GIS PPA and update, upgrade
sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable -y && sudo apt-get update && sudo apt-get upgrade

# Install PostGIS
sudo apt-get install postgis

# Recommends: sudo apt-get install postgresql-9.1-postgis-2.0

# Configure template database
POSTGIS_TEMPLATE=postgis-2.0.1
POSTGRESQL_VER=9.1
sudo su -c "createdb $POSTGIS_TEMPLATE" - postgres 
sudo -u postgres psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='$POSTGIS_TEMPLATE';" 
sudo -u postgres psql -d $POSTGIS_TEMPLATE -f /usr/share/postgresql/$POSTGRESQL_VER/contrib/postgis-2.0/postgis.sql
sudo -u postgres psql -d $POSTGIS_TEMPLATE -c "GRANT ALL ON geometry_columns TO PUBLIC;"
sudo -u postgres psql -d $POSTGIS_TEMPLATE -c "GRANT SELECT ON spatial_ref_sys TO PUBLIC;"

Building from source... This assumes PostGIS 2.1 and PostgreSQL 9.1 but could be any recent version of both; change as necessary.

cd /usr/local/ && sudo mkdir postgis && sudo chown [username] postgis && cd postgis
wget http://download.osgeo.org/postgis/source/postgis-2.1.0.tar.gz
tar -xzvf postgis-2.1.0.tar.gz && rm postgis-2.1.0.tar.gz && cd postgis-2.1.0
sudo ./configure --with-projdir=/usr/local/proj/v4.7.0/
make
sudo make install

# Configure template database
POSTGIS_TEMPLATE=postgis-2.1.0
POSTGRESQL_VER=9.1
sudo su -c "createdb $POSTGIS_TEMPLATE" - postgres 
sudo -u postgres psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='$POSTGIS_TEMPLATE';" 
sudo -u postgres psql -d $POSTGIS_TEMPLATE -f /usr/share/postgresql/$POSTGRESQL_VER/contrib/postgis-2.0/postgis.sql
sudo -u postgres psql -d $POSTGIS_TEMPLATE -c "GRANT ALL ON geometry_columns TO PUBLIC;"
sudo -u postgres psql -d $POSTGIS_TEMPLATE -c "GRANT SELECT ON spatial_ref_sys TO PUBLIC;"

You'll notice a theme between the two; it is still necessary to create a template database and populate the spatial_ref_sys table.

As for getting set up with TIGER, follow the instructions for loading TIGER data in the PostGIS 2.x documentation, Chapter 2.