[Qgis-developer] Using group by to set a PostGIS datasource using QGIS Python API

[Ian Packham]

Wait - I was just working away in QGIS version 1.7.3 and my plugin code like below works, but not in my dev version (1.9.90):

Query="select param_id, st_union(the_geom) as the_geom from myTable group by param_id"
uri.setDataSource('', '(%s)' % postGisQuery, "the_geom",'','param_id')
vlayer1 = QgsVectorLayer(uri.uri(), 'testlayer1', "postgres") 
So it allowed the use of param_id as a key, even though it isn't a primary key. Has something changed in recent versions that doesn't allow such code? In fact there doesn't seem to be an error message in the Log Messages, QgsVectorLayer just doesn't load the layer - using QgsMapLayerRegistry.instance().addMapLayer(vlayer1)

Maybe I should check out the most recent version - or build 1.8 to check?

Ian

----- Original Message -----
From: Bernhard Ströbl <bernhard.stroebl at jena.de>
Date: Thursday, May 10, 2012 10:04 am
Subject: Re: [Qgis-developer] Using group by to set a PostGIS datasource using QGIS Python API
To: Ian Packham <i.packham at ucd.ie>
Cc: qgis-developer at lists.osgeo.org

> Hi Ian,
> 
> I am always using views for such things. I am not sure (never 
> tried) if you can use a subselect as table parameter for 
> uri.setDataSource.So I would create a view on the DB. Be sure, 
> that the id field of the view is defined as the pk in its source 
> table (no combined pk) and that the values in the id field are unique.
> 
> Bernhard
> 
> Am 10.05.2012 10:56, schrieb Ian Packham:
> >Hi
> >
> >I asked the following question at gis.stackexchange.com, but 
> had no luck
> >so far. Can any of you guys help?
> >
> >Ian
> >
> >http://gis.stackexchange.com/questions/24941/using-group-by-to-
> set-a-postgis-datasource-using-qgis-python-api
> >
> >I am writing a QGIS Plugin and wish to obtain data from a 
> PostGIS table
> >using the Group By clause using something like the following code:
> >
> >|uri=  QgsDataSourceURI()
> >uri.setConnection('localhost',  '5432',  
> 'postgis',  'postgres',  'xxx')
> >Query="select param_id, st_union(the_geom) as the_geom from 
> myTable group by param_id"
> >uri.setDataSource('',  '(%s)'  %  
> postGisQuery,  "the_geom",'','gid')
> >vlayer1=  QgsVectorLayer(uri.uri(),  
> 'testlayer1',  "postgres")
> >|
> >
> >This gives the error "No key field for query given." because it is
> >looking for the gid key field. But if gid was put in the select
> >statement of the Query, we won't get aggregated rows, just distinct
> >rows. If I try an aggregation function on gid, I get the same 
> error. If
> >I try aliasing the aggregate, I get an "Erroneous query" error (even
> >|count(gid) as gid|), same as if I put 'param_id' as the key 
> field in
> >setDataSource, because 'param_id' is not a primary key, I guess.
> >
> >In this case, forcing the key field in the uri seems to make 
> the group
> >by clause useless. Is there another workaround I haven't 
> thought of or
> >is this the expected behaviour?
> >
> >
> >
> >
> >
> >_______________________________________________
> >Qgis-developer mailing list
> >Qgis-developer at lists.osgeo.org
> >http://lists.osgeo.org/mailman/listinfo/qgis-developer
> >


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/qgis-developer/attachments/20120510/1de48609/attachment.html