SQL For Everything

Geoprocessing, Cartography and the Web with PostGIS and CartoDB

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