Ryan Lambert rustprooflabs

PostgreSQL is my database of choice; skydiving is my adrenaline rush of choice!

rustprooflabs / db.py

Created October 29, 2019 17:06

Python and Postgres - psycopg2 quick example

	import getpass
	import psycopg2
	import psycopg2.extras


	def select_one(sql_raw, params):
	""" Runs SELECT query that will return zero or 1 rows. `params` is required."""
	return _execute_query(sql_raw, params, 'sel_single')

rustprooflabs / srid_units.sql

Last active May 18, 2021 17:32

Creates view in PostGIS enabled database to make it easier to find what units each SRID is in.

	CREATE OR REPLACE VIEW public.srid_units AS
	SELECT srid, CASE WHEN proj4text LIKE '%+units=%' THEN True
	ELSE False
	END AS units_set,
	CASE WHEN proj4text LIKE '%+units=m%' THEN 'Meters'
	WHEN proj4text LIKE '%+units=ft%' THEN 'Feet'
	WHEN proj4text LIKE '%+units=us-ft%' THEN 'Feet'
	WHEN proj4text LIKE '%+units=link%'
	OR proj4text LIKE '%+units=%'
	THEN 'Set, not caught properly'

rustprooflabs / fdw_us_state_bbox.sql

Last active February 22, 2021 17:16

Load US state bounding boxes from CSV - Source: https://anthonylouisdagostino.com/bounding-boxes-for-all-us-states/


	CREATE EXTENSION IF NOT EXISTS file_fdw;
	CREATE EXTENSION IF NOT EXISTS postgis;

	CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw;

	CREATE FOREIGN TABLE public.fdw_us_state_bbox
	(
	id INT,
	fips TEXT,

rustprooflabs / example-partition-pruning-not-happening.sql

Created February 24, 2021 14:48

Minimal test case illustrating example where partition pruning is not happening. Simplified example to better test findings for Query 2 on this post https://blog.rustprooflabs.com/2021/02/postgres-partition-openstreetmap-road-v1-review

	DROP TABLE IF EXISTS p_group;
	CREATE TABLE p_group
	(
	id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	osm_date DATE NOT NULL,
	region TEXT NOT NULL,
	CONSTRAINT uq_p_group UNIQUE (osm_date, region)
	);

	INSERT INTO p_group (osm_date, region)