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

[Pietro Rossin]

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-tp5005981p5005983.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/ea8df062/attachment.html>