Webinar: PostGIS Advanced Features (6/6)

This page has the resources for the PostGIS Advanced Features webinar from Tuesday March 4, 2020. This session is the sixth and final (!) in a series of six covering how I work with PostGIS and OpenStreetMap. See the intro page for links to the other videos in the series.

Recording

Downloads for session

The data used for this session's pgrouting demo is available to downloaded and load to a test PostGIS database in advance of the session. Instructions for loading via command line are below.

• Golden OpenStreetMap data (.sql.gz) (2.7 MB)
• MD5 Checksum

The SQL script used for the demo:

• Routing Demo Code (SQL)

Steps to load

The following steps will download and load the example data set. It's always a good idea to ensure the checksum of the downloaded file matches the provided checksum.

sudo su - postgres
mkdir ~/tmp
cd ~/tmp
wget https://blog.rustprooflabs.com/static/data/osm_golden_co.sql.gz
wget https://blog.rustprooflabs.com/static/data/osm_golden_co.sql.gz.md5

Checksum:

cat osm_golden_co.sql.gz.md5
b1edd3b81c34a66a67cd58a76fadf70d  osm_golden_co.sql.gz

md5sum osm_golden_co.sql.gz
b1edd3b81c34a66a67cd58a76fadf70d  osm_golden_co.sql.gz

Extract the file, create DB w/ PostGIS and hstore extensions and load the data.

gunzip osm_golden_co.sql.gz
psql -c "CREATE DATABASE golden_demo;"
psql -d golden_demo -c "CREATE EXTENSION postgis;"
psql -d golden_demo -c "CREATE EXTENSION hstore;"
psql -d golden_demo -f osm_golden_co.sql

Included in the data

The data set is from Colorado's OpenStreetMap data processed on 2/27/2020. See Load PostGIS with OpenStreetMap and the PgOSM project for more details on that process. From there, I created four (4) table extracts in the golden schema, filtered down from the full Colorado data using this query:

SELECT ST_Buffer(ST_Envelope(ST_Collect(way)), 3000) AS way
    FROM osm.boundary_polygon b 
    WHERE b.name = 'Golden'
;

Tables with OpenStreetMap data are:

• golden.boundary_polygon
• golden.building_polygon
• golden.natural_point
• golden.road_line

The other helper tables included for meta-data and styling.

• pgosm.layer_detail
• pgosm.layer_group
• pgosm.routable
• public.layer_styles

Layer Styles XML

The data set includes XML data for the public.layer_styles table used by QGIS. If the database name is not golden_demo, you need to update f_table_catalog accordingly for default styling to apply.

The latest minor release of all supported versions of Postgres can restore this data. Older versions of Postgres may not have the XML bug fixed and will require the SET XML OPTION DOCUMENT; workaround.

Links

Links from the slides in the order they appeared. Sorry for the lack of context in this list right now, I will try to come back to this and provide more context in the future.

• https://postgis.net/2019/10/20/postgis-3.0.0/
• https://pgrouting.org/
• https://postgis.net/docs/ST_IsValidReason.html
• https://github.com/osmlab/fixing-polygons-in-osm/blob/master/doc/background.md#winding-order
• https://github.com/rustprooflabs/pgdd
• https://postgis.net/docs/Address_Standardizer.html
• https://postgis.net/docs/stdaddr.html
• https://postgis.net/docs/Pagc_Normalize_Address.html
• https://experimentalcraft.wordpress.com/2017/11/01/how-to-make-a-postgis-tiger-geocoder-in-less-than-5-days/
• https://postgis.net/docs/ST_Project.html
• https://github.com/rustprooflabs/pgosm/blob/master/travel_time_grid.md
• https://postgis.net/docs/ST_NumInteriorRings.html
• https://postgis.net/docs/manual-3.0/ST_Extrude.html
• https://doc.x3dom.org/tutorials/index.html
• https://docs.pgrouting.org/latest/en/index.html

Summary

Need help with your PostgreSQL servers or databases? Need a presenter on the technologies and data I am excited about? Contact us to start the conversation!