Skip to content

Instantly share code, notes, and snippets.

View justinlewis's full-sized avatar

Justin Lewis justinlewis

34 followers · 26 following
  • TerraFrame
  • Marble, Colorado
View GitHub Profile
@justinlewis
justinlewis / postgis2geojson
Created March 4, 2013 18:28
Export a PostGIS table to GeoJSON format with ogr2ogr.
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
justinlewis / add_geoserver_wfs_w_openlayers.js
Created March 5, 2013 23:00
Add a simple WFS layers to an OpenLayers map from a Geoserver WFS service. Standard approach to getting around the browsers same origin policy with JavaScript.
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
justinlewis / grant_table_privilege_on_all_table_w_psql
Last active January 10, 2018 22:22
A simple function and some commands to grant privileges to every table in a PostgreSQL database. * each file is a different method. You don't need need to use them all in conjunction.
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
justinlewis / size_all_tables_in_sql_server_db.sql
Created April 15, 2013 20:09
A query to list all the tables in a SQL Server database with their size in KB and the size of their Indexes.
-- 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
justinlewis / 1_build_spatial_index_all_postgis_tables.py
Created August 20, 2013 22:09
Script for building spatial indexes on tables that do not have them already in a PostGIS database. Attempts to build spatial indexes for all geometry fields in the db.
##
## 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.