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

Fri Mar 28 04:27:44 PDT 2014

St_isvalid() is "t" for all features..
I'll try the rest..
Thanks!

2014-03-28 11:47 GMT+01:00 Rémi Cura [via PostGIS] <
ml-node+s17n5005986h90 at n6.nabble.com>:

> 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 <[hidden email]<http://user/SendEmail.jtp?type=node&node=5005986&i=0>
> >:
>
>> 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
>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5005986&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=5005986&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-tp5005981p5005986.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-tp5005981p5005989.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/8be6a11e/attachment.html>