[Qgis-user] Very long sql in postgres layer definition

[Bernhard Ströbl]

Hi Samuel,

you could use the layer filter, couldn't you? It's the second last 
parameter in the setDataSource method.

Bernhard

Am 26.05.2016 um 12:23 schrieb Samuel Ingarfield:
> Good idea, but I need to be able to vary a parameter in the sql expression
> to filter the dataset differently as the user requires.
> Filtering needs to happen on the sql server, the dataset is far too large
> to filter directly within qgis (roughly 100mil rows).
>
> Sam.
>
> On Thu, May 26, 2016 at 4:20 PM, Bernhard Ströbl <bernhard.stroebl at jena.de>
> wrote:
>
>> Maybe it's easier to create a view on the DB and load that?
>>
>> Bernhard
>>
>>
>> Am 26.05.2016 um 09:52 schrieb Samuel Ingarfield:
>>
>>> Hi Bernhard;
>>>
>>> I can load the layer using an identical query in the DB manager sql
>>> window.
>>> Yes, r.id is a unique key.
>>>
>>> Sam.
>>>
>>> On Thu, May 26, 2016 at 3:37 PM, Bernhard Ströbl <
>>> bernhard.stroebl at jena.de>
>>> wrote:
>>>
>>> Can you load it via the "add PostGIS layer" button?
>>>> Is r.id a suitable key (unique)?
>>>>
>>>> Bernhard
>>>>
>>>>
>>>> Am 26.05.2016 um 09:33 schrieb Samuel Ingarfield:
>>>>
>>>> Hi All,
>>>>>
>>>>> I'm trying to load a postigs layer via python which is defined by a very
>>>>> long sql query. It seems that Qgis is truncating the query (which
>>>>> obviously
>>>>> causes it to fail). Has anyone come across this before and come up with
>>>>> a
>>>>> solution? Or am I loading the layer incorrectly?
>>>>>
>>>>> There is an unanswered question on stackexchange regarding this:
>>>>>
>>>>>
>>>>> http://gis.stackexchange.com/questions/162409/how-to-load-layers-from-postgres-using-select-statement
>>>>>
>>>>> My python code is very simple:
>>>>> sql = 'a very long query'
>>>>> uri=QgsDataSourceURI()
>>>>> uri.setConnection(host,"5432",dbname,user,passwd)
>>>>> uri.setDataSource("",sql,"geom","","r.id")
>>>>> vlayer = QgsVectorLayer(uri.uri(),"layer_name","postgres")
>>>>> QgsMapLayerRegistry.instance().addMapLayer(vlayer)
>>>>>
>>>>> The debug log shows the following:
>>>>> qgsmessagelog.cpp(45) : (QgsMessageLog::logMessage) [1ms]
>>>>> 2016-05-26T15:16:14 PostGIS[1] NOTICE: NOTICE:  identifier "...long
>>>>> query
>>>>> here..." will be truncated to "...truncated query here..."
>>>>> qgsmessagelog.cpp(45) : (QgsMessageLog::logMessage) [1ms]
>>>>> 2016-05-26T15:16:14 PostGIS[1] Erroneous query: ...etc
>>>>>
>>>>> Any help would be great.
>>>>> Sam.
>>>>>
>>>>>
>>>>>
>>>>>
>>
>> __________ Information from ESET Mail Security, version of virus signature
>> database 13547 (20160526) __________
>>
>> The message was checked by ESET Mail Security.
>> http://www.eset.com
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>
>
>
> __________ Information from ESET Mail Security, version of virus signature database 13548 (20160526) __________
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>



__________ Information from ESET Mail Security, version of virus signature database 13548 (20160526) __________

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