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

SQL For Everything

SQL For Everything

Geoprocessing, Cartography and the Web with PostGIS and CartoDB

A8d71b1580d266b8f5d1e2175561c6ff?s=128

wboykinm

May 08, 2013
Tweet

Transcript

  1. SQL For Everything Geoprocessing and Cartography With PostGIS Bill Morris

    – Geosprocket LLC
  2. Speedy Background • SQL = Structured Query Language • Beloved/BeHated

    by Database Admins • Runs Behind Nearly Everything SQL For Everything - Bill Morris - Geosprocket LLC
  3. Ever Fired Up a Computer? You’ve Used SQL. SQL For

    Everything - Bill Morris - Geosprocket LLC
  4. So What’s PostGIS? • A spatial extender for the PostgreSQL

    database system • Free as in Speech, Free as in Beer SQL For Everything - Bill Morris - Geosprocket LLC
  5. What’s Post-PostGIS? • A parody on Twitter. Pay no mind.

    SQL For Everything - Bill Morris - Geosprocket LLC
  6. What’s CartoDB? • A PostGIS environment built for the web

    • “Open-Source Fusion Tables” • Build your own server: https://github.com/CartoDB/cartodb20 • Or get a hosted account: http://cartodb.com/ SQL For Everything - Bill Morris - Geosprocket LLC
  7. SQL For Everything - Bill Morris - Geosprocket LLC

  8. SQL For Everything - Bill Morris - Geosprocket LLC

  9. Back to SQL. SQL For Everything - Bill Morris -

    Geosprocket LLC
  10. SQL as we know it SQL For Everything - Bill

    Morris - Geosprocket LLC Source: Chicago Crime Commission & Openstreetmap Contributors
  11. SELECT * FROM territory_all WHERE “NAME” LIKE ‘Latin%’ SQL For

    Everything - Bill Morris - Geosprocket LLC Source: Chicago Crime Commission & Openstreetmap Contributors
  12. SELECT * FROM territory_all WHERE name LIKE ‘Latin%’ SQL For

    Everything - Bill Morris - Geosprocket LLC
  13. Geoprocessing as we know it SQL For Everything - Bill

    Morris - Geosprocket LLC
  14. Geoprocessing in PostGIS SQL For Everything - Bill Morris -

    Geosprocket LLC SELECT ST_Whatever( geoprocess! )
  15. Geoprocessing in PostGIS SQL For Everything - Bill Morris -

    Geosprocket LLC
  16. Buffer 500m SQL For Everything - Bill Morris - Geosprocket

    LLC
  17. Buffer 500m from the points that fall on Ashland Ave

    SQL For Everything - Bill Morris - Geosprocket LLC
  18. Append two layers of different geometry types SQL For Everything

    - Bill Morris - Geosprocket LLC
  19. Geoprocessing Optimized SQL For Everything - Bill Morris - Geosprocket

    LLC …png?sql=…
  20. ? SQL For Everything - Bill Morris - Geosprocket LLC

  21. This is Familiar SQL For Everything - Bill Morris -

    Geosprocket LLC google.com?q=GIS
  22. SQL For Everything - Bill Morris - Geosprocket LLC …png?sql=…

  23. http://2.tiles.cartocdn.com/geosprocket/tiles/vtparcels_burlington2002b/15/9714/11850.png SQL For Everything - Bill Morris - Geosprocket LLC

  24. http://2.tiles.cartocdn.com/geosprocket/tiles/vtparcels_burlington2002b/15/9714/11850.png?sql =SELECT * FROM vtparcels_burlington2002b WHERE area>8000 SQL For Everything

    - Bill Morris - Geosprocket LLC
  25. http://2.tiles.cartocdn.com/geosprocket/tiles/vtparcels_burlington2002b/15/9714/11850.png?sql =SELECT 20 as distance, st_buffer(the_geom_webmercator,50) as the_geom_webmercator FROM vtparcels_burlington2002b

    WHERE area>80000 SQL For Everything - Bill Morris - Geosprocket LLC
  26. Going off the Rails a Bit . . . SQL

    For Everything - Bill Morris - Geosprocket LLC https://gist.github.com/wboykinm/5533575
  27. SQL For Everything - Bill Morris - Geosprocket LLC

  28. A Query Turns This . . . SQL For Everything

    - Bill Morris - Geosprocket LLC
  29. Into This. SQL For Everything - Bill Morris - Geosprocket

    LLC
  30. I’m barely scratching the surface of SQL here. SQL For

    Everything - Bill Morris - Geosprocket LLC https://gist.github.com/wboykinm/5533575
  31. Because the web wants to see your data SQL For

    Everything - Bill Morris - Geosprocket LLC Why Care?
  32. SQL For Everything - Bill Morris - Geosprocket LLC

  33. SQL For Everything - Bill Morris - Geosprocket LLC

  34. SQL For Everything - Bill Morris - Geosprocket LLC

  35. SQL For Everything - Bill Morris - Geosprocket LLC

  36. SQL For Everything - Bill Morris - Geosprocket LLC PostGIS

  37. Thanks! Accepting all questions: SQL For Everything - Bill Morris

    - Geosprocket LLC wboykinm@geosprocket.com @vtcraghead