justinlewis’s gists

justinlewis / postgis2shp.py

Last active October 10, 2022 09:02

A simple script for converting PostGIS tables to shapefiles with ogr2ogr.

	#### Author: Justin Lewis
	#### This script can be used to export a single pg table to shapefile.
	####
	#### Developed in a linux environment but should require minimal modification to run on other platforms.
	####
	#### Dependancies:
	###### fwtools (gdal > ogr2ogr), PostGIS database, Python, PyGreSQL

	import os, _pg

justinlewis / google_postgis_geocode.py

Created January 11, 2013 19:26

Script that geocodes address data stored in a PostgreSQL (PostGIS) database using Google.

	##
	## Mike Tafel and Justin Lewis
	## 2010/07/26 : updated Dec. 2012
	##
	## Script that geocodes address data stored in a PostgreSQL (PostGIS) database using Google.
	### Preps the db table, reads address data, builds a request url, returns lat/long/precision valuse to the same table,
	### transforms coordinates to 4326 then 2232.
	##
	## Dependancies:
	#### PostGIS database, Google Private Key and Client ID, all the libraries listed in the import below

justinlewis / cleanse_postgis_geometry_columns_table.py

Last active December 11, 2015 00:19

Script for truncating and repopulating the geometry_columns table in a pre PostGIS 2.0 database.

	##
	## Script for truncating and repopulating the geometry_columns table in a pre PostGIS 2.0 database.
	#### This is important because the geometry_columns table often has legacy records where tables have been deleted
	#### but the corresponding geom record was not removed.
	##
	## This could be done as a PostgreSQL function with a trigger as well.
	##
	## Author = Justin Lewis
	##

justinlewis / postgis_cluster_boundary_create.sql

Created January 30, 2013 18:03

Creates polygon boundaries around clusters of polygons. Implemented as a PostGIS function. Inputs include data table, geometry field (built for polygon), unique id, and radius (distance between clustered polygons).

	-- Function: public.polygon_clusters(character varying, character varying, character varying, numeric)

	-- DROP FUNCTION public.polygon_clusters(character varying, character varying, character varying, numeric);

	CREATE OR REPLACE FUNCTION public.polygon_clusters(parcels character varying, geom character varying, gid character varying, radius numeric)
	RETURNS SETOF record AS
	$BODY$
	DECLARE
	lid_new integer;
	dmn_number integer := 1;

justinlewis / postgis_randomise_points.sql

Last active September 13, 2019 09:16

Create a random point geometry within the boundaries of a specified polygon. Implemented as a PostGIS function. Inputs include the_geom (name of input polygon geometry field) and maxiter (max # of iterations to try placing point within the polygon).

	-- Function: babysim_random_jobs_explode(geometry, integer)

	-- DROP FUNCTION babysim_random_jobs_explode(geometry, integer);

	CREATE OR REPLACE FUNCTION babysim_random_jobs_explode(the_geom geometry, maxiter integer DEFAULT 10000)
	RETURNS geometry AS
	$BODY$
	DECLARE
	i INTEGER := 0;
	x0 DOUBLE PRECISION;

justinlewis / searchFor_text_in_sqlServer_DB.sql

Created February 7, 2013 16:58

Search an entire SQL Server database for a string. Assumes all tables are owned by dbo.

	DECLARE
	@search_string VARCHAR(100),
	@table_name SYSNAME,
	@table_id INT,
	@column_name SYSNAME,
	@sql_string VARCHAR(2000)

	SET @search_string = 'PUT THE TEXT HERE YOU WANT TO LOOK FOR'

	DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

justinlewis / shape2postgis.py

Created February 7, 2013 17:48

A simple script to load/replace a PostGIS table from Shapefile with the shp2pgsql utility. Completely deletes and rebuilds the table in the database.

	# ---------------------------------------------------------------------------
	#
	# Script to copy data from an ESRI Shapefile to PostGIS database
	# Requirements:
	## shp2pgsql.exe
	## psql.exe
	## PostgreSQL DB with PostGIS installed
	#
	# ---------------------------------------------------------------------------
	#

justinlewis / sqlserver_connect_w_pyodbc.py

Created February 7, 2013 17:57

Connect to a SQL Server database with pyodbc: http://www.easysoft.com/developer/languages/python/pyodbc.html

	#### Just some notes for the future.

	import pyodbc

	connect = pyodbc.connect('DRIVER={SQL Server};SERVER=your_host;DATABASE=your_database;UID=username;PWD=password')
	cursor = connect.cursor()
	print "Connection to TRIPS database established"

	cursor.execute("select * from YOUR_TABLE;")
	for row in cursor.fetchall():

justinlewis / backup_pg_db_w_linux_terminal.sh

Last active December 12, 2015 06:59

Linux terminal command to backup a PostgreSQL database. Can be executed with a cron job.

PGPASSWORD=your_password pg_dump -i -h your_host -p 5432 -U your_user -F c -b -v -Tc -f /path/to/your/backup.backup your_db_name

justinlewis / search_column_name_from_all_tables_in_db.sql

Created February 13, 2013 21:57

Search for a specific column name from every table in a SQL Server database. Simply change the text after "LIKE" to the name your looking for.

	USE YOUR_DB_NAME
	GO
	SELECT t.name AS table_name,
	SCHEMA_NAME(schema_id) AS schema_name,
	c.name AS column_name
	FROM sys.tables AS t
	INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
	WHERE c.name LIKE '%Level%' -- Change this to the column name your looking for.
	ORDER BY schema_name, table_name;

Justin Lewis justinlewis