Filtering records in GeoPackage file by numeric/text fields using GeoPandas/Fiona

Question

I have a GPKG file of 46M records and 6.5GB filesize, and need to open about 300 records, where fieldX = Y (Y may change). Opening the entire file would cause memory overflow, while processing the entire file in chunks would take a couple of hours.

I walked through GDAL Python API docs, but there's nothing. Filtering seems to be done by calling a method rather than passing a parameter to Open.

If it's possible to open the file in Fiona and then call a method to filter records (.filter()), that would work too.

Of course, I can find workarounds, but would like to have it rather straightforward.

edit: I tried Fiona's .filter() with where argument (Iterator source, .filter source, OGR docs), but it doesn't seem to work:

import geopandas as gpd
import fiona
from shapely.geometry import Point

ds = (
    (Point(0, 0), 'abcde'),
    (Point(1, 1), 'fghij'),
    (Point(2, 2), 'klmno')
)

gdf = gpd.GeoDataFrame(ds, columns=['geometry', 'fieldX'])
gdf.to_file('test.gpkg', driver='GPKG')

with fiona.open('test.gpkg', layername='test') as fp:
    itr1 = fp.filter(where="fieldX='klmno'")
    print(next(itr1))

    itr2 = fp.filter(where="fieldX like '%klmno%'")
    print(next(itr2))

outputs:

{'type': 'Feature', 'id': '1', 'properties': OrderedDict([('fieldX', 'abcde')]), 'geometry': {'type': 'Point', 'coordinates': (0.0, 0.0)}}
{'type': 'Feature', 'id': '1', 'properties': OrderedDict([('fieldX', 'abcde')]), 'geometry': {'type': 'Point', 'coordinates': (0.0, 0.0)}}

In Sqlite3 this works as expected:

sqlite> .open notebooks/test.gpkg
sqlite> .tables
gpkg_contents           gpkg_spatial_ref_sys    rtree_test_geom_node  
gpkg_extensions         gpkg_tile_matrix        rtree_test_geom_parent
gpkg_geometry_columns   gpkg_tile_matrix_set    rtree_test_geom_rowid 
gpkg_ogr_contents       rtree_test_geom         test                  
sqlite> select * from test where fieldX='klmno';
3|GP|klmno

Answer 1

You can pass a SQL statement to geopandas.read_file.

Something like this:

import geopandas as gpd

sql = f"""
    SELECT * FROM "test"
     WHERE fieldX = 'klmno'
"""
gdf = gpd.read_file("test.gpkg", sql=sql)