[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Can't load aggregation query in qgis - row_number() over() as id
From: Pietro Rossin <pierigis () gmail ! com>
Date: 2014-03-28 11:27:44
Message-ID: CAPwOVCEbJQiJ9d74MktK1WhsgVO63A7MWbqSFCiHENarm0rZJQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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_view \
> > > > er&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicName \
> > > > space-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNa \
> > > > mespace&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&breadcrumb \
> s=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.
[Attachment #5 (text/html)]
<div dir="ltr"><div><div>St_isvalid() is "t" for all \
features..<br></div>I'll try the rest..<br></div>Thanks! <br></div><div \
class="gmail_extra"><br><br><div class="gmail_quote">2014-03-28 11:47 GMT+01:00 Rémi \
Cura [via PostGIS] <span dir="ltr"><<a \
href="/user/SendEmail.jtp?type=node&node=5005989&i=0" target="_top" rel="nofollow" \
link="external">[hidden email]</a>></span>:<br> <blockquote style='border-left:2px \
solid #CCCCCC;padding:0 1em' class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">
<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><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="http://user/SendEmail.jtp?type=node&node=5005986&i=0" rel="nofollow" \
link="external" target="_blank">[hidden email]</a>></span>:<br>
<blockquote style='border-left:2px solid #CCCCCC;padding:0 1em' \
style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div \
class=""><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><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' \
style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div \
class=""><div> <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><div \
class="gmail_extra"><br><br><div class="gmail_quote"><div>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><div><div class="h5"><blockquote style='border-left:2px solid #CCCCCC;padding:0 \
1em' 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>I'm not an \
expert, <br></div></div><div>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>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> Try \
:<br>SELECT *<br>FROM (<br></div></div><div><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><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' \
style="border-left:2px solid #cccccc;padding:0 1em" \
class="gmail_quote"><div><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><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" target="_top">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.BasicNamesp \
ace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&am \
p;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></div></div><br></div> \
</blockquote></div><br></div><div>
<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" \
rel="nofollow" link="external" target="_blank">Re: Can't load aggregation query \
in qgis - row_number() over() as id</a><br>
</div><div class=""><div><div>
Sent from the <a href="http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html" \
rel="nofollow" link="external" target="_blank">PostGIS - User mailing list \
archive</a> at Nabble.com.<br></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="http://user/SendEmail.jtp?type=node&node=5005986&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></div></blockquote></div><br></div>
<div class="">
<br>_______________________________________________
<br>postgis-users mailing list
<br><a href="http://user/SendEmail.jtp?type=node&node=5005986&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><div style="color:#444;font:12px \
tahoma,geneva,helvetica,arial,sans-serif"><div class=""> <div \
style="font-weight:bold">If you reply to this email, your message will be added to \
the discussion below:</div> </div><a \
href="http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005986.html" \
target="_blank" rel="nofollow" \
link="external">http://postgis.17.x6.nabble.com/Can-t-load-aggregation-query-in-qgis-row-number-over-as-id-tp5005981p5005986.html</a>
</div><div class="HOEnZb"><div class="h5">
<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 href="" target="_blank" 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.BasicNamesp \
ace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&am \
p;breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml" \
rel="nofollow" style="font:9px serif" target="_blank" link="external">NAML</a> \
</div></div></div></blockquote></div><br></div>
<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-tp5005981p5005989.html">Re: \
Can't load aggregation query in qgis - row_number() over() as id</a><br/> Sent from \
the <a href="http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html">PostGIS - \
User mailing list archive</a> at Nabble.com.<br/>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic