7,612
edits
Changes
Jump to navigation
Jump to search
$ shp2pgsql nyu_2451_34505/nyu_2451_34505.shp
<nowiki>$ 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</nowiki>This documentation has been moved to the [[Tiger Geocoder]] wiki page.
→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.
== Creating a Postgis DB ==
=== Initial Setup ===
<nowiki>
$ 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</nowiki>
=== 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>$ 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 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>
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.
$ 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</nowiki>
==To get into tigertest database = Initial Setup = 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.
