[Qgis-developer] Postgis-Provider and compression

[Pirmin Kalberer]

Hi Andreas,

Am Dienstag, 10. August 2010, um 13.45:21 schrieb Andreas Neumann:
> Hi Pirmin,
> 
> Thank you for your thoughts on the issue. My comments inline.
> 
> On 08/09/2010 10:47 PM, Pirmin Kalberer wrote:
> > Hi Andreas,
> > 
> > Am Montag, 9. August 2010, um 14.29:12 schrieb Andreas Neumann:
> >> Hi all,
> >> 
> >> I wonder if the current Postgis-Provider somehow supports or would
> >> support compression during data-transfer between the QGIS-client and
> >> the remote Postgis-server. In my experience, data transfer across
> >> slower connection (DSL, cable) is often rather slow.
> >> 
> >> Would there be, technically, a way to accelerate data transfer over
> >> slower network connections?
> > 
> > I don't know a built-in method for compressing data transfer on Postgres
> > connections (http://www.postgresql.org/docs/current/static/libpq-
> > connect.html).
> 
> ok - that's what I thought. So the best solution would be probably to
> get the PostgreSQL developers to implement compression in libpq ? That
> way not only geometry would profit, but also attribute data, which can
> also get quite large (in case of text). That way also slower mobile
> connections (phones, tablets, notebooks) could benefit from data
> compression in the future.

Finally it should be implemented on Postgres side, yes. But you could 
investigate with the mentioned tunnel method, how much you win. The 
compression results could be very data type dependent.

> 
> > But you could tunnel the connection e.g. with netcat or ssh (without
> > encryption) and compress it with gzip. Other solutions would be a
> > compressed geometry format (like
> > http://www.gaia-gis.it/spatialite-2.4.0/) or a partial update method.
> > Both would need changes or custom stored procedures within Postgis.
> 
> ok - thanks for the idea.
> 
> > A different approach would be using an "offline" database and
> > synchronizing with the master database. (We're currently working on this
> > approach in a customer project).
> 
> do you mean synchronizing Postgis with Postgis or Postgis with SpatiaLite?

I have SpatiaLite as offline storage in my mind. But since the next Postgres 
version has replication built-in, it will be an interesting option as well.

> 
> >> I also remember that Sandro Santilli once suggested a keyword
> >> substitution parameter for the psql provider so one could use simplify
> >> based on map resolution/map scale. This should also help to speed up
> >> data transfer on slower connections
> >> (http://lists.osgeo.org/pipermail/qgis-developer/2010-May/009923.html) -
> >> unfortunately noone answered to his mail. I think the proposal would be
> >> interesting to follow.
> > 
> > You're asking for something like http://tinyurl.com/pgvar I think. QGIS
> > could provide useful variables (like :scale) and offer a possibility to
> > configure the select statements.
> 
> yes - that's what I was looking for. Scale, and in the future maybe also
> time or potentially other variables specific to a certain map
> extent/view could be defined. The select statement would have to
> "digest" those variables and use them for something meaningful.
> 
> > Another way to achieve this could be using custom variables like in
> > http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_var
> > iables
> 
> thanks for this other link!

When QGIS defines a set of custom variables, PostGIS could eventually integrate 
new query functions, which other client would also benefit from. A good 
discussion topic for Barcelona...

Pirmin

-- 
Pirmin Kalberer
Sourcepole  -  Linux & Open Source Solutions
http://www.sourcepole.com