postgis-geocoder.md

Import House Districts

ogr2ogr -f 'PostgreSQL' PG:"dbname=ctnj-vote_development user=mzagaja" housect_37800_0000_2010_s100_census_1_shp_wgs84.shp -nlt MULTIPOLYGON -nln ct_house_districts

system({dbname: Rails.configuration.database_configuration[Rails.env]['database']}, "ruby", "-e p ENV['rubyguides']")

PostGIS TIGER Setup - A story

These instructions: https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension are not the best.

Ignore the instructions there. In the database you want to enable TIGER execute the following SQL:

CREATE EXTENSION postgis;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

You'll probably want to make a temporary directory for the data from TIGER to land in:

mkdir ~/gisdata
mkdir ~/gisdata/temp

Now export the loader script from the command line:

psql -c "SELECT Loader_Generate_Nation_Script('sh')" -d $YOUR_DATABASE -tA > ~/gisdata/nation_script_load.sh

Now you'll want to edit the script to use information related to your machine:

# Temp directory created above
TMPDIR=~/gisdata/temp/
# A tool for unzipping, usually unzip on most UNIX systems.
UNZIPTOOL=unzip
# Below is not needed for sh.
# WGETTOOL="/usr/bin/wget"
# MacOS homebrew users can use /usr/local/bin for this, on your server you'll want to point to the value of `which psql`.
export PGBIN=/usr/local/bin
# The below values are referenced by psql to decide where to connect to. See https://www.postgresql.org/docs/current/libpq-envars.html. 
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=yourpasswordhere
export PGDATABASE=geocoder
PSQL=${PGBIN}/psql
# The below is entirely unnecessary other than for the fact that the rest of the script inexplicably uses a variable to refer to this app.
SHP2PGSQL=shp2pgsql

Finally run the script

~/gisdata/nation_load_script.sh

Next Load the State Script

psql -c "SELECT Loader_Generate_Script(ARRAY['MA'], 'sh')" -d $YOUR_DATABASE -tA > /gisdata/ma_load.sh

Now edit the state script with the variables you used above, and run it.

~/gisdata/ma_load_script.sh

Finally clean up your TIGER tables.

SELECT install_missing_indexes();
vacuum (analyze, verbose) tiger.addr;
vacuum (analyze, verbose) tiger.edges;
vacuum (analyze, verbose) tiger.faces;
vacuum (analyze, verbose) tiger.featnames;
vacuum (analyze, verbose) tiger.place;
vacuum (analyze, verbose) tiger.cousub;
vacuum (analyze, verbose) tiger.county;
vacuum (analyze, verbose) tiger.state;
vacuum (analyze, verbose) tiger.zip_lookup_base;
vacuum (analyze, verbose) tiger.zip_state;
vacuum (analyze, verbose) tiger.zip_state_loc;

Now test your geocoder

SELECT * FROM geocode(
  '60 TEMPLE PL, BOSTON, MA', 1);