Difference between revisions of "PostGIS Installation"

From edegan.com
Jump to navigation Jump to search
 
(28 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 postgis, we do not want this.
+
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.
  
 
== Creating 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 ===
 
=== Bulk Download TIGER Shapefiles ===
  
−
For example, say we want all of the state-level place data.
+
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>
 
  <nowiki>
−
# TODO
+
$ for i in $(seq -f "%02g" 1 80); do
−
# ftp://ftp2.census.gov/geo/tiger/TIGER2016/PLACE/
+
    wget "https://www2.census.gov/geo/tiger/TIGER2016/PLACE/tl_2016_${i}_place.zip";
−
</nowiki>
+
    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</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.
 
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.
Line 41: Line 55:
 
$ psql --username researcher --dbname tigertest
 
$ psql --username researcher --dbname tigertest
 
> select count(*) from nyu_2451_34505;
 
> select count(*) from nyu_2451_34505;
−
> \q
+
> \q</nowiki>
−
</nowiki>
 
  
−
=== Initial Setup ===
+
== 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.
  
−
<nowiki>
+
This documentation has been moved to the [[Tiger Geocoder]] wiki page.
−
$ 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>
 

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.


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.

Retrieved from "http://www.edegan.com/mediawiki/index.php?title=PostGIS_Installation&oldid=26159"