justinlewis

ogr2ogr -f "GeoJSON" /PATH/TO/NEWFILE.json PG:"host=YOUR_HOST dbname=YOUR_DB user=YOUR_USER password=YOUR_PASS port=5432" "YOUR_POSTGIS_TABLE(the_geom)"

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

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

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

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

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

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

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

   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

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;