Difference between revisions of "PostGIS Installation"
| Line 34: | Line 34: | ||
=== 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> | ||
| − | # | + | #!/bin/bash |
| − | + | ||
| + | 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;</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. | ||
Revision as of 17:33, 22 September 2017
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.
#!/bin/bash
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;
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
