[postgis-devel] gzip support for ST_AsMVT

Mon Nov 4 01:24:33 PST 2019

Paul rulez!!!! Thank you!!! :)

Experimentation ensues...

On Sun, Nov 3, 2019 at 9:29 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> 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
> _______________________________________________
> 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/20191104/03a02838/attachment-0001.html>