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

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

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

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

## bigquery_GeoJSON.sql
UPDATE
  `project.dataset.table`
SET
  geom = ST_GEOGFROMGEOJSON(
    JSON_QUERY(geo_json,'$.geometry'), make_valid => TRUE)
WHERE
  geom IS NULL

## ndjson.sh
# NB. First install jq using homebrew
cat original.geojson | jq -c '.[]' > new_line_delimited.json
	-- 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
	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)
	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;
	# 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
	UPDATE
	`project.dataset.table`
	SET
	geom = ST_GEOGFROMGEOJSON(
	JSON_QUERY(geo_json,'$.geometry'), make_valid => TRUE)
	WHERE
	geom IS NULL
	# NB. First install jq using homebrew
	cat original.geojson \| jq -c '.[]' > new_line_delimited.json