Postgres Open 2014: Chicago

Although we don't require you to have PostGIS 2.1 and PostgreSQL 9.3 to attend these tutorials, you'll be better able to follow along if you do.

We'll be posting downloadable data, tutorial materials and examples for these here so bookmark this page so you can cut and paste the examples if you want to follow along.

Since time is short, we have a lot of ground to cover, and there are various platforms people will be using, we won't have time to help with installation issues. However we will have the slides posted here and available after the presentations, so you can always try these exercises after the sessions.

If you'll be at either of these tutorials and have particular kinds of examples you'd really like us to demonstrate, email us at lr@pcorp.us.

• Installation Starter Guide
• pgAdmin III Not required, but if you don't know your way around psql, might be helpful.
• QGIS popular viewing tool for PostGIS
• OpenJump Great for adhoc PostGIS queries

• We will also be using NodeJS as a quicky web app for viewing data. For the NodeJS, install NodeJS, make a directory where you'll host the app. Source code for quicky app is https://github.com/robe2/node_postgis_express

  We'll be using two libraries -- express and pg.js. Both are pure Javascript libraries so should work fine on any node.js install.

  At OS shell/command line prompt. Run the below two.

  npm install express
  npm install pg.js

• OSM2PGSQL is a command-line tool for loading OpenStreetMap data. We'll be using it to load Chicago OSM data. If you are a windows user, we suggest http://wiki.openstreetmap.org/wiki/Osm2pgsql#Cygwin which seems to be the most robust when working on windows.

• If you want to use the compressed files, you need something that can extract bzipped files. This normally exists already on Unix/Linux. For windows users, easiest is to use 7-zip.

Session 1: PostGIS Introduction: Geometry, Geography, and Geocoding

Sept. 17, 2014
09:00 AM - 12:00 PM

We'll cover core PostGIS types geometry, geography, and the tiger geocoder packaged with PostGIS. We'll also explore a bit the 3D-geometry types.

Data sources

• For tiger geocoder piece, we have already prepared set of data from Census Tiger 2014 dataset. tiger_data_chic.sql.bz2 (77MB). Extract the file, and in a database with postgis_tiger_geocoder extension installed, run the sql script from psql.
• OSM Data - https://mapzen.com/metro-extracts - Navigate with map -- or click on C and download the Chicago, Illinois PBF file. We'll be loading this with OSM2PGSQL.

Session 2: PostGIS on caffeine: Raster, Topology, and pgRouting

Sept. 17, 2014
01:30 PM - 4:30 PM

This session assumes you have an understanding of PostGIS geometry. We'll cover some nifty things you can do with PostGIS raster and also explore PostGIS topology and pgRouting.

To get the most out of this you should have pgRouting installed as well.

For network routing, we'll build our pgrouting network using http://osm2po.de which is a Java-based loader for OSM (PBF, OSM,bz) formats and loads in a routable form. We'll be using our chicago pbf file. Already processed data is hh_2po_4pgr.sql.bz2 (40MB)

Save 40% off on Third Edition of PostGIS In Action and other Manning published books Offer ends August 19th, 2022

pgRouting: A Practical Guide is available in Hardcopy from Hardcopy from Amazon. E-Book or Bulk discounts on Hard-copy from Locate Press.