SQL expression works in DB Manager but not in Query Builder/Filter, returns "OGR[3] error 1: Undefined function" error

Question

I have a geopackage table/layer of buildings. I need to "offset" by using ST_Translate. https://postgis.net/docs/ST_Translate.html

The expression SELECT ST_Translate(geom, 1, 1, 0) FROM "building_UTM" works when ran in the SQL Window of the DB Manager.

However, if the same expression is ran via the Query Builder/Filter function, it doesn't work and returns the following error

An error occurred when executing the query.The data provider said: OGR[3] error 1: Undefined function 'ST_Translate' used.

I'm sure that this expression ran as expected in the Query Builder in my other laptop.

Answer 1

The Query Builder is not the same as the DB Manager. DB Manager allows you to query using SQL syntax for various databases, but the Query Builder uses the QGIS expression engine. See https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/expression.html

I would just use the DB manager and create a layer from the query. But ff you want to do this within QGIS, check out the Field Calculator, where you can use the in-built translate function using the same syntax as the PostGIS function you're using in the first example:

 translate($geometry,1,1)

Answer 2

From the screenshot you can see that the SQL builder is used to filter a given layer, so the expression you can write is only a where clause, which is automatically appended to select * from givenLayer where .

You can either load the layer from the DBManager (maybe include an ID field?) or do roughly the same using a virtual layer.

Go the the menu layer / add layer / add-edit virtual layer and enter your query, but make sure to include an ID and eventually other fields from your original layer...

Answer 3

Apparently, the table in the geopackage was faulty, I guess. I had to re-save the shapefile to the geopackage to overwrite it. And when I ran the query in the Query Builder/Filter window, it worked.