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

[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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20160526/fd973685/attachment.html>