[Qgis-developer] Postgis-Provider and compression

[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.

> 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 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_variables
>    

thanks for this other link!

Andreas