Difference between revisions of "PostGIS Installation"
(19 intermediate revisions by 4 users not shown) | |||
Line 17: | Line 17: | ||
$ sudo apt-get install --no-install-recommends postgis</nowiki> | $ 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 | + | 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 postgresql, 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. | 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. | ||
Line 56: | Line 56: | ||
> select count(*) from nyu_2451_34505; | > select count(*) from nyu_2451_34505; | ||
> \q</nowiki> | > \q</nowiki> | ||
+ | |||
+ | == To get into tigertest database == | ||
+ | ssh researcher@128.42.44.181 | ||
+ | cd /bulk | ||
+ | psql tigertest | ||
== Translating Table names to corresponding States == | == 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" | {| class="wikitable" | ||
|- | |- | ||
Line 68: | Line 77: | ||
| tl_2016_02_place | | tl_2016_02_place | ||
| Alaska | | Alaska | ||
+ | |- | ||
+ | | tl_2016_03_place | ||
+ | | | ||
|- | |- | ||
| tl_2016_04_place | | tl_2016_04_place | ||
Line 78: | Line 90: | ||
| California | | California | ||
|- | |- | ||
− | | | + | | tl_2016_07_place |
− | | | + | | |
|- | |- | ||
| tl_2016_08_place | | tl_2016_08_place | ||
Line 98: | Line 110: | ||
| tl_2016_13_place | | tl_2016_13_place | ||
| Georgia | | Georgia | ||
+ | |- | ||
+ | | tl_2016_14_place | ||
+ | | | ||
|- | |- | ||
| tl_2016_15_place | | tl_2016_15_place | ||
| Hawaii | | 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. |
Latest revision as of 12:39, 8 October 2019
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 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.
This documentation has been moved to the Tiger Geocoder wiki page.