CartoDB + OpenStreetMap

Transcript

1. Analyzing OpenStreetMap using CartoDB Sunday, October 14, 12

2. Senior scientist and developer @vizzuality Find me @andrewxhill Sunday, October

   14, 12

3. User, developer, and pusher of limits @cartodb Find me @andrewxhill

   Sunday, October 14, 12

4. blog.cartodb.com Sunday, October 14, 12

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

   12

6. Sunday, October 14, 12

7. Sunday, October 14, 12

8. Sunday, October 14, 12

9. Sunday, October 14, 12

10. Sunday, October 14, 12

11. Sunday, October 14, 12

12. Sunday, October 14, 12

13. Sunday, October 14, 12

14. Sunday, October 14, 12

15. CartoDB is a tool to build applications and master your

    geospatial data Sunday, October 14, 12

16. Sunday, October 14, 12

17. Sunday, October 14, 12

18. Sunday, October 14, 12

19. Sunday, October 14, 12

20. Sunday, October 14, 12

21. Sunday, October 14, 12

22. Importing OSM data into CartoDB it’s sooooo easy Sunday, October

    14, 12

23. We support directly importing OSM and other formats too! shp,

    csv, kml, etc Sunday, October 14, 12

24. CartoDB uses osm2pgsql it keeps it simple Sunday, October 14,

    12

25. Sunday, October 14, 12

26. 1 OSM file becomes 4 CartoDB tables 1. Points 2.

    Polygons 3. Roads 4. Linestrings Sunday, October 14, 12

27. Import local data from .org or mirror simple drag-drop support

    on most browsers Sunday, October 14, 12

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

29. Sunday, October 14, 12

30. Sunday, October 14, 12

31. Sunday, October 14, 12

32. Sunday, October 14, 12

33. Sunday, October 14, 12

34. Sunday, October 14, 12

35. Sunday, October 14, 12

36. From here the sky is the limit combine SQL, CartoCSS,

    and APIs with ease Sunday, October 14, 12

37. no. seriously. Sunday, October 14, 12

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

39. Sunday, October 14, 12

40. Sunday, October 14, 12

41. EMBED sql is everywhere Sunday, October 14, 12

42. Sunday, October 14, 12

43. SQL API sql is everywhere Sunday, October 14, 12

44. Sunday, October 14, 12

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

46. CREATIVITY sql is everywhere Sunday, October 14, 12

47. not for me Sunday, October 14, 12

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

49. Sunday, October 14, 12

50. Sunday, October 14, 12

51. Sunday, October 14, 12

52. Sunday, October 14, 12

53. Sunday, October 14, 12

54. CLIENT SIDE Leaflet, Google Maps, Backbone, D3 Sunday, October 14,

    12

55. Sunday, October 14, 12

56. OR more practical things Sunday, October 14, 12

57. Sunday, October 14, 12

58. Sunday, October 14, 12

59. Sunday, October 14, 12

60. Sunday, October 14, 12

61. Sunday, October 14, 12

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

63. Sunday, October 14, 12

64. Sunday, October 14, 12

65. Sunday, October 14, 12

66. Sunday, October 14, 12

67. Sunday, October 14, 12

68. _I want to see ‘X’ over time using the SQL

    API with big data Sunday, October 14, 12

69. Sunday, October 14, 12

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

71. 1 10 100 1000 3mb 70mb 300mb 1.5 2 1.2

    300 70 3 Raw Datacube Payload sizes Sunday, October 14, 12

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

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

74. Sunday, October 14, 12

75. Sunday, October 14, 12

76. Sunday, October 14, 12

77. That’s CartoDB + OpenStreetMap in a nutshell come talk to

    me, I’m sure I missed much more Sunday, October 14, 12

78. Sunday, October 14, 12

79. Sunday, October 14, 12

80. xº Sunday, October 14, 12

81. Thanks! @andrewxhill Sunday, October 14, 12