[Qgis-user] Problem with "add postgis layers" in qgis 1.80

[Bernhard Ströbl]

Hi,

I can confirm long detection times when connecting to Postgres (~ 25 
seconds) using QGIS 1.8.0 compared to 1.7.4. I have some 50 schemas with 
both, tables and views in a PostgreSQL 8.4 db. Most tables contain only 
several hundred to several thousand features.

It seems QGIS 1.8 scans all views not registered in geometry_columns for 
their geometry type like that: SELECT DISTINCT CASE WHEN 
upper(geometrytype("the_geom")) IN 
('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN 
upper(geometrytype("the_geom")) IN 
('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 
'LINESTRING' WHEN upper(geometrytype("the_geom")) IN 
('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' 
END, srid("the_geom") FROM "schema"."view"
So if I tick "Only look in geometry_columns table" for the connection 
connecting takes 1 to 2 seconds! I assume that the same can be achieved 
by adding all views to geometry_columns.

QGIS 1.7 scanned like that: select distinct case when 
upper(geometrytype("the_geom")) IN ('POINT','MULTIPOINT') THEN 'POINT' 
when upper(geometrytype("the_geom")) IN ('LINESTRING','MULTILINESTRING') 
THEN 'LINESTRING' when upper(geometrytype("the_geom")) IN 
('POLYGON','MULTIPOLYGON') THEN 'POLYGON' end from (select "the_geom" 
from "schema"."view" where "the_geom" is not null limit 100) as t

Therefore QGIS 1.7.4 loads the view with an sql-query e.g. 
"upper(geometrytype("the_geom")) IN ('POLYGON','MULTIPOLYGON')" whereas 
1.8 does not. So 1.7 only checked 100 features and detected their 
geometry type and made sure that only fitting types are loaded by 
setting an appropriate query.

Also QGIS 1.7 offered the possibility to already open a schema while 
still detecting. The type said "waiting" for the views in question but 
you could nevertheless load a layer where the geometry type was already 
detected (e.g. because it is registered in geometry_columns).

Could one of the devs explain why this was changed?

Bernhard

Am 19.07.2012 10:17, schrieb Andre Joost:
> Am 19.07.12 10:09, schrieb Giovanni Manghi:
>> On Thu, 2012-07-19 at 07:14 +0200, Siegfried.Blank at lazbw.bwl.de wrote:
>>> Hallo
>>>
>>> ‚add postgis layers“ is very, very slowly in qgis 1.80 when I have
>>> many tables. It needs several (up to 10) minutes until all tables
>>> where “detecting” every time when I want add a postgis layer.
>>>
>>> Thanks for tricky tips to make it faster!
>>
>>
>> Hi,
>>
>> I have DB with 10000+ tables (no views for now) in it and many different
>> schemas, and the table list shows in a just 2/3 seconds.
>>
>
> Not quiet the same for me:
> I use a postgis database with openstreetmap data of my region. The table
> for (multi-)polygons takes a lot of time to detect the type of elements.
> Only one table, but lots of elements in there (one for every building...)
>
> Greetings,
> Andre Joost
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user




__________ Information from ESET Mail Security, version of virus signature database 7311 (20120719) __________

The message was checked by ESET Mail Security.
http://www.eset.com