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.
Contents
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
| 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 |
