<div dir="ltr">OSM -> PostgreSQL is done by Imposm 3, which updates database tables on the daily/hourly/minute basis. Every time it runs, it generates a list of changed tiles. I do not know what process they use for it -- from my perspective, I simply get the list of updated tiles on zoom 14 as a file, and I could use it to regenerate cached tiles or purge them from Varnish. Perhaps Imposm maintainers could find a good use for that functionality?<div><br></div><div>[1] <a href="https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles">https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles</a></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Nov 3, 2019 at 2:41 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com">mtnclimb@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Good background to know. Quite a bit to grok there!<div><br></div><div>One question: does any part of that toolchain regenerate tile cache subsets depending on detection of feature change? And if so, would it be useful to have a DB function which can determine the set of tile ids that need to be refreshed (i.e. by mapping a (set of or single) geometry (or envelopes) into a set of tile ids? </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Nov 3, 2019 at 11:00 AM Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com" target="_blank">yuriastrakhan@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">Martin, I am working on improving OpenMapTiles tooling [1] - the ultimate goal is to have tiles generated in real time from the up-to-date OSM data, and serve them directly to user's browser via some caching layer (i.e. Varnish). The tools already contain postserve - a simple python server that queries for MVT tiles (no compression yet, but can be easily added)</div><div dir="ltr"><br></div><div dir="ltr">The other task is tile pre-generation using tilelive-copy (nodejs) - I wrote a tilelive-pgquery plugin [2] that queries PG for the tile, compresses it, and passes it on to tilelive-copy for storage.</div><div dir="ltr"><br></div><div>[1] OpenMapTiles tools - <a href="https://github.com/openmaptiles/openmaptiles-tools" target="_blank">https://github.com/openmaptiles/openmaptiles-tools</a><br></div><div>[2] tilelive-pgquery - <a href="https://www.npmjs.com/package/tilelive-pgquery" target="_blank">https://www.npmjs.com/package/tilelive-pgquery</a></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Nov 3, 2019 at 12:55 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank">mtnclimb@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Great to hear that ST_AsMVT is useful.<div><br></div><div>The other PostGIS capability that is useful for web spatial applications is the (recently enhanced) ST_AsGeoJSON. This should also be gzipped over the wire. So this suggests a modular gzip capability would be more useful.</div><div><br></div><div>If this isn't provided in Postgres in some way (now or in near term) perhaps we should just add a ST_Gzip function to PostGIS.</div><div><br></div><div>Out of curiosity, what platform do you use for your external gzipping layer?</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Nov 3, 2019 at 8:29 AM nyurik <<a href="mailto:yuriastrakhan@gmail.com" target="_blank">yuriastrakhan@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">The amazing ST_AsMVT() has two common usage patterns: copy resulting MVTs to<br>
a tile cache (e.g. .mbtiles file or a materialized view), or serve MVT to<br>
the users (direct SQL->browser approach). Both patterns still require one<br>
additional data processing step -- gziping.<br>
<br>
Thus, rather than having a horizontally scalable db plus a simple IO-bound<br>
SQL->Web or a SQL->store process, one has to add a relatively CPU-intensive<br>
gzipping layer. This is especially relevant if I try to create a PG table<br>
with the pre-generated tiles - I must use an external data compression<br>
process to retrieve a tile, gzip it, and store it back, instead of running a<br>
single query for copying all tiles. My cursory look at the tile sizes<br>
indicate gzipping shrinks MVTs 50% to 300%.<br>
<br>
Note that a similar CPU-intensive step - creating MD5 tile hashes for a more<br>
efficient storage - can be easily done with PG's `md5()` function, whereas<br>
`gzip()` doesn't appear to exist.<br>
<br>
I would like to propose two possible solutions:<br>
* Implement ST_AsMVT(..., compress) parameter - NULL=no compression,<br>
0-9=compression level.<br>
PROs: adds just the required functionality to where it is needed (YAGNI<br>
principle), does not require ungzip yet (ST_AsMVT is a one way function<br>
without the corresponding MVT->Table method)<br>
CONs: less generic (unusable for non-MVT usage)<br>
* Implement gzip() or ST_gzip()<br>
PROs: a more generic approach not tied to MVTs<br>
CONs: logically implies the need of ungzip(), requires PG community to<br>
agree this functionality is needed<br>
<br>
Thanks!<br>
<br>
<br>
<br>
--<br>
Sent from: <a href="http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html" rel="noreferrer" target="_blank">http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html</a><br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div></div>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>