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 postgis, 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