[postgis-devel] gzip support for ST_AsMVT

[Yuri Astrakhan]

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?

[1] https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles

On Sun, Nov 3, 2019 at 2:41 PM Martin Davis <mtnclimb at gmail.com> wrote:

> Good background to know.  Quite a bit to grok there!
>
> 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?
>
> On Sun, Nov 3, 2019 at 11:00 AM Yuri Astrakhan <yuriastrakhan at gmail.com>
> wrote:
>
>> 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)
>>
>> 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.
>>
>> [1] OpenMapTiles tools -
>> https://github.com/openmaptiles/openmaptiles-tools
>> [2] tilelive-pgquery - https://www.npmjs.com/package/tilelive-pgquery
>>
>> On Sun, Nov 3, 2019 at 12:55 PM Martin Davis <mtnclimb at gmail.com> wrote:
>>
>>> Great to hear that ST_AsMVT is useful.
>>>
>>> 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.
>>>
>>> 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.
>>>
>>> Out of curiosity, what platform do you use for your external gzipping
>>> layer?
>>>
>>> On Sun, Nov 3, 2019 at 8:29 AM nyurik <yuriastrakhan at gmail.com> wrote:
>>>
>>>> The amazing ST_AsMVT() has two common usage patterns:  copy resulting
>>>> MVTs to
>>>> a tile cache (e.g. .mbtiles file or a materialized view), or serve MVT
>>>> to
>>>> the users (direct SQL->browser approach).  Both patterns still require
>>>> one
>>>> additional data processing step -- gziping.
>>>>
>>>> Thus, rather than having a horizontally scalable db plus a simple
>>>> IO-bound
>>>> SQL->Web or a SQL->store process, one has to add a relatively
>>>> CPU-intensive
>>>> gzipping layer.  This is especially relevant if I try to create a PG
>>>> table
>>>> with the pre-generated tiles - I must use an external data compression
>>>> process to retrieve a tile, gzip it, and store it back, instead of
>>>> running a
>>>> single query for copying all tiles.  My cursory look at the tile sizes
>>>> indicate gzipping shrinks MVTs 50% to 300%.
>>>>
>>>> Note that a similar CPU-intensive step - creating MD5 tile hashes for a
>>>> more
>>>> efficient storage - can be easily done with PG's `md5()` function,
>>>> whereas
>>>> `gzip()` doesn't appear to exist.
>>>>
>>>> I would like to propose two possible solutions:
>>>> * Implement ST_AsMVT(..., compress) parameter - NULL=no compression,
>>>> 0-9=compression level.
>>>>    PROs:  adds just the required functionality to where it is needed
>>>> (YAGNI
>>>> principle), does not require ungzip yet (ST_AsMVT is a one way function
>>>> without the corresponding MVT->Table method)
>>>>    CONs: less generic (unusable for non-MVT usage)
>>>> * Implement gzip() or ST_gzip()
>>>>    PROs:  a more generic approach not tied to MVTs
>>>>    CONs:  logically implies the need of ungzip(), requires PG community
>>>> to
>>>> agree this functionality is needed
>>>>
>>>> Thanks!
>>>>
>>>>
>>>>
>>>> --
>>>> Sent from: http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html
>>>> _______________________________________________
>>>> postgis-devel mailing list
>>>> postgis-devel at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>>>
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>>
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20191103/ed9f024c/attachment.html>