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

SQL For Everything

A8d71b1580d266b8f5d1e2175561c6ff?s=47 wboykinm
May 08, 2013
Technology
1
240

SQL For Everything

Geoprocessing, Cartography and the Web with PostGIS and CartoDB

A8d71b1580d266b8f5d1e2175561c6ff?s=128

wboykinm

May 08, 2013
Tweet

More Decks by wboykinm

See All by wboykinm
A8d71b1580d266b8f5d1e2175561c6ff?s=48 wboykinm
0
92
A8d71b1580d266b8f5d1e2175561c6ff?s=48 wboykinm
0
60
A8d71b1580d266b8f5d1e2175561c6ff?s=48 wboykinm
1
940
A8d71b1580d266b8f5d1e2175561c6ff?s=48 wboykinm
0
39

Other Decks in Technology

See All in Technology
Db4af41c084fb66f56725d0b3b40fb09?s=48 go5paopao
8
1k
Bc0167df60f90a38e0ec30895b7ecc6c?s=48 karabish
0
330
70357f859596e494c8373be7a87966b1?s=48 1027kg
0
200
4b2f3a64637b51e81813accbe8a98083?s=48 miura55
0
350
Fb025419ed38f98df595eb2eafc859f4?s=48 ihcomega56
1
580
96f06c7c9af1405aa0f0e4102b2812be?s=48 iwashi86
54
24k
3353b76e1480888ea7a482ebaa883dcb?s=48 robcrowley
0
150
A7944f54379373479a430eb9e9954587?s=48 will03
0
100
02e11e1c79e926b59d011839bb10ab04?s=48 minma
0
160
3a95702770e66754d87b824c1400054f?s=48 oliva
7
1.2k
80542834077f51ffe731277513db2c07?s=48 zak3
1
200
0deae06ab5d86b39feeec2e23a30b88a?s=48 yoku0825
PRO
3
120

Featured

See All Featured
78b475797a14c84799063c7cd073962f?s=48 holman
288
130k
8ec1383b240b5ba15ffb9743fceb3c0e?s=48 philnash
9
590
282d599b414022eba5096510f675b6e2?s=48 chrislema
231
16k
5b6a2bd86ef643786a381a212e0ef2f1?s=48 geoffreycrofte
21
920
48c098b6579220a67a6ba949be6e4b5a?s=48 revolveconf
200
9.7k
016edace78ffe826f2348d1e0c504afd?s=48 maggiecrowley
10
500
F68cb25ae3e5c2106997454b13b7737d?s=48 brad_frost
157
6.4k
245cee81a9c424266e5e401d844ea881?s=48 lara
172
9.6k
E6c6e133e74c3b83f04d2861deaa1c20?s=48 searls
204
36k
766b9746b59e6f09e280cd33cf4ed419?s=48 skipperchong
8
720
B3ace07412a5178ea778e7eec161fc0a?s=48 jcasabona
8
550
Aebc2d07a50011e2a30d38435fde564a?s=48 jrom
116
7.2k

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