Re: [Qgis-user] DB Manager in QGIS 2.8
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.fawc...@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.n...@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.fawc...@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] DB Manager in QGIS 2.8
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.n...@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.fawc...@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.n...@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.fawc...@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] DB Manager in QGIS 2.8
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.fawc...@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] DB Manager in QGIS 2.8
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.n...@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.fawc...@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] DB Manager in QGIS 2.8
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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
