My book Mastering PostGIS and OpenStreetMap is available!

Pre-conference Session Materials: GIS Data, Queries, and Performance

By Ryan Lambert -- Published November 12, 2023

This post supports our full day pre-conference session, PostGIS and PostgreSQL: GIS Data, Queries, and Performance at PASS Data Community Summit 2023 on November 13.

Thank you everyone who participated! This page has been updated with the slide decks used during the session.

Downloads for session

The data, permissions script, and example SQL queries used through this session are available below.

Data downloads

Slides

The following downloads are the PDF versions of the slide decks. These printed with empty pages for some reason I was not able to quickly fix. Also note, there is no "02" slide deck. 🤷

SQL Query Files

Steps to load

The Demo Database (.sql.gz above) can be loaded into the PgOSM Flex Docker image. The following commands run the Docker container.

WARNING: This usage is not expected to support production workloads. This post does not go into those details.

The --cpus and --memory settings limit the container to the specs used for timings throughout the SQL examples.

export POSTGRES_USER=postgres
export POSTGRES_PASSWORD=mysecretpassword

docker pull rustprooflabs/pgosm-flex:h3

docker run --name pgosm -d \
    --cpus=1.2 --memory=1024m \
    -v ~/pgosm-data:/app/output \
    -v /etc/localtime:/etc/localtime:ro \
    -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD \
    -p 5433:5432 -d rustprooflabs/pgosm-flex:h3 \
    -c jit=off \
    -c max_parallel_workers_per_gather=2 \
    -c work_mem=10MB \
    -c shared_preload_libraries='pg_stat_statements' \
    -c track_io_timing=on \
    -c shared_buffers=250MB \
    -c max_wal_senders=0 -c wal_level=minimal \
    -c max_wal_size=10GB \
    -c checkpoint_timeout=300min \
    -c checkpoint_completion_target=0.9 \
    -c random_page_cost=1.1

The above command uses a special h3 image from PgOSM Flex to include the h3-pg extension, not included in the standard image. The normal PgOSM Flex Docker image should work for all but the H3 examples.

Prepare the database and load the data file.

psql -U postgres -d postgres -h localhost -p 5433 -c "CREATE DATABASE pass2023;"

Connect to pass2023 database.

psql -U postgres -d pass2023 -h localhost -p 5433

Run the following queries to setup the pgosm_flex role with appropriate permissions.

Don't forget to change the Password in the CREATE ROLE query!

CREATE ROLE pgosm_flex WITH LOGIN PASSWORD 'mysecretpassword';
GRANT CREATE ON DATABASE pass2023 TO pgosm_flex;
GRANT CREATE ON SCHEMA public TO pgosm_flex;

CREATE ROLE dd_read WITH NOLOGIN;
CREATE ROLE convert_read WITH NOLOGIN;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset TO pgosm_flex;

Exit psql using the \q command. Download the data. Use psql to load to pass2023 database.

wget https://postgis-osm.nyc3.cdn.digitaloceanspaces.com/postgis-precon-pass-washington-osm.sql.gz
gunzip postgis-precon-pass-washington-osm.sql.gz

# 30 - 45 seconds
psql -U postgres -d pass2023 -h localhost -p 5433 \
    -f postgis-precon-pass-washington-osm.sql

Setup permissions for PgDD and Convert extensions.

wget https://postgis-osm.nyc3.cdn.digitaloceanspaces.com/extension-pgosm-flex-perms.sql
psql -U postgres -d pass2023 -h localhost -p 5433 \
    -f extension-pgosm-flex-perms.sql

Summary

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!

By Ryan Lambert
Published November 12, 2023
Last Updated November 14, 2023