Is it be possible to re-render parts of a tile pyramid when postgis table updates?

Question

I have this idea of using postgis together with a tile renderer for serving my organization with tiled maps. As I believe my data will be partially altered almost on a daily basis it does feel like a performance issue if I have to render the whole cache every night as the changes are quite small but still significant.

Would it be possible to re-render parts of a tile pyramid or a grid in a cache instead of a complete re-rendering?

And have anyone tried to execute this rendering in for example mapnik from inside postgresql/postgis using a database trigger? I believe the first issue would be to determine what tiles are affected and then re-render them specifically.

Any ideas?

Answer 1

You should be able to easily achieve that by:

1. implement an on insert/update trigger which puts changed features on a work queue
2. a worker that periodically wales up and grabs stuff to do from the queue and refreshes tiles

Item 1 can be easily written in pure sql in postgres by means of a trigger. As for item 2 you just need to call the seed program (all caches provide one) with the following options:

• force: regenerate tiles even if already present in the cache
• bbox: regenerate tiles only for the provided bbox, which in your case is the bbox of the modified feature

Hint: documentation link for the relevant section of the TileCache manual.

Please note that it might be worth doing some bbox merging prior to start the reseeding process as it is most likely that changes happen in spatially close/limited areas and theregore you could end up regeneratong the same tile over and over again.

Answer 2

It should be easy to set up: in a slightly different - but actually similar - raster context, I had to do update-retiling for a bunch of GeoTIFFs that cover an entire city, with different sets of geoTIFFs at different ranges of zoom from 9 to 18. The GeoTIFFs also didn't 'fit' neatly into TMS/Google Maps tile numbers.

Rather than re-tile the whole job (which took 19 hours for the full fileset), I set it up so that if a geoTIFF changed the renderer would

1. identify the set of GMaps tile references that the 'changed' tile intersected at its lowest rendered zoom (that's easy since the bounds of GMaps tiles are fixed at each zoom - find the set of GMaps tiles that completely contain the bounds of the changed GeoTIFF);
2. grab all GeoTIFFs from the same 'zoom set' that intersected the GMap tiles from (1) above; and
3. make a new 'base' GeoTIFF by stitching together the geoTIFFs collected at (2) (making a vrt with gdal);
4. tile the resultant vrt from lowest-zoom to highest-zoom for the geoTIFF in question.

The re-tiling never took more than a few minutes: that's heaps better than 19 hours.

With PostGIS data it should actually be way easier because there's no need to do raster comparisons and what-not - just need to find the basemap tiles that are affected at the lowest zoom level, and generate tiles for the bounding box of that tile and all its children (i.e., all higher zoom levels).

So if I were faced with your task (and not knowing what your basemap is - Google Maps, OSM, Bing, custom), I would identify what basemap tiles were impacted by the change to your data at the lowest level of zoom at which the data is presented, and re-tile everything in those tile and its children.

If you know minZoom for the data in question, finding the base tile number is straightforward (there's awesome code by Klokan Petr Pridal on Tiles à la Google Maps that will enable you to find the TMS or GTile reference - and its bounds - for an arbitrary point... scroll down the page and look for a big text box. It helped me immensely with my task).

And yes, it ought to be able to be set up as a trigger: triggers can call e.g., Python scripts or other things that can do the yard-work for you.

Over the weekend I will try to dig out my old code and tweak it to make it more vector oriented (as I mentioned, this was a raster-based job).