QGIS Virtual Layer – Create Virtual Layer That Retains Source Field Types

qgisvirtual-layer

In a previous question that I posted, the answer that I accepted involved creating a Virtual Layer from a source layer. The query used to create the Virtual Layer was:

select *  
from my_layer  
order by field_a

Although the query works as intended, some of the source field types are converted to different types in the Virtual Layer. Specifically:

Integer (32 bit) are converted to Integer (64 bit)
Date and Date&Time are converted to Text (string)
Boolean are converted to Integer (64 bit)

I use the Refactor Fields to get the field types back to their original, but this involves another step that I would prefer to eliminate.

Question: How to create a Virtual Layer that retains the source field types?

Best Answer

Virtual layers lack field type support for Date, Time, and Boolean. See what Virtual layer context help says:

where type can be any of int, real or text

Related Solutions

QGIS Vector Layer – Creating Dynamic Vector Layer or Virtual Layer in QGIS

Assuming you want each of the points to have an identical geometry, you could use a recursive CTE and generate a row (with unique id) for each layer as such:

WITH seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq 
WHERE n + 1 <= (SELECT no_of_rasters FROM count_layer))

SELECT n, make_point(47,15) AS geometry FROM seq

As for assigning a raster name to each generated point, to my knowledge you can't do that in Virtual Layers, but you could set up a virtual field on the virtual layer with the following expression (where n is the sequence generated in the virtual layer):

array_get(
         array_foreach(
                       array_filter(@layers,
                                    layer_property(@element,'type') =  'Raster'),
                       layer_property(@element,'name')
         ),"n"-1)

This just extracts the raster name from the position in the @layers array corresponding to n - 1 (because zero-index).

Example:

Best Answer

Related Solutions

QGIS Vector Layer – Creating Dynamic Vector Layer or Virtual Layer in QGIS

Related Question