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

CartoDB + OpenStreetMap

35f4d000a88cdbcf6392dfb206ebd5e2?s=47 Andrew W Hill
October 16, 2012

CartoDB + OpenStreetMap

CartoDB is a great tool for using, sharing, and analyzing OSM data. Here I give an overview of some of the things that can be done. For any links or further information, tweet me @andrewxhill

35f4d000a88cdbcf6392dfb206ebd5e2?s=128

Andrew W Hill

October 16, 2012
Tweet

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