PyQGIS – Returning Multiple Values in Custom SQL Query with Python Expression Function

pyqgisqgis-expressionsql

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:

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')
    print(space)
    if not space:
        space = 1000
        QgsExpressionContextUtils.setProjectVariable(QgsProject.instance(), 'grid_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":%s,"ymin":%s,"xcount":%s,"ycount":%s,"space":%s}' % (xmin,ymin,xcount,ycount,space)
    if not extent:
        return None

and modified the SQL to use JSON operators:

WITH RECURSIVE   
    r(x) AS (
        SELECT getLayoutMapGridParam('MyLayout') -> 'xmin'
        UNION ALL      
        SELECT x+getLayoutMapGridParam('MyLayout')->'space' FROM r
        LIMIT getLayoutMapGridParam('MyLayout') -> 'xcount'
    ),
    c(y) AS (
        SELECT getLayoutMapGridParam('MyLayout') -> 'ymin'
        UNION ALL
        SELECT y+getLayoutMapGridParam('MyLayout')->'space' FROM c
        LIMIT getLayoutMapGridParam('MyLayout') -> 'ycount'
    ) 
SELECT row_number() over() as id,geomfromtext('POINT('||r.x||' '||c.y||')',31255) as geom /*:point:31255*/ FROM r,c

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)

*** UPDATE ***

with a small change in our SQL code we can reduce the number of calls to the PYTHON function getLayoutMapGridParam

WITH RECURSIVE 
   data(xmin,ymin,xcount,ycount,space) AS MATERIALIZED    
       (SELECT
          json->'xmin' AS xmin,
          json->'ymin' AS ymin,
          json->'xcount' AS xcount,
          json->'ycount' AS ycount,
          json->'space' AS space
      FROM (SELECT getLayoutMapGridParam('MyLayout') AS json)),
  r(x) AS (
      SELECT xmin FROM data
      UNION ALL
      SELECT x+space FROM r,data
      LIMIT (SELECT xcount FROM data)
  ),  
  c(y) AS (
      SELECT ymin FROM data
      UNION ALL
      SELECT y+space FROM c,data
      LIMIT (SELECT ycount FROM data)
  )
SELECT row_number() over() as id,geomfromtext('POINT('||r.x||' '||c.y||')',31255) as geom /*:point:31255*/ FROM r,c
Related Question