Skip to content

Instantly share code, notes, and snippets.

Paul Ramsey pramsey

Block or report user

Report or block pramsey

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@pramsey
pramsey / scotus2txt.pl
Last active Aug 29, 2015
Brittle perl for parsing SCOTUS coordinates into WKT
View scotus2txt.pl
use Switch;
# Download the SCOTUS decision from here
# http://www.supremecourt.gov/opinions/14pdf/no5orig_d18e.pdf
# Convert it from PDF to a raw text form using this service
# http://convertonlinefree.com/PDFToTXTEN.aspx
# Then run this script on it
@pramsey
pramsey / README.md
Last active Aug 29, 2015
Placenames Heatmap
View README.md

About

There are over 2 million place names defined for the United States at geonames.org, and they contain interesting patterns if you look at the right ones. For example:

  • Lots of gold names out in the West from the gold rush era. Also check out uranium, coal and oil.
  • Cougar live mostly in the Pacific North West (but there are mountain lions elsewhere and even catamounts).
  • German immigrants heavily settled the Midwest. Check out the Swedish and Danish too.
  • The Cherokee nation was originally established around Tennessee, but they were driven into Oklahoma on the Trail of Tears. The Navaho and Pawnee have clear regions too.
  • Regional place topologies, like canyon, bayou, brook and lac are fun history lessons (think about the languages they are derived from).
@pramsey
pramsey / README.md
Last active Aug 29, 2015
Carnac the Magnificent
View README.md

About

Carnac the Magnificent will predict the likelihood of rain anywhere in the contiguous United States!

How it Works

Getting the Data

Every 6 hours, my home computer retrieves the latest forecast data from NOAA, in particular the ds.pop12.bin precipitation forecast.

@pramsey
pramsey / photo-assets.sql
Created Feb 25, 2015
SQL Example for Finding Photos of Assets
View photo-assets.sql
--
-- Simple function that uses a circular "radius of view" around the camera
-- bearing/azimuth. A more complex function w/ a rectangular frustrum could
-- also be written w/o too much pain.
--
CREATE OR REPLACE FUNCTION ST_InPhoto(pt geometry, camera geometry, bearing real, azimuth real, camera_field real default 30)
RETURNS boolean AS
$$
DECLARE
x_c real;
@pramsey
pramsey / README.md
Last active Aug 29, 2015
Cebu Taxi Tracks
View README.md

About

Working from a table of 50M timestamped taxi location records...

@pramsey
pramsey / README.md
Last active Aug 29, 2015
New York City Homicides
View README.md

About

Almost 4000 homicides took place in New York City between 2003 and 2011. They were not uniformly distributed. The base map shows sensus blocks with % black population as the variable being colored.

How it Works

The dots are controlled by the slider bar at the top. Each time the slider stops, the map is updated with a new SQL query showing just the homicides that took place in census blocks wiht more than the indicated % of white population, and the total number of homicides is updated to reflect the threshold %.

View gist:9f66327becaa729aabb3
select c1.relname as tbl, r.conname as constraint, c2.relname as frn_tbl
from pg_class c1
join pg_constraint r on c1.oid = r.conrelid
join pg_class c2 on c2.oid = r.confrelid
order by frn_tbl;
@pramsey
pramsey / readme.md
Last active Oct 8, 2015
PostgreSQL FDW w/ PostGIS Support
View readme.md

FDW w/ PostGIS

The PostgreSQL native FDW (foreign data wrapper), postgres_fdw allows you to access tables from remote PostgreSQL servers very transparently, even doing thoughtful things like pushing restrictions to the remote server to reduce the amount of data transferred and ensure work is done close to the data.

Out of the box, the standard PostgreSQL FDW also allows PostGIS geometry to transit from remote to local hosts, which is pretty cool.

However, it will not push spatial restrictions from the local host to the remote host, only restrictions that relate to built-in types. I've done a rough and ready patch to the 9.4 branch to allow spatial restrictions to pass over FDW, and it is available here:

View bd-pr.sql
-- place data in s3 for consumption by redshift
-- unzip -p taxi.zip | perl strip.pl | gzip -9 > taxi.gz
-- aws s3 cp taxi.gz s3://taxitest/taxi.gz
CREATE TABLE taxis (
pickup_datetime varchar,
passenger_count integer,
trip_time_in_secs real,
trip_distance real,
pickup_longitude real,
@pramsey
pramsey / mercator.c
Last active Oct 9, 2015
Mercator Hash Key
View mercator.c
#include <stdio.h>
#include <stdlib.h>
#include <math.h>
static char *base16 = "0123456789abcdef";
char *mercator_hash(double x, double y, int precision)
{
double quad_width = 20037508.34;
double cx = 0.0;
You can’t perform that action at this time.