PostGIS Installation

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

Installation Commands

Going off of http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS23UbuntuPGSQL96Apt

$ lsb_release --codename
Codename:       trusty
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main" >> /etc/apt/sources.list'
$ wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
$ psql -V
psql (PostgreSQL) 9.5.4
$ sudo apt install postgresql-9.5-postgis-2.3
$ sudo apt-get install --no-install-recommends postgis

Note the last line. Without the --no-install-recommends line postgres assumes you're using the default configuration (in this case, postgis for postgresql 9.6). Since we're using a different flavor of postgresql, we do not want this.

The postgis apt package is not the extension to postgres. It is a collection of command line utilities for importing and exporting data to a postgis db.

Creating a Postgis DB

Initial Setup

$ createdb --username researcher tigertest
$ psql --username researcher --dbname tigertest
> create extension postgis;
> select postgis_full_version(); -- sanity test and make sure installed and enabled
> \q

Bulk Download TIGER Shapefiles

For example, say we want all of the state-level place data. The first step is to find a programmatic URL that we can use. You can inspect the HTML on the HTML interface to place data to get the correct mapping of states/territories to two digit integer. Note that they are not necessarily sequential and there is not strictly fifty.

$ for i in $(seq -f "%02g" 1 80); do
    wget "https://www2.census.gov/geo/tiger/TIGER2016/PLACE/tl_2016_${i}_place.zip";
    sleep 3 # not necessary but a good guy scraper move
done;
$ for f in *.zip; do unzip "$f"; done
$ for f in *.shp; do shp2pgsql -I "$f" | psql -U researcher -d tigertest; done
$ psql --username researcher --dbname tigertest
> select count(*) from tl_2016_01_place; -- 585

Also say we want to separate New York City, New York into a more granular by-borough set of polygons. Using City University of New York data, we will import this shapefile.

$ curl --insecure https://archive.nyu.edu/retrieve/74704/nyu_2451_34505.zip > /tmp/boroughs.zip
$ cd /tmp
$ unzip boroughs.zip
$ shp2pgsql -I nyu_2451_34505/nyu_2451_34505.shp | psql -U researcher -d tigertest
$ psql --username researcher --dbname tigertest
> select count(*) from nyu_2451_34505;
> \q

To get into tigertest database

ssh researcher@128.42.44.181
cd /bulk
psql tigertest

Translating Table names to corresponding States

See: https://www.census.gov/geo/reference/ansi_statetables.html (note that the numbers are FIPS state numeric codes)

Table Name	Corresponding State
tl_2016_01_place	Alabama
tl_2016_02_place	Alaska
tl_2016_03_place
tl_2016_04_place	Arizona
tl_2016_05_place	Arkansas
tl_2016_06_place	California
tl_2016_07_place
tl_2016_08_place	Colorado
tl_2016_09_place	Missouri
tl_2016_10_place	Delaware
tl_2016_11_place	District of Columbia
tl_2016_12_place	Florida
tl_2016_13_place	Georgia
tl_2016_14_place
tl_2016_15_place	Hawaii
tl_2016_16_place	Idaho
tl_2016_17_place	Illinois
tl_2016_18_place	Indiana
tl_2016_19_place	Iowa
tl_2016_20_place	Kansas
tl_2016_21_place	Kentucky
tl_2016_22_place	Louisiana
tl_2016_23_place	Maine
tl_2016_24_place	Maryland
tl_2016_25_place	Massachusetts
tl_2016_26_place	Michigan
tl_2016_27_place	Minnesota
tl_2016_28_place	Mississippi
tl_2016_29_place	Missouri
tl_2016_30_place	Motana
tl_2016_31_place	Nebraska
tl_2016_32_place	Nevada
tl_2016_33_place	New Hampshire
tl_2016_34_place	New Jersey
tl_2016_35_place	New Mexico
tl_2016_36_place	New York
tl_2016_37_place	North Carolina
tl_2016_38_place	North Dakota
tl_2016_39_place	Ohio
tl_2016_40_place	Oklahoma
tl_2016_41_place	Oregon
tl_2016_42_place	Pennsylvania
tl_2016_43_place
tl_2016_44_place	Rhode Island
tl_2016_45_place	South Carolina
tl_2016_46_place	South Dakota
tl_2016_47_place	Tennessee
tl_2016_48_place	Texas
tl_2016_49_place	Utah
tl_2016_50_place	Vermont
tl_2016_51_place	Virginia
tl_2016_52_place
tl_2016_53_place	Washington
tl_2016_54_place	West Virginia
tl_2016_55_place	Wisconsin
tl_2016_56_place	Wyoming
tl_2016_57_place
tl_2016_58_place
tl_2016_59_place
tl_2016_60_place	American Samoa
tl_2016_61_place
tl_2016_62_place
tl_2016_63_place
tl_2016_64_place
tl_2016_65_place
tl_2016_66_place	Guam
tl_2016_67_place
tl_2016_68_place
tl_2016_69_place	Northern Marinas Islands
tl_2016_70_place
tl_2016_71_place
tl_2016_72_place	Puerto Rico
tl_2016_73_place
tl_2016_74_place
tl_2016_75_place
tl_2016_76_place
tl_2016_77_place
tl_2016_78_place	Virgin Islands

Tiger Geocoder Extension

This section details the process to install and use the Tiger Geocoder Extension of PostGIS. The official docmentation can be found here.

This link outlines the process to enable our Postgres Database to support Tiger functionality.

I began by adding the extension listed above. First, enter into Postgres by using the psql command. Then:

--Add Extensions to database
CREATE EXTENSION postgis;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;

You can test that the installation worked by running the following query:

SELECT na.address, na.streetname,na.streettypeabbrev, na.zip
	FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;

This should return the following:

 address | streetname | streettypeabbrev |  zip
---------+------------+------------------+-------
	   1 | Devonshire | Pl               | 02109

Next, a new profile needs to be created by using the following command.

INSERT INTO tiger.loader_platform(os, declare_sect, pgbin, wget, unzip_command, psql, path_sep, 
		   loader, environ_set_command, county_process_command)
SELECT 'newuser', declare_sect, pgbin, wget, unzip_command, psql, path_sep,
	   loader, environ_set_command, county_process_command
  FROM tiger.loader_platform
  WHERE os = 'sh';

The installation instructions also provide the following note:

As of PostGIS 2.4.1 the Zip code-5 digit tabulation area zcta5 load step was revised to load current zcta5 data and is part of the Loader_Generate_Nation_Script when enabled. It is turned off by default because it takes quite a bit of time to load (20 to 60 minutes), takes up quite a bit of disk space, and is not used that often.

If you would like this feature, you can enable it by using the following command. This should be done before loading the script.

UPDATE tiger.loader_lookuptables SET load = true WHERE table_name = 'zcta510';

The paths in declare_sect need to be edited so they match our server locations. One option is to edit the declare_sect column in the tiger.loader_platform table. If so, the declare_sect looks like the following:

export PGHOST=localhost                       +
export PGUSER=postgres                        +
export PGPASSWORD=yourpasswordhere            +
export PGDATABASE=geocoder                    +
PSQL=${PGBIN}/psql                            +
SHP2PGSQL=shp2pgsql                           +
cd ${staging_fold}                            +

TMPDIR="${staging_fold}/temp/"                +
UNZIPTOOL=unzip                               +
WGETTOOL="/usr/bin/wget"                      +
export PGBIN=/usr/lib/postgresql/9.6/bin      +
export PGPORT=5432                            +
export PGHOST=localhost                       +
export PGUSER=postgres                        +
export PGPASSWORD=yourpasswordhere            +
export PGDATABASE=geocoder                    +
PSQL=${PGBIN}/psql                            +
SHP2PGSQL=shp2pgsql                           +
cd ${staging_fold}

Another option is to edit the sh file before running the script. The downloaded script is located in the following directory:

/gisdata

There needs to be a directory called "temp" in the gisdata directory. To make the script, use the following from the command line:

psql -c "SELECT Loader_Generate_Nation_Script('debbie')" -d databasename -tA > /gisdata/nation_script_load.sh

This will create a script in the gisdata directory. Change to that directory. If you did not edit the paths in the declare_sect table in psql, then you will need to edit this file to contain the correct paths. Run the script by using:

sh nation_script_load.sh

We changed the PGUSER and PGPASSWORD fields to:

PGUSER=postgres
PGPASSWORD=(Ask Anne for this password)!

Everything else remains the same.