Virtual Layer query connection failed at reload project

Question

I have a QGIS project that generates a layer with point geometry from a database (Excel sheet) that I load directly into QGIS. To this table a join is established with a common field of a GPKG point geometry layer.

The virtual layer is created with the option Layer / Add layer / Add-Edit virtual layer and use MakePoint(longitude, latitude, CRS) to create the geometry:

SELECT myID, 
 myField, 
 MakePoint(myLong,myLat,3857) as geometry
FROM myTable

The point geometry layer is created as expected. The problem appears when I want to recover (open) the project after having closed it. There seems to be some problem retrieving the query.

I have added an animated screenshot that tries to show the workflow:

screenshot

Answer 1

I don't know why the virtual layer is failing at reload. But as a work around I think you could include the join logic directly in the virtual layer. Try this for the virtual layer definition:

SELECT DOMCOD, GEOX_CARTO.GEOMETRY as Geometry FROM POSTALCODE1 
JOIN GEOX_CARTO ON POSTALCODE1.REF14 = GEOX_CARTO.Campo2

With this query the virtual layer joins the POSTALCODE1 table to the GEOX_CARTO table and retrieves the point geometry directly. No need to create the join in the the Layer Properties to get the lat and long and then create a point from the coordinates.

• Acperience: Solved. Thank you. However, I am still in doubt as to why the solution I was using so far in my projects has stopped working. Also the solution based on the MakePoint function has better fast loading performance on tables with more than 10,000 records. The error message displayed is 'Handled Unavailable Layer', and the query is ?query=SELECT%20DOMCOD,%0D%0AMakePoint(GEOX_CARTO_XCOORD,GEOX_CARTO_YCOORD,25831)%20as%20geometry%0D%0Afrom%20POSTALCODE1 Anybody have any idea why it's failed?