<div dir="ltr"><div><div><div><div><div><div><div><div><div><div>Alexandre, <br><br></div>Thank you very much. Of course, it was operator error! <br><br></div>I was writing a quick query for a demo and not to solve a real problem, so I hadn't really thought through the issue with unique results. Adding DISTINCT made it work as expected. <br><br></div>A great enhancement to DB Manager would be some sort of error reporting, even if it was minimal. <br><br></div>Like:<br></div>- "db error", The database reported an error with your query.<br></div>- "QGIS error", There is something about your result set that prevents QGIS from adding it to the map.<br><br></div>Even better would be an error about the selected id column not containing unique values.<br><br></div>With no error message reported, I just assumed that the plugin might not be completely compatible with the new 2.8 release. (when really it was my error...)<br><br></div>Thanks again. The QGIS integration with PostGIS is really valuable and powerful.<br><br></div>David.<br><div><div><div><div><br></div></div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Mar 19, 2015 at 6:18 AM, Alexandre Neto <span dir="ltr"><<a href="mailto:senhor.neto@gmail.com" target="_blank">senhor.neto@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><p dir="ltr">Hello David,</p><p dir="ltr">Can I assume that you use <a href="http://fires.id" target="_blank">fires.id</a> as unique integer value column in DBManager to load the layer?</p><p dir="ltr">Because with your query you will likely get repeated values for <a href="http://fires.id" target="_blank">fires.id</a>. That is, each fires feature will appear as many times as lakes within 1000 meters.<br></p><p>If that's what you want, you can use row_number() function to create a fictional id for your layer. Something like this:</p><p><font face="monospace, monospace">WITH my_query as<br></font><span class=""><span style="font-family:monospace,monospace">(<br>SELECT </span><a href="http://fires.id/" style="font-family:monospace,monospace" target="_blank">fires.id</a><span style="font-family:monospace,monospace">, fires.inci_no, fires.descript, fires.geom<br></span><span style="font-family:monospace,monospace">FROM lab.mpls_fires_2014 fires<br></span><span style="font-family:monospace,monospace">INNER JOIN lab.mpls_lakes lakes<br></span></span><span style="font-family:monospace,monospace">ON ST_DWITHIN(fires.geom, lakes.geom, 1000)<br>)<br></span><span style="font-family:monospace,monospace">SELECT<br></span><span style="font-family:monospace,monospace"> ROW_NUMBER() OVER() as id,<br></span><span style="font-family:monospace,monospace"> my_query.*<br></span><span style="font-family:monospace,monospace">FROM my_query;</span></p><p>But, if I understand your problem, you want to show all fires that occurred within 1000 m of a lake. In that case, each fire should only appear once. You can use DISTINCT for that:<br></p><p><font face="monospace, monospace">SELECT <b>DISTINCT</b> <a href="http://fires.id/" target="_blank">fires.id</a>, fires.inci_no, fires.descript, fires.geom<span class=""><br>FROM lab.mpls_fires_2014 fires<br>INNER JOIN lab.mpls_lakes lakes<br>ON ST_DWITHIN(fires.geom, lakes.geom, 1000)</span></font><br></p><p>Best regards,</p><p>Alexandre Neto</p><p><br></p>
<div class="gmail_quote">Em 19/03/2015 03:25, "David Fawcett" <<a href="mailto:david.fawcett@gmail.com" target="_blank">david.fawcett@gmail.com</a>> escreveu:<div><div class="h5"><br type="attribution"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div dir="ltr"><div><div><div>Thanks for the response Alexandre. <br><br></div>Here is the query:<br><br>SELECT <a href="http://fires.id" target="_blank">fires.id</a>, fires.inci_no, fires.descript, fires.geom<br>FROM lab.mpls_fires_2014 fires<br>INNER JOIN lab.mpls_lakes lakes<br> ON ST_DWITHIN(fires.geom, lakes.geom, 1000)<br><br></div>When I add ST_GEOMETRYTYPE(fires.geom) to the query, the value is St_MultiPoint for all records.<br><br></div>David.<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Mar 18, 2015 at 5:26 PM, Alexandre Neto <span dir="ltr"><<a href="mailto:senhor.neto@gmail.com" target="_blank">senhor.neto@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><p dir="ltr">Can you please show your sql query?</p>
<p dir="ltr">Notice that if your query result in different geometries, qgis won't be able to choose a geometry type for the layer. I advice you to Check your results with St_GeometryType().</p>
<p dir="ltr">Best regards,</p>
<p dir="ltr">Alexandre Neto</p>
<div class="gmail_quote">Em 18/03/2015 18:52, "David Fawcett" <<a href="mailto:david.fawcett@gmail.com" target="_blank">david.fawcett@gmail.com</a>> escreveu:<br type="attribution"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><div><div dir="ltr"><div><div><div><div><div>I am running QGIS 2.8.1 on OSX 10.9.5 using William's binaries. <br><br></div>The DB Manager plugin is at 0.1.20.<br><br></div>I can load layers into PostGIS using the DB Manager plugin, and I can use the SQL window to execute a spatial query intersecting two tables. But, when I check the box to load the query result as a layer, fill out the controls, and hit Load Now!, the layer isn't added. <br><br></div>I get the little black and white 'thinking beachball', but nothing else happens.<br><br></div>Can anyone else confirm this? If so, I can file an issue.<br><br></div>David.<br></div>
<br></div></div>_______________________________________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br></blockquote></div>
</blockquote></div><br></div>
</blockquote></div></div></div>
</div>
</blockquote></div><br></div>