4

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

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;

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 layer of polygons has roughly ~20'000'000 quite simple features, but loading the whole layer at once is painful and laggy. That's precisely why I'm searching to build a virtual layer: to limit the number of features arriving into QGIS and letting PG manage this.

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

But when I want to either "Test" or "Add" the virtual layer, QGIS becomes unresponsive and I finally have to force it down by killing the process.

After some tests I've noticed that testing the query without the WHERE statement is giving encouraging results:

encouraging results without the WHERE statement

But when trying to actually "Add" the layer, QGIS freezes again (I tried to wait ~20min or so but everything stays frozen with a gray background, where the query returns less than 10 small polygons within psql or pgAdmin4).

On the other hand, when the WHERE statement is present, pressing the "Test" button itself makes QGIS unresponsive, the "No error" popup window never comes on the screen and I have to kill the program.

This behaviour is the same in every cases; having the "Embedded Layers" selected or not in the upper part of the Virtual Layer window but in every cases, the required layers are already loaded in the table of content of my project.

Any hints on what's going on with this issue? Is this a know bug or am I doing something wrong?


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
4
  • I agree, this should work, but as a workaround, could it be possible to add the geometry layer and the join table separately in qgis and then do the join in qgis or make a view on the PostgreSQL server and read that into qgis? (That is something I do quite often, works great) Commented May 10, 2022 at 6:30
  • The LEFT JOIN here if you specify WHERE fk_i_id = 10 isn't useful, you can maybe try to do an INNER JOIN. Commented May 10, 2022 at 8:23
  • Do you have indexes on the fids etc? It's probably worth run explain analyze on the query in postgres
    – Ian Turton
    Commented May 10, 2022 at 8:25
  • Are your keys actually unique? Are you really doing a 1:1 join or 1:N?
    – BJW
    Commented Apr 7 at 11:16

2 Answers 2

0

I had the same issue. I uninstalled my version of QGIS of 3.32.3 and downloaded the latest at 3.34.0. Issue fixed.

0

I am not sure about the PostGIS part. But looks like this is happening because of file size. It may take longer time to load and then draw shapes in canvas. Last time when I wanted to test large shapefile in QGIS I did following, and it was faster.

  1. Create empty layer group.
  2. Uncheck this group so that by default the layer doesn't get showed up in map.
  3. Now add layer in this group.
  4. Add base map and zoom to added layer.
  5. Here you can check extent of your layer (.vrt).
  6. If extent is too large zoom to smaller extend. apply simple symbology such as black line for all features.
  7. Tick on Group layer and shapefile to visualize in smaller extent.

Trick is to now let QGIS make Draw layer to full extent.

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.