9

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.

  • What version of PostGIS are you running? Is it the version packaged with 12.04 or have you upgraded? If you can you want to run the latest PostGIS, 2.1.1. The TIGER Geocoder that comes with that version is much better than the version that works with PostGIS 1.5. – HeyOverThere Jan 3 '14 at 20:19
  • I am indeed using 2.1.1. I have all the necessary extensions enabled (fuzzystrmatch, postgis, postgi_tiger_geocoder, postgis_topology) and the tiger schema. Now, I just need to upload national census data. – stat_novice Jan 3 '14 at 20:21
12

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.

  • HeyOverThere: Thank you for the detailed response. Whilst in psql, typing "\o loader_script.sql" results in a permission denied statement. Any idea as to why? I'm logged in as user postgres. – stat_novice Jan 3 '14 at 21:12
  • Sorry! I forgot most people don't use their normal user accounts to run psql. You'll need to save the script somewhere that your postgres user has write permission. Easiest is to use \o /tmp/loader_script.sql which will save the file to /tmp. Then to run the file it will be sh ./tmp/loader_script.sql. – HeyOverThere Jan 3 '14 at 21:17
  • That worked! Though, now I'm receiving this error message "No function matches the given name and argument types. You might need to add explicit type casts." when executing the query for the loader script. Also, if I remember correctly, I'll have to edit that file, right to add my username and db? – stat_novice Jan 3 '14 at 21:22
  • Double sorry! I forgot about that too! First thing to check will be your database's search path, make sure the tiger schema is in there. 2nd thing to check is in the tiger schema there are tables, tiger.loader_platform and tiger.loader_variables that tell your scripts your password and other information. Use PGAdmin to edit those. Finally, before running your loader script run the loader_generate_nation function to set up all the tables that the geocoder needs. I'll edit my answer to fill in these blanks. – HeyOverThere Jan 3 '14 at 21:31
  • HeyOverThere: You are a god among men/women. This almost works. Both the nation and state scripts run and they are FTPing the census website and seem to be retrieving files, but after both complete they issue some of the following error statements: "loader_script.sql: 408: loader_script.sql: /usr/pgsql-9.0/bin/psql: not found". Moreover, there is no data in the tiger_data schema. Do I need to edit the files, specifically this line: "export PGBIN=/usr/pgsql-9.0/bin"? – stat_novice Jan 3 '14 at 22:25
0

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.

  • Arthur: I have PostGIS 2.1 installed and postgis.sql does not exist in the following directory: /usr/share/postgresql/9.3/contrib/postgis-2.1 – stat_novice Jan 3 '14 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.