3

I am trying to load a virtual layer from two existing PostGIS tables, using a simple LEFT JOIN statement:

SELECT t.id, t.poly
FROM table_t t
LEFT JOIN table_f f ON f.fk_poly_id = t.id 
WHERE fk_i_id = 10;

The table_f is a join table containing an id, and two foreign keys: fk_i_id and fk_poly_id.
The table_t is a standard table containing an id and the poly geometry that I'd like to see in my virtual layer for a feature i (referenced by its foreign key fk_i_id in the join table).

This query is perfectly working in psql or pgAdmin4 and returns me some geometric features (ST_Polygon - POLYGON Z) with their ids.

But when I add the two PostGIS tables that I need into the "Embedded layers" of the "Datasource Manager | Virtual Layer" window and when I paste the query in the dedicated space under, I keep getting this error when clicking either "Add" or the "Test" button:

Query preparation error on PRAGMA table_info(_tview): no such column: t.poly

I don't know what I may be doing wrong?
Is this a know bug?
I already tried to rewrite the query in plenty of way, including giving aliases or not, joining the other way, etc etc. All tests were working on pgAdmin but not in QGIS.


Here is the version information:

Name Version
QGIS version 3.24.2-Tisler
QGIS code revision 13c1a02865
Qt version 5.15.3
Python version 3.10.4
GDAL/OGR version 3.4.1
PROJ version 8.2.1
EPSG Registry database version v10.041 (2021-12-03)
GEOS version 3.10.2-CAPI-1.16.0
SQLite version 3.37.2
PDAL version 2.3.0
PostgreSQL client version unknown
SpatiaLite version 5.0.1
QWT version 6.1.4
QScintilla2 version 2.11.6
OS version Ubuntu 22.04 LTS

Follow up there: Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

1 Answer 1

4

As described in the doc, the geometry column is always called geometry. When you add a layer from PostGres, you choose which is the geometry column and after that the name geometry refers to this selected column.

The same is true when you defined a layer in the embedded layers section.

SELECT t.id, t.geometry
FROM table_t t
LEFT JOIN table_f f ON f.fk_poly_id = t.id 
WHERE fk_i_id = 10;
5
  • Well, this is good to know. Is it documented somewhere? I'm not successful in my researches... And then, I don't face the error anymore, but when I hit the "Add" button, QGIS is not responding (everything becomes gray and there is this popup telling me either to "Force quit" or "Wait") for such a long time I had to kill the process. May 9, 2022 at 17:30
  • 2
    Yes, there is one small note about the geometry column name (see the link in the edited answer). For the unresponsiveness, yes, it is a weakness of virtual layers: a small mistake can crash QGIS.. it is getting better with each version, though now I always save my work before touching a VL and I keep a copy of the query in a temp text file
    – JGH
    May 9, 2022 at 17:51
  • Thanks for you edits, that's clearer! I'm actually working the same way, but heck, there is not even a line printed in the console when I launch QGIS by the command line to get it a little more verbose. For the moment, I've only noticed that the "Test" button says "OK" only when I remove the "WHERE bla bla" statement... I don't know what's wrong with it... But I really need it, otherwise, even pgAdmin freeeze; because that's to many feature to query for. May 9, 2022 at 18:07
  • You have mentioned the embedded layers section, which is another not so documented and not so straightforward component. Try adding the tables/layers to the TOC and remove everything from the embedded layers section
    – JGH
    May 9, 2022 at 18:17
  • No changes: same behaviour. In order to keep things clean and clearly separate the questions -- because the current one is technically answered -- I've opened a new thread: gis.stackexchange.com/q/430804/65370 May 9, 2022 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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