[postgis-devel] gzip support for ST_AsMVT

[Yuri Astrakhan]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20191103/801fe13b/attachment-0001.html>