Upgrade to Pro — share decks privately, control downloads, hide ads and more …

General Assembly 2014

General Assembly 2014

Guest lecture for General Assembly data science course. June 2014

Andrew W Hill

June 03, 2014
Tweet

More Decks by Andrew W Hill

Other Decks in Technology

Transcript

  1. you Your team your boss Your follower The amount that

    other people know about your work
  2. “i found your hammer WITH RECURSIVE dims AS ( SELECT

    2*sqrt(sum(ST_Area(the_geom))) as d, sqrt(sum(ST_Area(the_geom)))/20 as w, count(*) as rows FROM osm_export_polygon WHERE the_geom IS NOT NULL), geoms AS ( SELECT the_geom, cartodb_id, ST_YMax(the_geom)-ST_YMin(the_geom) as height FROM osm_export_polygon WHERE the_geom IS NOT NULL ORDER BY ST_YMax(the_geom)-ST_YMin(the_geom) DESC), geomval AS ( SELECT the_geom, cartodb_id, row_number() OVER (ORDER BY height DESC) as id from geoms), positions(cartodb_id, the_geom,x_offset,y_offset,new_row,row_offset) AS ( (SELECT cartodb_id, the_geom, 0.0::float, 0.0::float, FALSE, 2 from geomval limit 1) UNION ALL (SELECT (SELECT cartodb_id FROM geomval WHERE id = p.row_offset), (SELECT the_geom FROM geomval WHERE id = p.row_offset), CASE WHEN p.x_offset < s.d THEN (SELECT (s.w+(ST_XMax(the_geom) - ST_XMin(the_geom)))+p.x_offset FROM geomval WHERE id = p.row_offset) ELSE 0 END as x_offset, CASE WHEN p.x_offset < s.d THEN p.y_offset ELSE (SELECT (s.w+(ST_YMax(the_geom) - ST_YMin(the_geom)))+p.y_offset FROM geomval WHERE id = p.row_offset) END as y_offset , FALSE, p.row_offset+1 FROM positions p, dims s WHERE p.row_offset < s.rows ) ), sfact AS ( SELECT ST_XMin(the_geom) as x, ST_YMax(the_geom) as y FROM geomval LIMIT 1 ) SELECT ST_Transform(ST_Translate( the_geom, (x - ST_XMin(the_geom) - x_offset), (y - ST_YMin(the_geom) - y_offset)),3857) as the_geom_webmercator, cartodb_id FROM positions,sfact order by row_offset asc
  3. WITH  hgrid              AS  (SELECT

     Cdb_rectanglegrid(Cdb_xyz_extent(8,  12,  5),                                    Cdb_xyz_resolution(5)  *  4,                                                          Cdb_xyz_resolution(5)  *  4)  AS  cell)     SELECT  x,                  y,                  Array_agg(c)  vals,                  Array_agg(d)  dates     FROM      (SELECT  St_xmax(hgrid.cell)                                                                                        x,                                  St_ymax(hgrid.cell)                                                                                        y,                                  Count(i.cartodb_id)                                                                                        c,                                  Floor((  Date_part('epoch',  built)  -­‐  -­‐10418716800  )  /  32837875)  d                    FROM      hgrid,                                  us_po_offices  i                    WHERE    St_intersects(i.the_geom_webmercator,  hgrid.cell)                    GROUP    BY  hgrid.cell,                                        Floor((  Date_part('epoch',  built)  -­‐  -­‐10418716800  )  /  32837875)                  )  f     GROUP    BY  x,                        y
  4. WHAT ARE OTHERS DOING WITH IT? A QUICK LOOK AT

    HOW OTHERS ARE USING CARTODB TO COMMUNICATE DATA