Andy Eschbacher • Aug 7, 2015

TechChange Workshop on Real-time Mapping

At the invitation of Eva Adler and Norman Shamas, I had the pleasure of giving a workshop on CartoDB’s real-time mapping capabilities for the TechChange class Mapping for Social Good. Eva Adler’s course covers an impressive array of material on web mapping related to global development work.

I gave a short workshop introducing the real-time mapping available through CartoDB’s sync tables to create a map that updates hourly with earthquake data pulled from the United States Geological Survey servers. Using the spatial analysis functionality of PostGIS, I showed how to join the dataset of earthquakes (which comes as latitude/longitude points) with the districts of Nepal (which are polygons), producing a map from an on-the-fly calculation. The result visualizes the largest earthquake from the USGS dataset that intersects with a district in Nepal. This is the SQL that I used (names changed for readability):

SELECT 
  n.district_name, 
  n.the_geom_webmercator,
  max(e.magnitude) As max_magnitude,
  n.cartodb_id
FROM
  nepal_districts AS n
JOIN
  all_month_10 As e
 ON
  ST_Intersects(n.the_geom,e.the_geom)
GROUP BY n.district_name, n.the_geom_webmercator, n.cartodb_id

All you need to do is paste your custom SQL into the SQL tray in the CartoDB Editor when you’re creating your visualization and it will run against your tables to produce the up-to-date map everytime someone looks at it. It’s pretty magical in my opinion! If you want to learn the basics of SQL, checkout our course on SQL and PostGIS in our Map Academy.

Want to get this crash course on mapping real-time data that is beyond just a basic dataset? Take a look at the write up and watch the video.

Mapping for Social Good will be taught again starting October 26, so don’t fret about missing Eva’s course this time around. If you want to get trained in other next-generation skills, check out TechChange’s awesome offerings.

Happy data Mapping!

About the author

Andy Eschbacher is a data scientist at CARTO, where he integrates data science solutions into CARTO's infrastructure, solves spatial data science problems for clients, and builds out tools to better enable people working at the intersection of data science and GIS.

More posts from Andy Eschbacher