Guest presentation at the Cartography Lab

UW Madison

Transcript

1. hello cartodb a half-day long adventure at the UW-Madison CArtography lab @andrewxhill
2. the plan CartoDB? Andrew Hill? dive in
7. seeing is believing data visualization is a powerful tool communicate complex data
8. maps for vizzuality it often started with
18. sql API
19. dynamic
23. cartodb a quick look at how people are using it
38. the plan CartoDB? Andrew Hill? dive in
39. how did i end up here today? a bit of background
40. biologist.
41. developer.
42. mapper.
48. i like to learn by doing things the wrong way
49. “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
54. 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
55. 1 10 100 1000 3mb 70mb 300mb 1.5 2 1.2 300 70 3 Raw Datacube
61. i make maps so that i can share the feelings of discover
62. You probably got a little excited, humored, or something when you noticed a ‘y’ missing in discovery! SEE!
63. c4a.me/1dYHKv9/ PLUTO is Free!
75. what are the implications of new forms OF online mapping?
76. how can we tell stories BETTER with maps?
77. what comes next for maps?
78. the plan CartoDB? Andrew Hill? dive in
79. data
80. basics of cartocss and sql http://bit.ly/1d0k8K9
81. development
84. the plan CartoDB? Andrew Hill? Workshop
85. thank you!