Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up
for free
SQL For Everything
wboykinm
May 08, 2013
Technology
1
240
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
wboykinm
0
92
wboykinm
0
60
wboykinm
1
940
wboykinm
0
39
Other Decks in Technology
See All in Technology
go5paopao
8
1k
karabish
0
330
1027kg
0
200
miura55
0
350
ihcomega56
1
580
iwashi86
54
24k
robcrowley
0
150
will03
0
100
minma
0
160
oliva
7
1.2k
zak3
1
200
yoku0825
PRO
3
120
Featured
See All Featured
holman
288
130k
philnash
9
590
chrislema
231
16k
geoffreycrofte
21
920
revolveconf
200
9.7k
maggiecrowley
10
500
brad_frost
157
6.4k
lara
172
9.6k
searls
204
36k
skipperchong
8
720
jcasabona
8
550
jrom
116
7.2k
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 wboykinm@geosprocket.com @vtcraghead