[postgis-devel] gzip support for ST_AsMVT

[Paul Ramsey]

Excuse me,

https://github.com/pramsey/pgsql-gzip

P

On Sun, Nov 3, 2019 at 6:24 PM Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>
> I could not find a gzip extension on the web, even though I swear one
> already exists.
> So, I wrote up one, it's quite a small piece of work, but hopefully it serves.
>
> https://github.com/pramsey/psql-gzip
>
> ATB,
>
> P
>
> On Sun, Nov 3, 2019 at 11:57 AM Yuri Astrakhan <yuriastrakhan at gmail.com> wrote:
> >
> > 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
> >
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-devel