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

[Rémi Cura]

Maybe you should check that the result of you querry is valid geometry
(ST_Isvalid()).
Another think is you could force your query output geometry type to multi
of one type,
for example : ST_Multi(ST_CollectionExtract(geom,2)) enforce that output
type is multi line.

Cheers,
Rémi-C


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

> Sorry the second link is this
> https://dl.dropboxusercontent.com/u/15885512/strangequeryresult2.jpg
>
>
> 2014-03-28 11:14 GMT+01:00 Pietro Rossin <[hidden email]<http://user/SendEmail.jtp?type=node&node=5005984&i=0>
> >:
>
>> 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] <[hidden email]<http://user/SendEmail.jtp?type=node&node=5005984&i=1>
>> >:
>>
>>> 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.
>>> 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: Re: Can't load aggregation query in qgis -
> row_number() over() as id<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<http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html>at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140328/e53a8d68/attachment.html>