Creating buffers that automatically update when new feature added using QGIS

Question

I have currently added a CSV file of earthquakes on a 7 day basis and added in the URL so that it gets updated automatically. I also have population count data for the whole of Central and South America. What would be the best way to add in buffers (e.g. 5km, 10km, 25km), so they automatically update once an earthquake appears and how would I go about extract the population data within each buffer, so I know the population affected by each.

Moreover, I have events for the world but I only want them for Central and South America. Is there any where I can filter by attributes, so I only have events for these two areas?

Answer 1

Assuming both, earthquakes and population are vector points, you can create a virtual layer with an expression like this:

SELECT 
e.id, st_buffer(e.geometry,10) as geom2, COUNT(i.geometry) as ncities, SUM(i.population) as affected_population
FROM 
earthquakes as e
JOIN 
Inhabitants as i
ON 
ST_Intersects(st_buffer(e.geometry,10), i.geometry)
GROUP BY 
e.id;

It will create buffers of earthquakes in specified distance (here 10) and count the total affected population + the number of cities. You can include st_transform() if you need all this in a special CRS. The virtual layer will update automatically each time a new point has been added. In worst case you have to zoom in and out or pan the map a little to refresh the canvas.

Make sure to not get lost in the three different geometries:

Here an example: