Skip to content

Instantly share code, notes, and snippets.

Avatar

Justin Lewis justinlewis

  • TerraFrame
  • Marble, Colorado
View GitHub Profile
@justinlewis
justinlewis / 1_build_spatial_index_all_postgis_tables.py
Created Aug 20, 2013
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.
View 1_build_spatial_index_all_postgis_tables.py
##
## 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
justinlewis / size_all_tables_in_sql_server_db.sql
Created Apr 15, 2013
A query to list all the tables in a SQL Server database with their size in KB and the size of their Indexes.
View size_all_tables_in_sql_server_db.sql
-- 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 / grant_table_privilege_on_all_table_w_psql
Last active Jan 10, 2018
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.
View grant_table_privilege_on_all_table_w_psql
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 / add_geoserver_wfs_w_openlayers.js
Created Mar 5, 2013
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.
View add_geoserver_wfs_w_openlayers.js
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 / postgis2geojson
Created Mar 4, 2013
Export a PostGIS table to GeoJSON format with ogr2ogr.
View postgis2geojson
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 / search_column_name_from_all_tables_in_db.sql
Created Feb 13, 2013
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.
View search_column_name_from_all_tables_in_db.sql
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
justinlewis / backup_pg_db_w_linux_terminal.sh
Last active Dec 12, 2015
Linux terminal command to backup a PostgreSQL database. Can be executed with a cron job.
View backup_pg_db_w_linux_terminal.sh
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
View sqlserver_connect_w_pyodbc.py
#### 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
justinlewis / shape2postgis.py
Created Feb 7, 2013
A simple script to load/replace a PostGIS table from Shapefile with the shp2pgsql utility. Completely deletes and rebuilds the table in the database.
View shape2postgis.py
# ---------------------------------------------------------------------------
#
# Script to copy data from an ESRI Shapefile to PostGIS database
# Requirements:
## shp2pgsql.exe
## psql.exe
## PostgreSQL DB with PostGIS installed
#
# ---------------------------------------------------------------------------
#
@justinlewis
justinlewis / searchFor_text_in_sqlServer_DB.sql
Created Feb 7, 2013
Search an entire SQL Server database for a string. Assumes all tables are owned by dbo.
View searchFor_text_in_sqlServer_DB.sql
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'