Joshua Brooks joshbrooks

Extracting data from OpenStreetMap into a Postgres db

1. Download PNG data

wget https://download.geofabrik.de/australia-oceania/papua-new-guinea-latest.osm.pbf

2. Clone the openstreetmap-carto dir

git clone git://github.com/gravitystorm/openstreetmap-carto.git --depth 1

Making Use of Topology

This runs on a restored dird_db from a Docker container, postgis:postgis/14-3.2; I run this as docker run -P ghcr.io/joshbrooks/openly_dird_prod

Pervious versions of postgis run into trouble on the topology building

First create the SimplifyEdgeGeom function: (https://gist.github.com/leplatrem/5729022) This wraps ST_ChangeEdgeGeom in error handling in cases of ugliness

Using pg_basebackup and Docker

Welcome to Tutorial Tuesday 🎉

This is a quick intro to running a database dump made using pg_basebackup with Docker. It's intended audience is developers who are interested in exploring a database from a server, perhaps for development, debugging or forensics.

It covers:

Running pg_basebackup
RSyncing this to your local machine
Configuring the backup to run with required files and setting a password

	# Create and Enter your venv (if not already done)

	python -m venv env
	. env/bin/activate

	# Install playwright

	pip install playwright

	# Install the chromium browser runner

	## apt installs:

	git
	gcc
	pkg-config


	## Install pyenv

	Following [pyenv-installer](https://github.com/pyenv/pyenv-installer)

	SERVER=partisipa-staging.catalpa.build
	DATABASE=partisipa_db
	ssh_server := ssh root@${SERVER}

	fetchdatabase :
	$(ssh_server) rm -rf /tmp/pg_data_$(shell date -I)_$(shell hostname)
	$(ssh_server) pg_dump --compress=0 --format=directory postgresql:///$(DATABASE) -f /tmp/pg_data_$(shell date -I)_$(shell hostname)
	rsync --archive --compress --del root@${SERVER}:/tmp/pg_data_$(shell date -I)_$(shell hostname)/ ./source/ \|\| true
	$(ssh_server) rm -rf /tmp/pg_data_$(shell date -I)_$(shell hostname)