7

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?

5
  • Have you tried a cast (e.g. mydate::DATE) for those fields?
    – jpinilla
    Mar 19, 2023 at 9:05
  • 3
    See what Virtual layer context help says: i.stack.imgur.com/QAz7E.png "where type can be any of int, real or text". So this seems to suggest that virtual layers do not support fieldtypes like Date&Time or Boolean, but I might be wrong. See also here: docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/…
    – Babel
    Mar 19, 2023 at 10:11
  • 2
    I think the problem lies with SQLITE, which knows only limited data types: sqlite.org/datatype3.html
    – christoph
    Mar 19, 2023 at 10:50
  • 2
    You could experiment with the OGR Virtual Format, where you have more options to define your field types: gdal.org/drivers/vector/vrt.html. After loading your XML description (i.e. myfile.vrt) into QGIS you could replace the layer datasource with the content of your VRT file using the changeDataSource plugin.
    – christoph
    Mar 19, 2023 at 11:06
  • The answer clearly involves the lack of field type support for Date, Time, and Boolean. @Babel was the first to point this out, so if you'd like to form your comment as an answer I'd be happy to credit.
    – Stu Smith
    Mar 25, 2023 at 21:29

2 Answers 2

5

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

enter image description here

See also here: https://docs.qgis.org/latest/en/docs/user_manual/managing_data_source/create_layers.html#special-comments

1

I don't know a possibility to define the resulting field-type of a virtual layer, but as workaround You could create the virtual layer with a query that contains only the absolute necessary fields and add all other fields via join from the original layers.

That has multiple advantages:

  1. simple queries for the virtual layer
  2. attribute-tables and dialogs, which show clear, from where the fields come
  3. correct (and all) field-types from original layers

So create virtual_lyr with something like

select 
id_field
from my_layer  
order by field_a

And then join all other data with layer-properties-dialogue or via Python:

join_info = QgsVectorLayerJoinInfo()
join_info.setJoinLayer(my_layer)
join_info.setJoinFieldName(id_field)
join_info.setTargetFieldName(id_field)
join_info.setUsingMemoryCache(True)
virtual_lyr.addJoin(join_info)

One possible disadvantage:

The resulting layers with simple query and join could be less performant than the with complex queries without join, I haven't tested this so far.

hth Ludwig

2
  • 1
    Using a join is an interesting approach, but it's probably just as complicated as Refactoring.
    – Stu Smith
    Mar 25, 2023 at 21:27
  • Refactoring QGis-Source-Code is appreciated, but far beyond my knowledge, I'm afraid...
    – ludwig
    Apr 24, 2023 at 10:14

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.