pramsey

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

    CREATE TABLE lines ( 
      gid integer primary key, 
      geom geometry(Linestring, 4326)
    );

    INSERT INTO lines VALUES (1, 'SRID=4326;LINESTRING(1 1, 2 2, 3 3, 4 4)');
    INSERT INTO lines VALUES (2, 'SRID=4326;LINESTRING(0 1, 0 2, 0 3, 0 4)');

    WITH 
    dumps AS ( 

pramsey

--
-- 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

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 %.

pramsey

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

#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;

pramsey

-- 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

-- 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

DROP TABLE taxis;
CREATE TABLE taxis (
 pickup_datetime varchar,
 passenger_count integer,
 trip_time_in_secs real,
 trip_distance real,

pramsey

{"create":{"_index":"taxi","_type":"pickup"}}
{"pickup_datetime":"2010-01-31 23:47:21","passenger_count":1,"trip_time_in_secs":20.33,"trip_distance":6.7,"pickup_location":"dr5ruddw35ry3"}
{"create":{"_index":"taxi","_type":"pickup"}}
{"pickup_datetime":"2010-01-31 23:47:22","passenger_count":1,"trip_time_in_secs":3.63,"trip_distance":1.2,"pickup_location":"dr5revvq6sjee"}
{"create":{"_index":"taxi","_type":"pickup"}}
{"pickup_datetime":"2010-01-31 23:47:22","passenger_count":1,"trip_time_in_secs":15.5,"trip_distance":4,"pickup_location":"dr5rshw6zmcdb"}
{"create":{"_index":"taxi","_type":"pickup"}}
{"pickup_datetime":"2010-01-31 23:47:22","passenger_count":2,"trip_time_in_secs":28.87,"trip_distance":20.5,"pickup_location":"dr5x0yywkqyps"}
{"create":{"_index":"taxi","_type":"pickup"}}
{"pickup_datetime":"2010-01-31 23:47:23","passenger_count":3,"trip_time_in_secs":15.84,"trip_distance":6.4,"pickup_location":"dr5ru3bh473z0"}

pramsey

--------------------------------------------------------------------------------
-- start with a 14M record table
--
taxi=# select count(*) from taxis ;
  count   
----------
 14863748
(1 row)

Time: 2730.094 ms