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!
Having brought together more than 12,000 experts online in 2020 & 2021, we’re beyond excited to host our long awaited, in-person Spatial Data Science Conference in Lond...
NewsWe’re thrilled to announce we will be attending, sponsoring and presenting at Snowflake Summit 2022 taking place June 13-16 in Las Vegas, Nevada!
NewsWithin the last few years, Spain has been focused on increasing electric vehicle incentives and production; and in 2021, Spain announced that they planned to invest 4.3 bil...
NewsPlease fill out the below form and we'll be in touch real soon.