Custom Python expression functions should return a QVariant-compatible value, which is specified in qgsfunction.py
(https://github.com/qgis/QGIS/blob/master/python/core/additions/qgsfunction.py).
So I guess it should be possible to return a Python array or dictionary, right?
In fact, this actually works pretty well in the QGIS Expression Builder, but not in a custom SQL query.
QGIS allows Python expression functions in custom SQL queries that follow the SQLite/SpatiaLite syntax. But how can we return multiple values here?
I have a practical example, where I like to build a dynamic point marker grid from the extent of the reference map of a given layout (grid spacing is defined in the project variable grid_space
– i.e. 1000):
Here is the Python expression function:
from qgis.core import qgsfunction,QgsProject,QgsExpressionContextUtils
@qgsfunction(args='auto', group='Custom')
def getLayoutMapGridParam(layoutname, feature, parent):
layout = QgsProject.instance().layoutManager().layoutByName(layoutname)
space = QgsExpressionContextUtils.projectScope(QgsProject.instance()).variable('grid_space')
if not space:
space = 1000
else:
space = int(space)
if layout:
map = layout.referenceMap()
if map:
extent = map.extent()
xmin = int(extent.xMinimum()/space)*space
ymin = int(extent.yMinimum()/space)*space
xcount = round(extent.width()/space,0)+1
ycount = round(extent.height()/space,0)+1
return [xmin,ymin,xcount,ycount]
if not extent:
return None
and the invalid SQL query for the virtual point grid layer:
WITH RECURSIVE
r(x) AS (
SELECT getLayoutMapGridParam('MyLayout')[0]
UNION ALL
SELECT x+var('grid_space') FROM r
LIMIT getLayoutMapGridParam('MyLayout')[2]
),
c(y) AS (
SELECT getLayoutMapGridParam('MyLayout')[1]
UNION ALL
SELECT y+var('grid_space') FROM c
LIMIT getLayoutMapGridParam('MyLayout')[3]
)
SELECT row_number() over() as id,geomfromtext('POINT('||r.x||' '||c.y||')',31255) as geom /*:point:31255*/ FROM r,c
Does anyone have an idea how to return multiple values from a Python expression function for use in a custom SQL query?
Best Answer
The best solution I could find at the moment is to use the new SQLite JSON functions and operators (available since SQLite 3.38.0): https://www.sqlite.org/json1.html
So I changed my Python expression function to return a JSON string:
and modified the SQL to use JSON operators:
The default grid spacing (1000) can be changed through setting the QGIS project variable
grid_space
.BTW: I'm using QGIS 3.26.3 on Windows (SQLite 3.38.1)