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

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

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

justinlewis

   var tipProjects = new OpenLayers.Layer.Vector("TIP Projects 2012 - 2017", {
         strategies: [new OpenLayers.Strategy.BBOX()],
         protocol: new OpenLayers.Protocol.Script({
             url: "http://gis.drcog.org/geoserver/wfs",
             callbackKey: "format_options",
             callbackPrefix: "callback:",
             params: {
                 service: "WFS",
                 version: "1.1.0",
                 srsName: "EPSG:4326",

justinlewis

-- Measures tables size (in kilobytes)

declare @t table (
name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)
)
declare @name nvarchar(100)

declare tt cursor for
Select name from sys.tables
open tt

justinlewis

##
## Script for building spatial indexes on tables that do not have them already.
## Attempts to build spatial indexes for all geometry fields in the db.
##
## Uses the PyGreSQL module
##
## Future Improvements:
#### add some handling of existing indexes to possibly delete them and rebuild them with this naming convention
#### rather than just building a list of tables that have 1 or more indexes this should determine what fields for a specific table have an index (or dont) and handle them.
###### The current script would not catch a second geometry field in a table that has a single index because it would not be added to the index_list.

justinlewis

Use the commands below to set privileges using the PSQL terminal commands.  

TABLES:
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

SEQUENCES:
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

VIEWS:
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done