Patrick Wilson patdevinwilson

🏠

🌎

Geospatial Professional

patdevinwilson / utah_housing_spatial_demo.sql

Last active February 11, 2022 01:09

spatial analysis using Snowflake SQL demo

	WITH ZIP_FILTER AS (
	SELECT * FROM UTAH_ZIP_CODES
	WHERE NAME != 'MOTOQUA' --ZIPCODE FOR MOTOQUA WAS ADDED BY THE STATE OF UTAH AFTER OUR BASELINE'S VINTAGE
	),
	VALID_REFINE AS (
	SELECT
	LISTING_ID,
	YEAR_BUILT,
	ZIPCODE,
	PRICE,

patdevinwilson / photos.py

Last active August 24, 2020 21:56

Looks up a list of inline photo IDs from a CSV file and downloads photos from Fulcrum API

	import csv
	from fulcrum import Fulcrum
	from fulcrum.exceptions import NotFoundException

	api_key = '[API KEY]'
	fulcrum = Fulcrum(key=api_key)
	photos_list = csv.reader(open('photos.csv'), delimiter=',')

	for row in photos_list:
	# fetch existing record (first column must contain fulcrum_id)

patdevinwilson / Query for null photos

Last active May 16, 2019 13:24

	SELECT
	p.record_id,
	p.photo_id,
	p.latitude,
	p.longitude
	FROM
	photos p
	INNER JOIN "Photos test for Patrick" i ON p.record_id = i._record_id
	WHERE p.latitude IS NULL and p.longitude IS NULL;

patdevinwilson / Fulcrum Video SUM

Created March 8, 2017 22:02

	SELECT
	COUNT(1) AS video_count,
	ROUND((AVG(v.duration)/60)::numeric,2) AS average_duration_minutes,
	ROUND((MIN(v.duration)/60)::numeric,2) AS min_duration_minutes,
	ROUND((MAX(v.duration)/60)::numeric,2) AS max_duration_minutes,
	ROUND(SUM(v.duration)::numeric,2) AS total_duration_seconds,
	ROUND((SUM(v.duration)/60)::numeric,2) AS total_duration_minutes,
	ROUND(((SUM(v.duration)/60)/60)::numeric,2) AS total_duration_hours,
	ROUND(AVG(ST_Length(v.geometry::geography))::numeric,2) AS average_track_length_meters,
	ROUND(MIN(ST_Length(v.geometry::geography))::numeric,2) AS min_track_length_meters,

patdevinwilson / gist:9827653589bed0ba149d

Created March 15, 2016 20:25

https://web.fulcrumapp.com/shares/a03ee04e5a2b3dfa.geojson

patdevinwilson / rotate_video.rb

Created December 1, 2015 21:18

Uses FFMPEG to batch rotate portrait locked

	#!/usr/bin/env ruby

	puts "Starting.."

	Dir.glob("./*.mp4").each do \|filename\|
	puts "Rotating Video: #{filename}"
	puts `mkdir "#{File.join( File.dirname(filename), "mp4")}"`
	newfilename = File.join(
	File.dirname(filename),
	"mp4",

patdevinwilson / fulcrum-video-download.py

Created July 21, 2015 16:45

It will prompt you for your API key and the form ID

	import sys
	import urllib2
	from fulcrum import Fulcrum

	api_key = raw_input('api_key:')
	form_id = raw_input('form_id:')

	if api_key == '' or api_key == 'your_api_key' or form_id == '' or form_id == 'your_form_id':
	sys.exit('api_key and form_id are required!')

patdevinwilson / gist:3947862

Created October 24, 2012 18:23

Generating Centroids From planet_osm_polygon buildings


	## Creates osm-florida database and initializes postgis
	osm2pgsql -c -d osm-florida florida.osm.bz2 -H localhost -P 5432 -U postgres

	## Adds a new point geometry column in WGS_84
	SELECT AddGeometryColumn ('public','planet_osm_polygon', 'geom', '4326', 'POINT', 2)

	## Populates the new 'geom' column with centroids of planet_osm_polygon in WGS84
	UPDATE planet_osm_polygon set geom = st_transform(st_centroid(way), 4326)

patdevinwilson / gist:2408726

Created April 17, 2012 20:16 — forked from zhm/gist:1975726

	Building GDAL 1.9 with MDB (PGeo) support on OS X Lion

	- Download and install the "Java for Mac OS X 10.7 Update 1 Developer Package" from https://developer.apple.com/downloads/index.action

	- Download jackcess-1.2.6.jar from http://sourceforge.net/projects/jackcess/files/jackcess/1.2.6/jackcess-1.2.6.jar/download

	- Download http://mdb-sqlite.googlecode.com/files/mdb-sqlite-1.0.2.tar.bz2 to get commons-lang-2.4.jar and commons-logging-1.1.1.jar. They will go alongside jackcess in the $CLASSPATH after it builds.

	- In the GDAL source root, edit the configure.in and replace it with https://gist.github.com/1975654