CartoDB + OpenStreetMap

Slide 1

Slide 1 text

Analyzing OpenStreetMap using CartoDB Sunday, October 14, 12

Slide 2

Slide 2 text

Senior scientist and developer @vizzuality Find me @andrewxhill Sunday, October 14, 12

Slide 3

Slide 3 text

User, developer, and pusher of limits @cartodb Find me @andrewxhill Sunday, October 14, 12

Slide 4

Slide 4 text

blog.cartodb.com Sunday, October 14, 12

Slide 5

Slide 5 text

So tell me already, What is CartoDB? Sunday, October 14, 12

Slide 6

Slide 6 text

Sunday, October 14, 12

Slide 7

Slide 7 text

Sunday, October 14, 12

Slide 8

Slide 8 text

Sunday, October 14, 12

Slide 9

Slide 9 text

Sunday, October 14, 12

Slide 10

Slide 10 text

Sunday, October 14, 12

Slide 11

Slide 11 text

Sunday, October 14, 12

Slide 12

Slide 12 text

Sunday, October 14, 12

Slide 13

Slide 13 text

Sunday, October 14, 12

Slide 14

Slide 14 text

Sunday, October 14, 12

Slide 15

Slide 15 text

CartoDB is a tool to build applications and master your geospatial data Sunday, October 14, 12

Slide 16

Slide 16 text

Sunday, October 14, 12

Slide 17

Slide 17 text

Sunday, October 14, 12

Slide 18

Slide 18 text

Sunday, October 14, 12

Slide 19

Slide 19 text

Sunday, October 14, 12

Slide 20

Slide 20 text

Sunday, October 14, 12

Slide 21

Slide 21 text

Sunday, October 14, 12

Slide 22

Slide 22 text

Importing OSM data into CartoDB it’s sooooo easy Sunday, October 14, 12

Slide 23

Slide 23 text

We support directly importing OSM and other formats too! shp, csv, kml, etc Sunday, October 14, 12

Slide 24

Slide 24 text

CartoDB uses osm2pgsql it keeps it simple Sunday, October 14, 12

Slide 25

Slide 25 text

Sunday, October 14, 12

Slide 26

Slide 26 text

1 OSM ﬁle becomes 4 CartoDB tables 1. Points 2. Polygons 3. Roads 4. Linestrings Sunday, October 14, 12

Slide 27

Slide 27 text

Import local data from .org or mirror simple drag-drop support on most browsers Sunday, October 14, 12

Slide 28

Slide 28 text

Or! Import directly from URL www.openstreetmap.org Sunday, October 14, 12

Slide 29

Slide 29 text

Sunday, October 14, 12

Slide 30

Slide 30 text

Sunday, October 14, 12

Slide 31

Slide 31 text

Sunday, October 14, 12

Slide 32

Slide 32 text

Sunday, October 14, 12

Slide 33

Slide 33 text

Sunday, October 14, 12

Slide 34

Slide 34 text

Sunday, October 14, 12

Slide 35

Slide 35 text

Sunday, October 14, 12

Slide 36

Slide 36 text

From here the sky is the limit combine SQL, CartoCSS, and APIs with ease Sunday, October 14, 12

Slide 37

Slide 37 text

no. seriously. Sunday, October 14, 12

Slide 38

Slide 38 text

SQL remember. it’s everywhere Sunday, October 14, 12

Slide 39

Slide 39 text

Sunday, October 14, 12

Slide 40

Slide 40 text

Sunday, October 14, 12

Slide 41

Slide 41 text

EMBED sql is everywhere Sunday, October 14, 12

Slide 42

Slide 42 text

Sunday, October 14, 12

Slide 43

Slide 43 text

SQL API sql is everywhere Sunday, October 14, 12

Slide 44

Slide 44 text

Sunday, October 14, 12

Slide 45

Slide 45 text

youraccount/osmtable/11/1022/679.png youraccount/osmtable/11/1022/679.png?yada WHERE rail Sunday, October 14, 12

Slide 46

Slide 46 text

CREATIVITY sql is everywhere Sunday, October 14, 12

Slide 47

Slide 47 text

not for me Sunday, October 14, 12

Slide 48

Slide 48 text

http://goo.gl/orkoq Sunday, October 14, 12

Slide 49

Slide 49 text

Sunday, October 14, 12

Slide 50

Slide 50 text

Sunday, October 14, 12

Slide 51

Slide 51 text

Sunday, October 14, 12

Slide 52

Slide 52 text

Sunday, October 14, 12

Slide 53

Slide 53 text

Sunday, October 14, 12

Slide 54

Slide 54 text

CLIENT SIDE Leaﬂet, Google Maps, Backbone, D3 Sunday, October 14, 12

Slide 55

Slide 55 text

Sunday, October 14, 12

Slide 56

Slide 56 text

OR more practical things Sunday, October 14, 12

Slide 57

Slide 57 text

Sunday, October 14, 12

Slide 58

Slide 58 text

Sunday, October 14, 12

Slide 59

Slide 59 text

Sunday, October 14, 12

Slide 60

Slide 60 text

Sunday, October 14, 12

Slide 61

Slide 61 text

Sunday, October 14, 12

Slide 62

Slide 62 text

INNOVATION they don’t let us sleep... Sunday, October 14, 12

Slide 63

Slide 63 text

Sunday, October 14, 12

Slide 64

Slide 64 text

Sunday, October 14, 12

Slide 65

Slide 65 text

Sunday, October 14, 12

Slide 66

Slide 66 text

Sunday, October 14, 12

Slide 67

Slide 67 text

Sunday, October 14, 12

Slide 68

Slide 68 text

_I want to see ‘X’ over time using the SQL API with big data Sunday, October 14, 12

Slide 69

Slide 69 text

Sunday, October 14, 12

Slide 70

Slide 70 text

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 Sunday, October 14, 12

Slide 71

Slide 71 text

1 10 100 1000 3mb 70mb 300mb 1.5 2 1.2 300 70 3 Raw Datacube Payload sizes Sunday, October 14, 12

Slide 72

Slide 72 text

http://goo.gl/TsGsr Sunday, October 14, 12

Slide 73

Slide 73 text

https://dl.dropbox.com/u/580074/osm_edits_in_madrid.mov Sunday, October 14, 12

Slide 74

Slide 74 text

Sunday, October 14, 12

Slide 75

Slide 75 text

Sunday, October 14, 12

Slide 76

Slide 76 text

Sunday, October 14, 12

Slide 77

Slide 77 text

That’s CartoDB + OpenStreetMap in a nutshell come talk to me, I’m sure I missed much more Sunday, October 14, 12