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.
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.
The SQL script used for the demo:
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
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.
Load PostGIS with OpenStreetMap
for more details on that process.
From there, I created four (4) table extracts in the
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:
The other helper tables included for meta-data and styling.
Layer Styles XML
The data set includes XML data for the
public.layer_styles table used
If the database name is not
you need to update
f_table_catalog accordingly for default styling
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
SET XML OPTION DOCUMENT; workaround.
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.
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!
Published February 29, 2020
Last Updated March 03, 2020