<div dir="ltr"><p dir="ltr">Hello David,</p><p dir="ltr">Can I assume that you use <a href="http://fires.id">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">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 style="font-family:monospace,monospace">(<br>SELECT </span><a href="http://fires.id/" target="_blank" style="font-family:monospace,monospace">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 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<br>FROM lab.mpls_fires_2014 fires<br>INNER JOIN lab.mpls_lakes lakes<br>ON ST_DWITHIN(fires.geom, lakes.geom, 1000)</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:<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>