2

I'm trying to create a virtual layer in QGIS for use in QField using SQL, but cannot work out what the syntax is to refer to a GeoPackage layer.

My GeoPackage is named "survey". My GeoPackage layer is named "treePt".

I cannot get a simple SQL statement to work. I have tried the following:

select fid from "survey - treePt" 
select fid from 'survey - treePt'
select fid from survey - treePt
select fid from "treePt"
select fid from 'treePt'
select fid from 'reePt

All of which throw an error "Referenced table in query not found"

What is the correct syntax?

Improve this question

2 Answers 2

Reset to default
3

When creating a virtual layer you need to

  • Add an embedded layer (if your geopackage layer is not present in your QGIS project)
    Or
  • Import an embedded layer (if your geopackage layer is allready present in your QGIS project)

enter image description here

After that you just need to use normal SQL syntax so SELECT fid FROM treePt should work but if your query is more complex or use several table it will be advisable to use something like SELECT treePt.fid FROM treePt or SELECT a.fid FROM treePt AS a

If you look at the virtual layer properties in the information tab you will see that the source will reference the geopackage as well as the query.

Improve this answer
3
  • Helpful reply, wqhich has helped, but is not the full answer. Even when embedding the layer, the same syntax fails, unless I cut and paste the layer name from local name, or alter the local name to avoid ' - ' in the string. It seems the '-' character is causing the issue. Must be a non standard hyphen which I cannot create from my keyboard, but can cut and paste from the local name of the embedded layer. Does that make sense?
    – Oisin
    Jul 11 at 18:20
  • The - is probably interpreted as a minus operator, you may try to put the name with it between quote
    – J.R
    Jul 11 at 18:49
  • Yes, I've tried using single and double quotes. Single quotes work if I cut and paste layer name. But it I over type the '-' , using hyphen or numpad minus key it fails.
    – Oisin
    Jul 11 at 20:05
2

I have found the answer.

With respect to J.R. the solution is NOT to embed the file. Layers and tables in the Layer panel should be available to the SQL statement in virtual layers.

The cause of my error is the 'em dash' character used in the default GeoPackage layer name. QGIS adds the layer and references is as

'GeoPackage Name' ||' - '||'layer name'

But the character '-' is the em dash unicode character 8212. My keyboard only generates the unicode hyphen-minus character 45. So I can only reproduce the correct layer name by cutting and pasting a unicode em dash character.

Improve this answer

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.