sql for everything: gis on the web

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