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
260
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
120
Segmenting Biodiversity
wboykinm
0
91
Curating the Storm: TS Irene in Vermont
wboykinm
1
1.2k
irene_foss4g1.pdf
wboykinm
0
59
Other Decks in Technology
See All in Technology
Four keys改善の取り組み事例紹介
sansantech
PRO
2
220
Apple Vision Pro trial session
akkeylab
0
120
AIQ株式会社 エンジニア向け会社紹介資料
aiqlab
0
350
シン・Kafka / shin-kafka
oracle4engineer
PRO
6
2.7k
カオナビの利用実績をアウトカムへつなげる旅 / example-of-data-management-startup-in-kaonavi
kaonavi
0
110
SPI原点回帰論:事業課題とFour Keysの結節点を見出す実践的ソフトウェアプロセス改善 / DevOpsDays Tokyo 2024
visional_engineering_and_design
4
1.1k
Pedestrian-Centric大規模交通安全映像解析向けWoven Traffic Safety (WTS) データセットの紹介
kbuster
0
140
自動生成を活用した、運用保守コストを抑える Error/Alert/Runbook の一元集約管理 / Centralized management of Error/Alert/Runbook to minimize operational costs using automated code generation
biwashi
9
2k
"好き"との生活/Regularly update profile with GitHub Actions
judeeeee
0
120
クラウドサインにおけるプロダクトマネージャーの役割と開発プロセス / 20240410_cloudsign-PdM
bengo4com
1
660
インシデントレスポンスのライフサイクルを廻すポイントってなに / Pinpoints of Incidentresponse Lifecycle for Operation
sakaitakeshi
0
280
オブザーバビリティの Primary Signals
onk
PRO
0
530
Featured
See All Featured
GitHub's CSS Performance
jonrohan
1023
450k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
118
38k
10 Git Anti Patterns You Should be Aware of
lemiorhan
645
57k
jQuery: Nuts, Bolts and Bling
dougneiner
58
7.1k
Scaling GitHub
holman
457
140k
Design by the Numbers
sachag
274
18k
Product Roadmaps are Hard
iamctodd
43
9.7k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
18
1.7k
Fantastic passwords and where to find them - at NoRuKo
philnash
35
2.5k
What’s in a name? Adding method to the madness
productmarketing
PRO
14
2.6k
Ruby is Unlike a Banana
tanoku
95
10k
Debugging Ruby Performance
tmm1
69
11k
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