Simon Wrigley wriglz

## rename_multiple_columns.sql
-- 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

## drop_all_connections.sql
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();

## delete_lines_from_txt_file_containing_specific_string.sh
# 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

## find_duplicate_rows_postgres.sql
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;

## delete_duplicate_rows_based_on_ctid.sql
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;

## merge_shapefiles.py
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)

## remove_old_git_branches
# 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

## OSMnx_requirements.txt
networkx
geopandas
pillow
matplotlib

## london_pub_count.py
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

## snap_points_to_lines.sql
-- 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)
(
	-- 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
	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();
	# 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
	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;
	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;
	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)
	# 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
	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
	-- 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)
	(