Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
SQL For Everything
Search
wboykinm
May 08, 2013
Technology
1
310
SQL For Everything
Geoprocessing, Cartography and the Web with PostGIS and CartoDB
wboykinm
May 08, 2013
Tweet
Share
More Decks by wboykinm
See All by wboykinm
Printing to a 2'x3' (Arch D) Poster on the Jeffords Plotter
wboykinm
0
130
Segmenting Biodiversity
wboykinm
0
110
Curating the Storm: TS Irene in Vermont
wboykinm
1
1.5k
irene_foss4g1.pdf
wboykinm
0
71
Other Decks in Technology
See All in Technology
ドメイン特化なCLIPモデルとデータセットの紹介
tattaka
1
220
Lambda Web Adapterについて自分なりに理解してみた
smt7174
5
130
より良いプロダクトの開発を目指して - 情報を中心としたプロダクト開発 #phpcon #phpcon2025
bengo4com
1
3.2k
生成AI時代 文字コードを学ぶ意義を見出せるか?
hrsued
1
660
PHPでWebブラウザのレンダリングエンジンを実装する
dip_tech
PRO
0
210
Node-RED × MCP 勉強会 vol.1
1ftseabass
PRO
0
170
Witchcraft for Memory
pocke
1
620
5min GuardDuty Extended Threat Detection EKS
takakuni
0
160
mrubyと micro-ROSが繋ぐロボットの世界
kishima
2
370
監視のこれまでとこれから/sakura monitoring seminar 2025
fujiwara3
11
4k
AWS Organizations 新機能!マルチパーティ承認の紹介
yhana
1
190
Delegating the chores of authenticating users to Keycloak
ahus1
0
130
Featured
See All Featured
GitHub's CSS Performance
jonrohan
1031
460k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
31
1.2k
BBQ
matthewcrist
89
9.7k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
229
22k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
657
60k
Practical Orchestrator
shlominoach
188
11k
The Pragmatic Product Professional
lauravandoore
35
6.7k
The MySQL Ecosystem @ GitHub 2015
samlambert
251
13k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
46
9.6k
Typedesign – Prime Four
hannesfritz
42
2.7k
Building Flexible Design Systems
yeseniaperezcruz
328
39k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
48
5.4k
Transcript
SQL For Everything Geoprocessing and Cartography With PostGIS Bill Morris
– Geosprocket LLC
Speedy Background • SQL = Structured Query Language • Beloved/BeHated
by Database Admins • Runs Behind Nearly Everything SQL For Everything - Bill Morris - Geosprocket LLC
Ever Fired Up a Computer? You’ve Used SQL. SQL For
Everything - Bill Morris - Geosprocket LLC
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
What’s Post-PostGIS? • A parody on Twitter. Pay no mind.
SQL For Everything - Bill Morris - Geosprocket LLC
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
SQL For Everything - Bill Morris - Geosprocket LLC
SQL For Everything - Bill Morris - Geosprocket LLC
Back to SQL. SQL For Everything - Bill Morris -
Geosprocket LLC
SQL as we know it SQL For Everything - Bill
Morris - Geosprocket LLC Source: Chicago Crime Commission & Openstreetmap Contributors
SELECT * FROM territory_all WHERE “NAME” LIKE ‘Latin%’ SQL For
Everything - Bill Morris - Geosprocket LLC Source: Chicago Crime Commission & Openstreetmap Contributors
SELECT * FROM territory_all WHERE name LIKE ‘Latin%’ SQL For
Everything - Bill Morris - Geosprocket LLC
Geoprocessing as we know it SQL For Everything - Bill
Morris - Geosprocket LLC
Geoprocessing in PostGIS SQL For Everything - Bill Morris -
Geosprocket LLC SELECT ST_Whatever( geoprocess! )
Geoprocessing in PostGIS SQL For Everything - Bill Morris -
Geosprocket LLC
Buffer 500m SQL For Everything - Bill Morris - Geosprocket
LLC
Buffer 500m from the points that fall on Ashland Ave
SQL For Everything - Bill Morris - Geosprocket LLC
Append two layers of different geometry types SQL For Everything
- Bill Morris - Geosprocket LLC
Geoprocessing Optimized SQL For Everything - Bill Morris - Geosprocket
LLC …png?sql=…
? SQL For Everything - Bill Morris - Geosprocket LLC
This is Familiar SQL For Everything - Bill Morris -
Geosprocket LLC google.com?q=GIS
SQL For Everything - Bill Morris - Geosprocket LLC …png?sql=…
http://2.tiles.cartocdn.com/geosprocket/tiles/vtparcels_burlington2002b/15/9714/11850.png SQL For Everything - Bill Morris - Geosprocket LLC
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
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
Going off the Rails a Bit . . . SQL
For Everything - Bill Morris - Geosprocket LLC https://gist.github.com/wboykinm/5533575
SQL For Everything - Bill Morris - Geosprocket LLC
A Query Turns This . . . SQL For Everything
- Bill Morris - Geosprocket LLC
Into This. SQL For Everything - Bill Morris - Geosprocket
LLC
I’m barely scratching the surface of SQL here. SQL For
Everything - Bill Morris - Geosprocket LLC https://gist.github.com/wboykinm/5533575
Because the web wants to see your data SQL For
Everything - Bill Morris - Geosprocket LLC Why Care?
SQL For Everything - Bill Morris - Geosprocket LLC
SQL For Everything - Bill Morris - Geosprocket LLC
SQL For Everything - Bill Morris - Geosprocket LLC
SQL For Everything - Bill Morris - Geosprocket LLC
SQL For Everything - Bill Morris - Geosprocket LLC PostGIS
Thanks! Accepting all questions: SQL For Everything - Bill Morris
- Geosprocket LLC
[email protected]
@vtcraghead