bitner’s gists

bitner / gist:1248501

Created September 28, 2011 17:04

KNN GIST query

	select
	parcel_id,
	address,
	st_distance(geom,'SRID=3005;POINT(1011102 450541)') as distance
	from
	parcels
	where
	geom && (
	select
	st_extent(geom)

bitner / everynseconds.sql

Created April 5, 2012 15:34

PostgreSQL/PostGIS PL Functions for dealing with 4D Track Data

	CREATE OR REPLACE FUNCTION everynseconds(geometry, integer)
	RETURNS geometry AS
	$BODY$
	SELECT
	st_makeline(st_locate_along_measure($1,g))
	FROM
	generate_series(0,floor(st_m(st_endpoint($1)))::int,$2) g;
	$BODY$
	LANGUAGE sql;

bitner / plpython - geopy geocode

Last active August 8, 2020 14:29

	CREATE OR REPLACE FUNCTION public.geocode_google(IN inaddress text, OUT address text, OUT longitude double precision, OUT latitude double precision)
	RETURNS record AS
	$BODY$
	from geopy.geocoders import GoogleV3
	geolocator = GoogleV3()
	try:
	address, (latitude, longitude) = geolocator.geocode(inaddress,timeout=1,exactly_one=True)
	return address, longitude, latitude
	except:
	return None, None, None

bitner / PostGISday

Created November 20, 2014 15:17

SELECT 'MULTIPOLYGON(((-2.28716645489199 1.04616687844134,-2.46505717916137 1.2579415501906,-2.38034731046167 1.47395171537484,-2.17704362558238 1.55866158407454,-1.88055908513342 1.42312579415502,-1.87208809826345 1.17323168149089,-2.18127911901737 0.982634476916561,-2.26175349428208 0.766624311732317,-2.09233375688268 0.631088521812791,-1.79161372299873 0.796272765777213,-1.55866158407454 0.732740364252435,-1.43159678102499 0.550614146548073,-1.43583227445997 0.461668784413384,-1.53748411689962 0.436255823803473,-1.32147395171537 0.321897501058873,-1.13087674714104 0.0720033883947477,-1.06310885218128 -0.23718763235917,-0.910631088521813 -0.203303684879289,-1.43159678102499 -0.309191020753918,-1.69419737399407 -0.309191020753918,-1.71113934773401 -0.249894112664126,-1.8297331639136 -0.330368487928844,-2.01609487505294 -0.262600592969081,-1.97373994070309 -0.207539178314274,-2.14739517153748 -0.190597204574333,-2.00762388818297 0.448962304108429,-2.13468869123253 0.470139771283354,-2.33375688267683 0.7115628

bitner / gist:1f3d1c755eff4d70b485

Created February 6, 2015 22:30

	WITH t AS (
	SELECT id, (SELECT min(val), max(val) FROM mytable WHERE st_dwithin(geom,a.geom,16093)) b,geom FROM mytable a
	)
	SELECT id, (b).min, (b).max, geom FROM t

bitner / gist:7463839ea4e40e8f3605

Created February 17, 2015 20:35

	bitner@dbspatialtest:/var/www/iDtest/iD$ make
	cat css/reset.css css/map.css css/app.css css/feature-icons.css > dist/iD.css
	node build.js
	/var/www/iDtest/iD/node_modules/d3/node_modules/jsdom/lib/jsdom/level1/core.js:432
	set nodeName() { throw new core.DOMException();},
	^
	SyntaxError: Unexpected token )
	at exports.runInThisContext (vm.js:73:16)
	at Module._compile (module.js:443:25)
	at Object.Module._extensions..js (module.js:478:10)

bitner / pl elevation functions

Created June 14, 2016 16:35

	CREATE OR REPLACE FUNCTION public.dbs_ned_z(
	IN text,
	IN text,
	OUT text)
	RETURNS text AS
	$BODY$
	#!/bin/sh
	url="http://ned.usgs.gov/epqs/pqs.php?output=json&x=$1&y=$2"; /usr/bin/curl -s $url
	$BODY$
	LANGUAGE plsh VOLATILE

bitner / gist:a252a00df5920213445be18d6b44d1e1

Created July 14, 2016 17:58

	==> django-osgeo-importer: self.distribution.run_command(command)
	==> django-osgeo-importer:
	==> django-osgeo-importer: File "/usr/lib/python2.7/distutils/dist.py", line 972, in run_command
	==> django-osgeo-importer:
	==> django-osgeo-importer: cmd_obj.run()
	==> django-osgeo-importer:
	==> django-osgeo-importer: File "/tmp/pip_build_root/numpy/numpy/distutils/command/build_src.py", line 147, in run
	==> django-osgeo-importer:
	==> django-osgeo-importer: self.build_sources()
	==> django-osgeo-importer:

bitner / Fun with time

Last active February 22, 2017 08:50

	-- Convenience Functions
	CREATE OR REPLACE FUNCTION to_epoch(IN timestamptz, OUT epoch float8) AS
	$$ SELECT extract(epoch from $1) $$ LANGUAGE SQL;
	CREATE OR REPLACE FUNCTION ts_at_tod(IN timestamptz, IN timetz, OUT timestamptz) AS
	$$ SELECT date($1) + $2 $$ LANGUAGE SQL;

	-- Get list of target time between start and end
	CREATE OR REPLACE FUNCTION times_between(IN range_start timestamptz, IN range_end timestamptz, IN t timetz)
	RETURNS setof timestamptz AS $$
	SELECT * FROM

bitner / phenomenagrid.sql

Created February 12, 2020 18:43

Create a grid to aggregate phenomena by.

	-- Procedural Function that takes in an extent and a cellsize (in extent srid units)
	-- And creates a grid bounding that extent with given cellsize with an origin that is in
	-- a multiple of the cellsize + zero
	CREATE OR REPLACE FUNCTION ST_GRID(
	IN _extent geometry,
	IN _cellsize float8,
	OUT id bigint,
	OUT r integer,
	OUT c integer,
	OUT geom geometry

David Bitner bitner