2

I am trying to make SQL spatial query for GeoPackage with PyQGIS in my QGIS plugin.

I get error message :

"sqlite3.OperationalError: no such function: st_envelope"

I can make the query without problem manually with QGIS "DB Manager". What I am missing ?

It looks like sqlite3 does not work here. If so - how can I do the query?

Here is my code:

import sqlite3

geoPack = r 'c:\qgis_dat\mapaIshit\mapaIshit.gpkg'

conn = sqlite3.connect(geoPack)

sql = "select st_astext(st_envelope(geom)) from setl_area where fid = 1 "

cur = conn.cursor()

cur.execute(sql)

column_data = cur.fetchall()   

cur.close()
conn.close()
1

2 Answers 2

3

You don't need to use sqlite3 Python but you'd better use QGIS functions as below

from osgeo import ogr

input_file = '/path/to/geopackage.gpkg'

# To list tables, useful to know the table name in the sql query
# Can be commented
gpkg_layers = [l.GetName() for l in ogr.Open(input_file)]
print(gpkg_layers)

# For info, if GPKG, use "ogr", if spatialite, use "spatialite"
md = QgsProviderRegistry.instance().providerMetadata("ogr")
con = md.createConnection( input_file, {})

result = conn.executeSql('SELECT * FROM youtable')

for row in result:
    print(row)
4
0

Instead of limiting yourself to one specific type of database, why not use a layer

# get the path to a geopackage  e.g. /usr/share/qgis/resources/data/world_map.gpkg
path_to_gpkg = os.path.join(QgsApplication.pkgDataPath(), "resources", "data", "world_map.gpkg")
# append the layername part
gpkg_countries_layer = path_to_gpkg + "|layername=countries"
# e.g. gpkg_places_layer = "/usr/share/qgis/resources/data/world_map.gpkg|layername=countries"
vlayer = QgsVectorLayer(gpkg_countries_layer, "Countries layer", "ogr")
if not vlayer.isValid():
    print("Layer failed to load!")
else:
    QgsProject.instance().addMapLayer(vlayer)

will get you a layer from a GeoPackage, then you can use a filter to fetch the features that meet your requirement and then calculate anything you need:

expression = 'Test >= 3'
request = QgsFeatureRequest().setFilterExpression(expression)

matches = 0
for f in layer.getFeatures(request):
   matches += 1

print(matches)

QGis will take care of talking to the underlying datasource (so it will work if you switch to PostGIS or a ShapeFile).

1
  • 1
    Thanks for your answer, but i want to use spatial query on geoPackage because it much more faster(X2-10). I already have my zoom code for layer: requestt = QgsFeatureRequest ().setFilterExpression( expressionCur ) it = layer1.getFeatures(requestt) layer1.selectByIds([f.id() for f in it]) self.iface.mapCanvas().zoomToSelected(layer1)
    – Arie
    Aug 16, 2021 at 11:14

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.