0

Whenever I run a query on project layers that should return a geometry column such as

select 1 as id, st_union(geometry) as geometry from layer

the column containing the geometry simply doesn't show up. This is annoying since I can't load the results as virtual layers anymore. Using spatial functions that don't return a geometry object such as st_area do work though. This only affects Virtual Layers in the DB-Manager no matter the data source (json,gpkg..). QGIS Versions used: 3.20.2, uninstalled then 3.20.3 on Win 10.

Could it be some setting I am overlooking or is there a easy solution?

Update 1: The problem appears also in the LTR-version 3.16.11 using the installer that uses 'OSGeo4W v2' but it does not appear in the same LTR-version that uses the old OSGeo4W.

Update 2: The problem still persists in any qgis version that depends on OSGeo4W v2 Tried on different windows 10 machines (1609, 1809 and 21H1) Please push this issue on the QGIS bug tracker.

Update 3: Issue still persists in 3.22.3. A feasible workaround is posted here. Update 4 Issue still persists in 3.24 RC

Improve this question
5
  • Are you sure the geometry field is named 'geometry' and not 'geom' ? That's what the default gpkg geometri fields are named in my system.
    – Encomium
    Oct 10, 2021 at 23:20
  • @Encomium yes 'geometry' is the default name for virtual layers.
    – maxwhere
    Oct 10, 2021 at 23:30
  • Ah, I understand. I guess one fix would be to include the virtual layer SQL definition as a CTE in the DB Manager query rather than looping the virtual layer in by name. But maybe that's not an option for your use case.
    – Encomium
    Oct 10, 2021 at 23:45
  • I've never had this issue and I've been using virtual layers regularly since 2.18. Is the geometry in your original layer valid? Which other functions have you tried apart from st_union? Do you have the same problem with points, lines and polygons (and/or multigeometry versions of each)? Do you recall which version it last worked on?
    – she_weeds
    Oct 11, 2021 at 5:50
  • @she_weeds no matter the geometry type nor if it's valid. tried on a different win10 machine with 3.20.1. same thing.
    – maxwhere
    Oct 13, 2021 at 23:34

2 Answers 2

Reset to default
1

The missing geometry column after the switch to OSGeo4W v2 being a bug was confirmed on March 18th and fixed thanks to jef-n. Starting with QGIS 3.30.1 virtual layer geometry column in DB-Manager works again.

Improve this answer
0

Not able to reproduce. Work fine on my QGIS versions (3.16 and 3.18). You may adopt vrt file by putting the following content in a demo.vrt and open it as a vector layer (quite long to show)

<OGRVRTDataSource>
    <OGRVRTLayer name="bbox_from_csv_coords">
        <SrcDataSource relativeToVRT="1">/vsicurl/https://github.com/nvkelso/natural-earth-vector/raw/master/geojson/ne_10m_admin_0_countries.geojson</SrcDataSource>
        <SrcSQL dialect="SQlite">SELECT 1 as id, ST_Union(geometry) as geometry FROM ne_10m_admin_0_countries</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

Clearly, it does not solve your issue but at least, it may help to achieve your goal.

Improve this answer
1
  • What OS are you using QGIS 3.16 and 3.18 on? If it is MS Windows, what installer did you use to install QGIS, the v1/exe or the v2/msi installer?
    – Andrea Giudiceandrea
    Mar 23, 2022 at 11:16

Your Answer

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

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