bruth

Postgres push triggers

Watch a table for changes and push a notification with a payload describing the change.

Example

In the Postgres shell:

-- Create the functions

pramsey

CREATE OR REPLACE FUNCTION public.CDB_Delayed_TableMetadata_Trigger() 
RETURNS trigger AS $$ 
  DECLARE upd timestamp;
BEGIN 
  IF TG_RELID = 'cartodb.CDB_TableMetadata'::regclass::oid THEN 
    RETURN NULL;
  END IF;
  SELECT updated_at INTO upd 
    FROM cartodb.CDB_TableMetadata WHERE tabname = TG_RELID::regclass;
  IF upd IS NOT NULL AND (now() - upd) > '20m'::interval THEN 

andy-esch

Election Mapping Bonanza

With Mamata Akella (@mamataakella) and Andy Eschbacher (@MrEPhysics)

Presentations here

Part 1: Importing our Datasets

We are going to be using a dataset of 2012 Presidential Election Results from Data.gov. To make it easier tonight, we made a simplified version.

andrewxhill

javisantana

WITH deltas as (
   SELECT 
  	st_distance(the_geom::geography, lag(the_geom::geography, 1) over(order by timestamp)) as ddist,
    timestamp - lag(timestamp, 1) over(order by timestamp) as dt 
   from out_2 offset 1000
)
  
select avg(ddist/dt) as speed from deltas

auremoser

Hasadna: Pollution Mapping + CartoDB

Aurelia Moser, CartoDB Workshop - Hasadna, IL

July 6, 2015

Find this document here:

• Stackedit:

andrewxhill

--
-- Create a table in your CartoDB editor called 'version_control'
-- Run the following SQL in your editor to create the needed columns
--

ALTER TABLE version_control ADD COLUMN data json;
ALTER TABLE version_control ADD COLUMN source_id integer;
ALTER TABLE version_control ADD COLUMN table_name text;
ALTER TABLE version_control ADD COLUMN tg_op text;

chriswhong

//node script for generating complex cartoCSS for torque
//as value increases, duration of marker gets longer, 
//radius gets larger, and color moves up the ramp.

var breaks = [0,10,50,100]
var colors = ['#eff3ff','#bdd7e7','#6baed6','#2171b5'];

var data = {
  "tableName":"#nepaltrends",
  "valueCondition":0,

danicarrion

This script reads a table fom CartoDB using the SQL API, geocodes one of its fields using Google's geocoder and updates the data on CartoDB.

If no valid credentials are provided for Google's geocoder, the free limits will apply.

TODO: multithread support

chriswhong

//Logs current map center and zoom level for use in map init options
//Use it to find that perfect intial view without having to use trial and error
map.on('dragend',getInit).on('zoomend',getInit);

function getInit() {
  var c = map.getCenter();
  console.log('center: [' + c.lat + ',' + c.lng + '],\nzoom: ' + map.getZoom());
}