10

I am trying to load a set of queried data into the map window but the layer will not load.

I have a query that has successfully run returning data from two tables.

When I select to 'Load as new layer', set the columns, geometry column and layer prefix nothing loads and there is no error report or explanation as to why not.

I saw else where that you need to make sure a geometry column is returned in you query which as you will see from the image below one is.

Not sure why I can;t display the layer?

enter image description here

8
  • When you click on the laid now button, what happens?
    – whyzar
    Nov 25, 2016 at 0:44
  • Nothing I can notice. It has a 'think', for want of a better term, for a second or two but nothing is loaded or changes.
    – Ben
    Nov 25, 2016 at 0:45
  • I would try closing out of DB Manager, then try once more. See how that turns out.
    – whyzar
    Nov 25, 2016 at 0:46
  • I have tried closing down, restarting my computer and re-loading the process in a fresh work space to no avail. I should add that I can load for a simple query on the table which holds the 'geom' column but when I conduct this query joining data from 2 tables it does not load.
    – Ben
    Nov 25, 2016 at 0:48
  • Have you been able to load a new layer previously.?
    – whyzar
    Nov 25, 2016 at 0:53

5 Answers 5

7

As an aside to anyone else who come across this, I found that when I changed my query to select specific columns, not select all columns, I was then able to load the layer.

I can't say why exactly but I believe that by selecting all the columns I was returning two identically names columns (boreid) in the query result. When I amend the query to not return the boreid column from the second table the subsequent data will load as a layer.

1
  • Necro-commenting to confirm. Yes, the duplicate columns after a join is what was causing my issues. Thanks for your answer. Dec 21, 2018 at 15:39
1

I also experienced this problem, and it was because the field I indicated that was supposed to have unique values did not in fact have unique values. After fixing that, everything went as expected. Not very friendly to not get an error message-

1

I had similar problem in QGIS Madeira and I solved it by changing in DB Manager plug-in the getExecutableSQLQuery() to getSqlExecutableQuery() (there are 4 instances in the file).

So just go to C:\Program Files\QGIS 3.4\apps\qgis-ltr\python\plugins\db_manager, open the file dlg_sql_window.py and with find and replace change the above and save (with QGIS program not active).

1
  • Ok, I tried doing this with the Network Install and (a) there were only 3 instances of getExecutableSQLQuery() and (b) it still threw up an error - however upgrading to the latest LTR/3.7.1 worked as this bug has been fixed.
    – she_weeds
    Apr 3, 2019 at 2:58
0

On both QGIS Wien 2.8 and QGIS 3.4 Madeira, my problem was a bit different. Though I tried logging the error to get more details, I only got repeating lines of:

PostGIS(1): invalid PostgreSQL layer

Connecting as a database-user other than postgres fixed the problem.

I'm not sure what goes on under the hood for that to make a difference, but it appears that's the case.

1
  • not working for me Oct 21, 2021 at 21:32
0

I had this problem because I had a semicolon (;) at the end of my query. This messes up how QGIS was loading the query. I found this out when I learned that you can view logs in QGIS while trying to figure this out. Nice!

This was what I saw in the logs:

2022-06-09T18:15:28     WARNING    Unable to execute the query.
             The error message from the database was:
             ERROR: syntax error at or near ";"
             LINE 1: ... on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2) limit 10;
              ^
             .
             SQL: SELECT * FROM (SELECT row_number() over () AS _uid0_, * FROM (select cs.* from class_segments as cs join shorelines as s on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2) limit 10;
             ) AS _subq_0_
             ) AS "subQuery_0" LIMIT 1

which clued me into the fact that the semicolon was interrupting the query that was being injected.

You can view logs panel with the little message icon at the bottom right of the QGIS window.

So, when I changed

select cs.* from class_segments as cs join shorelines as s on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2);

to

select cs.* from class_segments as cs join shorelines as s on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2)

It worked!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.