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

whyzar
  • 12,053
  • 23
  • 38
  • 72
Ben
  • 899
  • 2
  • 9
  • 19
  • When you click on the laid now button, what happens? – whyzar Nov 25 '16 at 00: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 '16 at 00:45
  • I would try closing out of DB Manager, then try once more. See how that turns out. – whyzar Nov 25 '16 at 00: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 '16 at 00:48
  • Have you been able to load a new layer previously.? – whyzar Nov 25 '16 at 00:53
  • Yes, with the query in my original question above open I can start another query in DB Manager, query just the buildings_espo table and the query result will load as a new layer. I can then switch straight back to the query above, re-execute and attempt to load and nothing will happen. – Ben Nov 25 '16 at 00:56
  • Can you show the full geom column by expanding and posting anothe screen shot – whyzar Nov 25 '16 at 00:57
  • @whyzar I have posted an additional shot as an answer, it would not load as an edit into the original question. – Ben Nov 25 '16 at 01:30

5 Answers5

8

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.

Ben
  • 899
  • 2
  • 9
  • 19
  • Necro-commenting to confirm. Yes, the duplicate columns after a join is what was causing my issues. Thanks for your answer. – Hayden Elza Dec 21 '18 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-

chrismarx
  • 408
  • 4
  • 16
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).

Kadir Şahbaz
  • 76,800
  • 56
  • 247
  • 389
Stefania
  • 11
  • 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 03 '19 at 02: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.

Seldom 'Where's Monica' Needy
  • 121
  • 6
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!

Nick Brady
  • 148
  • 7