SQL For Everything - Speaker Deck

Slide 1

Slide 1 text

SQL For Everything Geoprocessing and Cartography With PostGIS Bill Morris – Geosprocket LLC

Slide 2

Slide 2 text

Speedy Background • SQL = Structured Query Language • Beloved/BeHated by Database Admins • Runs Behind Nearly Everything SQL For Everything - Bill Morris - Geosprocket LLC

Slide 3

Slide 3 text

Ever Fired Up a Computer? You’ve Used SQL. SQL For Everything - Bill Morris - Geosprocket LLC

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

What’s Post-PostGIS? • A parody on Twitter. Pay no mind. SQL For Everything - Bill Morris - Geosprocket LLC

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 8

Slide 8 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 9

Slide 9 text

Back to SQL. SQL For Everything - Bill Morris - Geosprocket LLC

Slide 10

Slide 10 text

SQL as we know it SQL For Everything - Bill Morris - Geosprocket LLC Source: Chicago Crime Commission & Openstreetmap Contributors

Slide 11

Slide 11 text

SELECT * FROM territory_all WHERE “NAME” LIKE ‘Latin%’ SQL For Everything - Bill Morris - Geosprocket LLC Source: Chicago Crime Commission & Openstreetmap Contributors

Slide 12

Slide 12 text

SELECT * FROM territory_all WHERE name LIKE ‘Latin%’ SQL For Everything - Bill Morris - Geosprocket LLC

Slide 13

Slide 13 text

Geoprocessing as we know it SQL For Everything - Bill Morris - Geosprocket LLC

Slide 14

Slide 14 text

Geoprocessing in PostGIS SQL For Everything - Bill Morris - Geosprocket LLC SELECT ST_Whatever( geoprocess! )

Slide 15

Slide 15 text

Geoprocessing in PostGIS SQL For Everything - Bill Morris - Geosprocket LLC

Slide 16

Slide 16 text

Buffer 500m SQL For Everything - Bill Morris - Geosprocket LLC

Slide 17

Slide 17 text

Buffer 500m from the points that fall on Ashland Ave SQL For Everything - Bill Morris - Geosprocket LLC

Slide 18

Slide 18 text

Append two layers of different geometry types SQL For Everything - Bill Morris - Geosprocket LLC

Slide 19

Slide 19 text

Geoprocessing Optimized SQL For Everything - Bill Morris - Geosprocket LLC …png?sql=…

Slide 20

Slide 20 text

? SQL For Everything - Bill Morris - Geosprocket LLC

Slide 21

Slide 21 text

This is Familiar SQL For Everything - Bill Morris - Geosprocket LLC google.com?q=GIS

Slide 22

Slide 22 text

SQL For Everything - Bill Morris - Geosprocket LLC …png?sql=…

Slide 23

Slide 23 text

http://2.tiles.cartocdn.com/geosprocket/tiles/vtparcels_burlington2002b/15/9714/11850.png SQL For Everything - Bill Morris - Geosprocket LLC

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

Going off the Rails a Bit . . . SQL For Everything - Bill Morris - Geosprocket LLC https://gist.github.com/wboykinm/5533575

Slide 27

Slide 27 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 28

Slide 28 text

A Query Turns This . . . SQL For Everything - Bill Morris - Geosprocket LLC

Slide 29

Slide 29 text

Into This. SQL For Everything - Bill Morris - Geosprocket LLC

Slide 30

Slide 30 text

I’m barely scratching the surface of SQL here. SQL For Everything - Bill Morris - Geosprocket LLC https://gist.github.com/wboykinm/5533575

Slide 31

Slide 31 text

Because the web wants to see your data SQL For Everything - Bill Morris - Geosprocket LLC Why Care?

Slide 32

Slide 32 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 33

Slide 33 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 34

Slide 34 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 35

Slide 35 text

SQL For Everything - Bill Morris - Geosprocket LLC

Slide 36

Slide 36 text

SQL For Everything - Bill Morris - Geosprocket LLC PostGIS

Slide 37

Slide 37 text

Thanks! Accepting all questions: SQL For Everything - Bill Morris - Geosprocket LLC [email protected] @vtcraghead