[postgis-users] Can't load aggregation query in qgis - row_number() over() as id

[Pietro Rossin]

Sorry the second link is this
https://dl.dropboxusercontent.com/u/15885512/strangequeryresult2.jpg


2014-03-28 11:14 GMT+01:00 Pietro Rossin <pierigis at gmail.com>:

> Thanks Remi
> of course I can materialize the query, but I don't want to create n new
> tables..
> The incapsulation don't work
>
> I found this to work but with strage results
>
> SELECT row_number() over() as id,
> ST_CollectionHomogenize(st_collect(geom)) as geom, f1, f2
>   FROM schema.table
>   group by 3,4
>
> With this query I cal loa my table as plylines, but many (not all) of them
> present segments that goes to 0,0 x,y..
> Look at this
> https://dl.dropboxusercontent.com/u/15885512/strangequeryresult1.jpg
> and this
> https://dl.dropboxusercontent.com/u/15885512/strangequeryresult1.jpg
>
> ????
>
>
>
>
>
> 2014-03-28 10:21 GMT+01:00 Rémi Cura [via PostGIS] <
> ml-node+s17n5005982h66 at n6.nabble.com>:
>
>> Hey,
>> I'm not an expert,
>> I would say the first thing would be to create a table with this query,
>> then try to load the result into qgis.
>>
>> Another option is to encapsulate your group by into a sub querry, QGis
>> might be confused by the number of rows.
>>
>>  Try :
>> SELECT *
>> FROM (
>> SELECT row_number() over() as id, st_collect(geom) as geom, field3,
>> field4,
>> field5, field6
>>   FROM schema.table
>>   where field6 is not null
>>   group by 3,4,5,6
>> ) AS sub
>>
>> Cheers,
>> Rémi-C
>>
>>
>>
>> 2014-03-27 17:27 GMT+01:00 Pietro Rossin <[hidden email]<http://user/SendEmail.jtp?type=node&node=5005982&i=0>
>> >:
>>
>>> Hello all
>>> I need to aggregate geometries and load them in qgis
>>>
>>> I made a query like this, without success..
>>>
>>> SELECT row_number() over() as id, st_collect(geom) as geom, field3,
>>> field4,
>>> field5, field6
>>>   FROM schema.table
>>>   where field6 is not null
>>>   group by 3,4,5,6
>>>
>>> Qgis don't load the layer with message:
>>>
>>> "layer PostgreSQL not valid - level 1"
>>>
>>> Where am I wrong?
>>> Thank you all
>>> Pietro
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981.html
>>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>> _______________________________________________
>>> postgis-users mailing list
>>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5005982&i=1>
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5005982&i=2>
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>> ------------------------------
>>  If you reply to this email, your message will be added to the
>> discussion below:
>>
>> http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005982.html
>>  To unsubscribe from Can't load aggregation query in qgis - row_number()
>> over() as id, click here<http://postgis.17.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5005981&code=cGllcmlnaXNAZ21haWwuY29tfDUwMDU5ODF8MjMwODY1MTQ5>
>> .
>> NAML<http://postgis.17.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
>




--
View this message in context: http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005984.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140328/fc45f191/attachment.html>