<div dir="ltr"><div><div>Maybe you should check that the result of you querry is valid geometry <br></div>(ST_Isvalid()).<br></div>Another think is you could force your query output geometry type to multi of one type,<br>for example : ST_Multi(ST_CollectionExtract(geom,2)) enforce that output type is multi line.<br>
<br>Cheers,<br>Rémi-C<br></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-03-28 11:15 GMT+01:00 Pietro Rossin <span dir="ltr"><<a href="mailto:pierigis@gmail.com" target="_blank">pierigis@gmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Sorry the second link is this<br><a href="https://dl.dropboxusercontent.com/u/15885512/strangequeryresult2.jpg" rel="nofollow" link="external" target="_blank">https://dl.dropboxusercontent.com/u/15885512/strangequeryresult2.jpg</a><br>
</div><div class="gmail_extra">
<br><br><div class="gmail_quote">2014-03-28 11:14 GMT+01:00 Pietro Rossin <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5005984&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span>:<br>
<blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div class="">
<div dir="ltr"><div><div><div><div><div><div>Thanks Remi<br></div>of course I can materialize the query, but I don't want to create n new tables..<br></div>The incapsulation don't work<br><br></div>I found this to work but with strage results<br>
<br>SELECT row_number() over() as id, ST_CollectionHomogenize(st_collect(geom)) as geom, f1, f2<br> FROM schema.table<br> group by 3,4<br><br></div>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..<br>
</div>Look at this<br><a href="https://dl.dropboxusercontent.com/u/15885512/strangequeryresult1.jpg" rel="nofollow" link="external" target="_blank">https://dl.dropboxusercontent.com/u/15885512/strangequeryresult1.jpg</a><br>
</div>and this<br><a href="https://dl.dropboxusercontent.com/u/15885512/strangequeryresult1.jpg" rel="nofollow" link="external" target="_blank">https://dl.dropboxusercontent.com/u/15885512/strangequeryresult1.jpg</a><br>
<br>????<br><div><div><div><br><br><br></div></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div class="">2014-03-28 10:21 GMT+01:00 Rémi Cura [via PostGIS] <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5005984&i=1" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span>:<br>
</div><blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div>
<div dir="ltr"><div><div><div><div><div><div>Hey,<br></div><div class="">I'm not an expert, <br></div></div><div class="">I would say the first thing would be to create a table with this query,<br>then try to load the result into qgis.<br>
<br>
</div></div><div class="">Another option is to encapsulate your group by into a sub querry, QGis might be confused by the number of rows.<br>
<br></div></div><div class=""> Try :<br>SELECT *<br>FROM (<br></div></div><div class=""><div>
SELECT row_number() over() as id, st_collect(geom) as geom, field3, field4,<br>
field5, field6<br>
FROM schema.table<br>
where field6 is not null<br>
group by 3,4,5,6<br></div></div></div><div class=""><div>) AS sub<br><br>Cheers,<br>Rémi-C<br><div><div><div><br></div></div></div></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-03-27 17:27 GMT+01:00 Pietro Rossin <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5005982&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span>:<br>
<blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div class=""><div><div>Hello all<br>
I need to aggregate geometries and load them in qgis<br>
<br>
I made a query like this, without success..<br>
<br>
SELECT row_number() over() as id, st_collect(geom) as geom, field3, field4,<br>
field5, field6<br>
FROM schema.table<br>
where field6 is not null<br>
group by 3,4,5,6<br>
<br>
Qgis don't load the layer with message:<br>
<br>
"layer PostgreSQL not valid - level 1"<br>
<br>
Where am I wrong?<br>
Thank you all<br>
Pietro<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981.html" rel="nofollow" link="external" target="_blank">http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981.html</a><br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
_______________________________________________<br>
postgis-users mailing list<br>
</div></div></div><a href="http://user/SendEmail.jtp?type=node&node=5005982&i=1" rel="nofollow" link="external" target="_blank">[hidden email]</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br></div><div class=""><div>
<br>_______________________________________________
<br>postgis-users mailing list
<br><a href="http://user/SendEmail.jtp?type=node&node=5005982&i=2" rel="nofollow" link="external" target="_blank">[hidden email]</a>
<br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
<br>
<br>
<hr color="#cccccc" noshade size="1">
<div style="color:#444;font:12px tahoma,geneva,helvetica,arial,sans-serif">
<div style="font-weight:bold">If you reply to this email, your message will be added to the discussion below:</div>
<a href="http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005982.html" rel="nofollow" link="external" target="_blank">http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005982.html</a>
</div>
<div style="color:#666;font:11px tahoma,geneva,helvetica,arial,sans-serif;margin-top:.4em;line-height:1.5em">
To unsubscribe from Can't load aggregation query in qgis - row_number() over() as id, <a rel="nofollow" link="external">click here</a>.<br>
<a href="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" rel="nofollow" style="font:9px serif" link="external" target="_blank">NAML</a>
</div></div></div></blockquote></div><br></div>
</blockquote></div><br></div><div class="im HOEnZb">
<br><hr align="left" width="300">
View this message in context: <a href="http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005984.html" target="_blank">Re: Can't load aggregation query in qgis - row_number() over() as id</a><br>
</div><div class="HOEnZb"><div class="h5">
Sent from the <a href="http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html" target="_blank">PostGIS - User mailing list archive</a> at Nabble.com.<br></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>