Download - Sql for Everything: GIS on the Web
![Page 1: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/1.jpg)
SQL For Everything
Geoprocessing and Cartography With PostGIS
Bill Morris – Geosprocket LLC
![Page 2: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/2.jpg)
Speedy Background
• SQL = Structured Query Language
• Beloved/BeHated by Database Admins
• Runs Behind Nearly Everything
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 3: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/3.jpg)
Ever Fired Up a Computer?
You’ve Used SQL.
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 4: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/4.jpg)
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
![Page 5: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/5.jpg)
What’s Post-PostGIS?
• A parody on Twitter. Pay no mind.
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 6: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/6.jpg)
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
![Page 7: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/7.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 8: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/8.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 9: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/9.jpg)
Back to SQL.
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 10: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/10.jpg)
SQL as we know it
SQL For Everything - Bill Morris - Geosprocket LLC
Source: Chicago Crime Commission & Openstreetmap Contributors
![Page 11: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/11.jpg)
SELECT * FROM territory_all WHERE
“NAME” LIKE ‘Latin%’
SQL For Everything - Bill Morris - Geosprocket LLC
Source: Chicago Crime Commission & Openstreetmap Contributors
![Page 12: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/12.jpg)
SELECT * FROM territory_all WHERE
name LIKE ‘Latin%’
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 13: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/13.jpg)
Geoprocessing as we know it
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 14: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/14.jpg)
Geoprocessing in PostGIS
SQL For Everything - Bill Morris - Geosprocket LLC
SELECT ST_Whatever( geoprocess! )
![Page 15: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/15.jpg)
Geoprocessing in PostGIS
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 16: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/16.jpg)
Buffer 500m
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 17: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/17.jpg)
Buffer 500m from the points that fall on Ashland Ave
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 18: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/18.jpg)
Append two layers of different geometry types
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 19: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/19.jpg)
Geoprocessing Optimized
SQL For Everything - Bill Morris - Geosprocket LLC
…png?sql=…
![Page 20: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/20.jpg)
?
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 21: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/21.jpg)
This is Familiar
SQL For Everything - Bill Morris - Geosprocket LLC
google.com?q=GIS
![Page 22: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/22.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
…png?sql=…
![Page 23: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/23.jpg)
http://2.tiles.cartocdn.com/geosprocket/tiles/vtparcels_burlington2002b/15/9714/11850.png
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 24: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/24.jpg)
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
![Page 25: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/25.jpg)
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
![Page 26: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/26.jpg)
Going off the Rails a Bit . . .
SQL For Everything - Bill Morris - Geosprocket LLC
https://gist.github.com/wboykinm/5533575
![Page 27: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/27.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 28: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/28.jpg)
A Query Turns This . . .
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 29: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/29.jpg)
Into This.
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 30: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/30.jpg)
I’m barely scratching the surface of SQL here.
SQL For Everything - Bill Morris - Geosprocket LLC
https://gist.github.com/wboykinm/5533575
![Page 31: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/31.jpg)
Because the web wants to see your data
SQL For Everything - Bill Morris - Geosprocket LLC
Why Care?
![Page 32: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/32.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 34: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/34.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
![Page 36: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/36.jpg)
SQL For Everything - Bill Morris - Geosprocket LLC
PostGIS
![Page 37: Sql for Everything: GIS on the Web](https://reader033.vdocuments.us/reader033/viewer/2022052523/555e091ed8b42a99188b48f0/html5/thumbnails/37.jpg)
Thanks! Accepting all questions:
SQL For Everything - Bill Morris - Geosprocket LLC
@vtcraghead