2

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?

2
  • Could you return the list as a string from your Python function and then cast it to an array in SQL?
    – Matt
    Oct 21, 2022 at 8:39
  • @Matt Unfortunately SQLite has no support for arrays. A possible solution could be to return the values as a string and parse them via regular expressions in SQL. But there must be an easier way .
    – christoph
    Oct 21, 2022 at 8:50

1 Answer 1

2

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

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.