[Qgis-user] DB Manager in QGIS 2.8

[David Fawcett]

Alexandre,

Thank you very much.  Of course, it was operator error!

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.

A great enhancement to DB Manager would be some sort of error reporting,
even if it was minimal.

Like:
- "db error", The database reported an error with your query.
- "QGIS error", There is something about your result set that prevents QGIS
from adding it to the map.

Even better would be an error about the selected id column not containing
unique values.

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...)

Thanks again.  The QGIS integration with PostGIS is really valuable and
powerful.

David.


On Thu, Mar 19, 2015 at 6:18 AM, Alexandre Neto <senhor.neto at gmail.com>
wrote:

> 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/4b5dc7c5/attachment.html>