justinlewis

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

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

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

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

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

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

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

justinlewis

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

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

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;