wriglz’s gists

wriglz / rename_multiple_columns.sql

Created December 5, 2018 16:25

Rename columns across multiple tables in the same schema in Postgres

	-- Call function with the schema name
	CREATE OR REPLACE FUNCTION rename_columns(_schema text) RETURNS VOID AS $func$
	DECLARE
	rec RECORD;
	table RECORD;
	result RECORD;
	sql TEXT := '';
	i INTEGER;

	BEGIN

wriglz / drop_all_connections.sql

Last active January 9, 2020 14:41

Drop all connections to a Postgres database so you can drop the DB. Make sure you are not currently connected to the database that you wish to drop!

	SELECT pg_terminate_backend(pg_stat_activity.pid)
	FROM pg_stat_activity
	WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
	AND pid <> pg_backend_pid();

wriglz / delete_lines_from_txt_file_containing_specific_string.sh

Last active January 22, 2020 16:57

Remove lines from a text file containing a specific string

	# Directly modify file & create a backup
	sed -i.bak '/pattern to match/d' ./input_file

	# Directly modify file - no backup
	sed -i '' '/pattern to match/d' ./infile

wriglz / find_duplicate_rows_postgres.sql

Last active January 24, 2020 14:30

	WITH data AS
	(SELECT column_to_test,
	count(*) OVER (PARTITION BY column_to_test), other_columns_to_output
	FROM schema.table )
	SELECT *
	FROM data
	WHERE count > 1
	ORDER BY column_to_test;

wriglz / delete_duplicate_rows_based_on_ctid.sql

Created January 22, 2020 16:59

Replace schema.table and duplicate_field.

	DELETE
	FROM schema.table a USING
	(SELECT MIN(ctid) AS ctid,
	duplicate_field
	FROM schema.table
	GROUP BY duplicate_field
	HAVING COUNT(*) > 1) b
	WHERE a.duplicate_field = b.duplicate_field
	AND a.ctid <> b.ctid;

wriglz / merge_shapefiles.py

Last active January 24, 2020 09:38

	from pathlib import Path
	import pandas
	import geopandas

	folder = Path("/path/to/shapefile/folder")
	shapefiles = folder.glob("shapefile_name_with_wildcards_*.shp")
	gdf = pandas.concat([
	geopandas.read_file(shp)
	for shp in shapefiles
	]).pipe(geopandas.GeoDataFrame)

wriglz / remove_old_git_branches

Created February 11, 2020 11:00

	# Remove all merged, closed branches from your local machine, leaving unmerged branches in place

	git remote prune origin

	git branch -vv \| grep 'origin/.*: gone]' \| awk '{print $1}' \| xargs git branch -d

wriglz / OSMnx_requirements.txt

Created March 10, 2020 14:42

wriglz / london_pub_count.py

Last active October 14, 2021 01:39

	import pandas as pd
	import geopandas as gpd
	from shapely import wkt
	import time

	# Read pub points from CSV to a pandas dataframe
	pubs = pd.read_csv('~/Documents/pub_points.csv',
	header=None, names=['name', 'coordinates'])

	# Parse the WKT coordinate format from PostGIS

wriglz / snap_points_to_lines.sql

Last active September 1, 2022 04:21

SQL to snap points to the closest line within a predefined radius

	-- Snap the points to their closest lines, found in the subquery below
	SELECT
	point_id,
	line_id,
	ST_LINE_INTERPOLATE_POINT(line_geom,
	ST_Line_Locate_Point(line_geom, point_geom)) AS snapped_points --Create the snapped points
	FROM

	--Subquery to find the closest line to each point (within a pre-defined raidus)
	(

Simon Wrigley wriglz