2

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.

enter image description here

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.

enter image description here

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

6
  • The Query Builder is not the same as the DB Manager. DB Manager allows you to query a PostgreSQL database, but the Query Builder uses the QGIS query engine. See docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/…
    – Encomium
    Nov 23 at 13:36
  • Understood. I'll confirm in the QGIS in my other laptop if the SQL expression works in the QB.
    – BallpenMan
    Nov 23 at 13:39
  • 1
    I would just use the DB manager and create a layer from the query. But the QGIS Expression builder does have a translate function, as you can see if you search for translate in that document.
    – Encomium
    Nov 23 at 13:42
  • Thanks for the tip. I still have to figure out how translate() works. I'm supposed to use the SQL expression in a PyQGIS script by putting it in the QgsVectorLayer.setSubsetString(). The other option is the QgsVectorLayer.translateFeature() method but it requires the layer to be in edit mode first.
    – BallpenMan
    Nov 23 at 14:03
  • 1
    This is for a PyQGIS script so I'd like it to be 100% working out of the box with no needed external libraries.
    – BallpenMan
    Nov 23 at 14:09
3

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)

enter image description here

3

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...

1
  • Yes. I'm supposed to use the SQL expression in a PyQGIS script by putting it in the QgsVectorLayer.setSubsetString(). The other option is the QgsVectorLayer.translateFeature() method but it requires the layer to be in edit mode first. I'll include your answer as one of my options. Thanks!
    – BallpenMan
    Nov 23 at 14:08
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.