google-templatespostgresql

Install and use Tiger Geocoder

NOTE: This guide is for PostgreSQL 9.1+.

Install Tiger Geocoder

Tiger Geocoder is a plpgsql-based fully-functional geocoder that can process an arbitrary address string and, using normalized TIGER census data, produce a point geometry and rating reflecting the location of the given address and likeliness of the location.

To install it, follow these steps:

  • Navigate to the /opt/bitnami/postgresql/share/contrib/postgis-2.0/extras/tiger_geocoder/tiger_2010/ directory.

  • Edit the create_geocode.sh script and set the PGPASSWORD and THEDB parameters. In this example, the database name will be geocoder (the default value) so it is not necessary to modify the THEDB parameter. Set the PGPASSWORD parameter to your database password.

  • In the same file, uncomment the line below if it is not already uncommented:

      ${PSQL_CMD} -d "${THEDB}" -c "ALTER DATABASE ${THEDB} SET search_path=public, tiger;"
    
  • Edit the tiger_loader.sql file and set the PGPASSWORD and PGDATABASE parameters with the same values as in the create_geocoder.sh file. You may need to modify the lines containing export PGPASSWORD and export PGDATABASE.

  • Edit other parameters in the files, such as PGPORT, PGHOST, PGUSER, PSQL_CMD and PGBIN, as needed.

  • Edit the create_geocoder.sh file and comment out the line which refers to fuzzystrmatch.sql and uncomment the line containing CREATE EXTENSION fuzzystrmatch.

  • Create an example database with PostGIS support:

      $ createdb -T template_postgis geocoder
    
  • Create the necessary schema:

      $ ./create_geocode.sh
    

    This will create the tiger schema in your database and make it part of your database search_path. To verify this, run the following command:

      $ psql geocoder -c "SHOW search_path";
    
      search_path
      ---------------
      public, tiger
      (1 row)
    
      $ psql geocoder -c "SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address;"
    
      pretty_address
      ---------------------------------------
      202 E Fremont St, Las Vegas, NV 89101
      (1 row)
    

Load Tiger Geocoder data

After installing Tiger Geocoder, load data from the census website into your database. The easiest way to do this is to use the loader_generate_script() function and create a script that will download data from the census website, extract it and load it into the example database. This example will load the data for the state of Rhode Island (RI).

  • Use the following command to create the script RIdata.sh:

      $ psql geocoder -c "SELECT loader_generate_script(ARRAY['RI'], 'sh');" -A -o RIdata.sh
      $ chmod +x RIdata.sh
    
  • Edit this file and set the PGPASSWORD parameter to reflect the correct database password.

  • Remove the first and last lines of the script. These are not Bash commands but output of the SQL command executed previously). Notice that the script uses the /gisdata directory to store the data downloaded from the census website. You can change this location if you wish.

  • Run these commands to obtain and load the data. Note that the wget and unzip commands should be installed for this to work.

      $ sudo mkdir /gisdata
      $ sudo chown -R bitnami /gisdata
      $ ./RIdata.sh
      $ psql geocoder -c "SELECT install_missing_indexes();"
    

    If necessary, replace the account username bitnami with the correct username for the user running these commands.

Last modification December 21, 2022