[Qgis-user] DB Manager in QGIS 2.8

[Alexandre Neto]

Hello David,

Can I assume that you use fires.id as unique integer value column in
DBManager to load the layer?

Because with your query you will likely get repeated values for fires.id.
That is, each fires feature will appear as many times as lakes within 1000
meters.

If that's what you want, you can use row_number() function to create a
fictional id for your layer. Something like this:

WITH my_query as
(
SELECT fires.id, fires.inci_no, fires.descript, fires.geom
FROM lab.mpls_fires_2014 fires
INNER JOIN lab.mpls_lakes lakes
ON ST_DWITHIN(fires.geom, lakes.geom, 1000)
)
SELECT
    ROW_NUMBER() OVER() as id,
    my_query.*
FROM my_query;

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:

SELECT *DISTINCT* fires.id, fires.inci_no, fires.descript, fires.geom
FROM lab.mpls_fires_2014 fires
INNER JOIN lab.mpls_lakes lakes
ON ST_DWITHIN(fires.geom, lakes.geom, 1000)

Best regards,

Alexandre Neto


Em 19/03/2015 03:25, "David Fawcett" <david.fawcett at gmail.com> escreveu:

> Thanks for the response Alexandre.
>
> Here is the query:
>
> SELECT fires.id, fires.inci_no, fires.descript, fires.geom
> FROM lab.mpls_fires_2014 fires
> INNER JOIN lab.mpls_lakes lakes
>       ON ST_DWITHIN(fires.geom, lakes.geom, 1000)
>
> When I add ST_GEOMETRYTYPE(fires.geom) to the query, the value is
> St_MultiPoint for all records.
>
> David.
>
> On Wed, Mar 18, 2015 at 5:26 PM, Alexandre Neto <senhor.neto at gmail.com>
> wrote:
>
>> Can you please show your sql query?
>>
>> 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().
>>
>> Best regards,
>>
>> Alexandre Neto
>> Em 18/03/2015 18:52, "David Fawcett" <david.fawcett at gmail.com> escreveu:
>>
>>> I am running QGIS 2.8.1 on OSX 10.9.5 using William's binaries.
>>>
>>> The DB Manager plugin is at 0.1.20.
>>>
>>> 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.
>>>
>>> I get the little black and white 'thinking beachball', but nothing else
>>> happens.
>>>
>>> Can anyone else confirm this?  If so, I can file an issue.
>>>
>>> David.
>>>
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150319/7cc557e5/attachment.html>