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
Long journey of Continuous Delivery at Mercari
hisaharu
1
220
VISITS_AIIoTビジネス共創ラボ登壇資料.pdf
iotcomjpadmin
0
110
DB 醬,嗨!哪泥嘎斯基?
line_developers_tw
PRO
0
260
doda開発 生成AI元年宣言!自家製AIエージェントから始める生産性改革 / doda Development Declaration of the First Year of Generated AI! Productivity Reforms Starting with Home-grown AI Agents
techtekt
0
170
自分を理解するAI時代の準備 〜マイプロフィールMCPの実装〜
edo_m18
0
110
AIにどこまで任せる?実務で使える(かもしれない)AIエージェント設計の考え方
har1101
3
1.2k
Kotlinで学ぶ 代数的データ型
ysknsid25
5
1.1k
比起獨自升級 我更喜歡 DevOps 文化 <3
line_developers_tw
PRO
0
250
宇宙パトロール ルル子から考える LT設計のコツ
masakiokuda
2
110
OpenTelemetry Collector internals
ymotongpoo
5
560
Definition of Done
kawaguti
PRO
5
380
OCI Oracle Database Services新機能アップデート(2025/03-2025/05)
oracle4engineer
PRO
1
170
Featured
See All Featured
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
181
53k
The World Runs on Bad Software
bkeepers
PRO
68
11k
Designing for humans not robots
tammielis
253
25k
The Language of Interfaces
destraynor
158
25k
Navigating Team Friction
lara
186
15k
The Cult of Friendly URLs
andyhume
79
6.4k
jQuery: Nuts, Bolts and Bling
dougneiner
63
7.8k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
3k
Designing for Performance
lara
609
69k
Writing Fast Ruby
sferik
628
61k
Thoughts on Productivity
jonyablonski
69
4.7k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
233
17k
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