My hope with this page is to provide a summary of resources that explore the power of using SQL queries within CartoDB (and remember to update it...). The CartoDB platform is often seen as an easy-to-learn entry option for creating interactive and visually compelling web-based maps. While true, the real power of CartoDB (IMO) lies in providing cloud-based access to PostgreSQL+PostGIS; the underlying spatially-enabled database. My assumption is that if you are reading this you probably have some understand of the SQL language and/or PostGIS but just want to explore how to utilize it within a browser or using CartoDB's API. To be clear I'm not a SQL or JavaScript 'expert' - just a passionate convert trying to keep my head above the frequently crashing waves of neogeospatial goodness (go on, you know you want some - it's a close second to maple syrup).
The best 'starting out' reference I used was "PostGIS in Action" by Obe and Hsu. However these are few other resources on my radar:
- This DZone Reference Card #190 serves as a nice quick reference. You can download the PDF and tape it on your desk! Or not...
- Boundless offers a nice tutorial complete with data. Note: the advantage of using CartoDB is you don't need to install PostgreSQL & PostGIS on your own machine.
- For more advanced PostGIS users I highly recommend the"PostGIS Cookbook" by Corti et al. (2014). And I would follow Steven Mather (for more than just PostGIS-related topics; you like drones too right?)
- I also recently ordered "How Do I Do That In PostGIS: Illustrating Classic GIS Tasks" by Arthur Lembo. Still need to work through this one.
- There is also "PostGIS Essentials" by Angel Marquz, which I haven't reviewed or ordered.
- And lastly, if you REALLY want to dive deep into database architecture (it gets deep fast, but still fascinating), Reynold Xin maintains a great GitHub repository of seminal papers.
But it is worth taking the time to develop some fluency with SQL & understand how PostGIS is different than other spatial databases you make be more familiar with or be ready to endure some growing pains.
Source: XKCD
"The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management." -- E.F. Codd
Source: Giphy
If you're not wowed by the intricacies of spatial databases, this may be the section you were looking for... The following links provide insight how to leverage the power of PostGIS specifically within CartoDB. They are organized in order of technical difficulty (although towards the end that becomes a little more subjective).
- This CartoDB tutorial offers a gentle introduction to PostGIS & SQL within the browser.
- And an additional tutorial on querying a CartoDB table using the CartoDB.js library.
- And this video tutorial from Eric Brelsford provides a nice visual of how basic and intermediate SQL is used within the CartoDB interface.
- This slideshow from Guido Stein covers the basics through calculating buffers.
- Similarly Chris Whong shows us that we can do geoprocessing in the cloud QUICKLY... You feel me right?
- Andy Eschbacher maintains the most extensive CartoDB repository I'm aware of and ranges from introductory to advanced.
- Andrew Hill is the person who really turned me onto CartoDB and has provided insight into the power of this platform. And he's SQL crazy as this example demonstrates (Javi Santana called him out first, although that's really just a pot calling a kettle names...).
- This tutorial illustrates how to allow read/write access to one of your CartoDB tables (not just temporary queries). Moreover you get to see how capable the SQL parser within CartoDB Editor is with complex queries and how you can treat like a traditional desktop PostGIS installation.
- This recent post from Paul Ramsey demonstrates the integration of GDAL and CartoDB. He knows a thing or two about PostGIS; this is is equivalent to Paul sneezing SQL... just wait until he unveils support for querying LiDAR in CartoDB.
- These three brief tutorials link the underlying spatial theory of the - Egenhofer inspired - DE-9IM model with practical examples of stringing together multiple SQL statements. (Theory, Basic, Advanced).
- This post from Peter Desmet nicely highlights both the power of CartoDB and flexibility of CartoCSS. I'd encourage beginners to really explore the SQL code he shares in this post.
- This tutorial from Mike Foster is probably one of the most thorough I've seen and was influenced by Nick Martinelli's 'Neighborhood Mapping Project' - another awesome interface. His bridges are cool too though...
- Another SQL ninja (or maybe samurai is more alliteratively appropriate) is Bill Morris who decided to create an open-data portal served through CartoDB. What makes this so awesome is PostGIS does the format conversations server side.
- And this visual from John Branigan is still one of my favorite created using the CartoDB SQL API. You can explore the underlying code here.
I'm ultimately interested in how we can simplify access to the power of PostGIS by tricking folks into using new tools within the all familiar browser. I specifically want to move explorations of geologic data out Excel and traditional GIS tables into a PostGIS environment - enter, CartoDB.
Source: Meme Generator