7,612
edits
Changes
Jump to navigation
Jump to search
# TODO$ for i in $(seq -f "%02g" 1 80); do# ftp wget "https://ftp2www2.census.gov/geo/tiger/TIGER2016/PLACE/tl_2016_${i}_place.zip"; sleep 3 # not necessary but a good guy scraper movedone;$ 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</nowiki>
→Tiger Geocoder Extension
$ sudo apt-get install --no-install-recommends postgis</nowiki>
Note the last line. Without the <code>--no-install-recommends</code> 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 postgispostgresql, we do not want this.
The <code>postgis</code> 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.
=== 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.
<nowiki>
Also say we want to separate New York City, New York into a more granular by-borough set of polygons. Using [https://geo.nyu.edu/catalog/nyu_2451_34505 City University of New York data], we will import this shapefile.
> select count(*) from nyu_2451_34505;
> \q</nowiki>
== 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)
{| class="wikitable"
|-
! 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 [https://postgis.net/docs/Extras.html here].
[https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension This link] outlines the process to enable our Postgres Database to support Tiger functionality.
This documentation has been moved to the [[Tiger Geocoder]] wiki page.
