Filter vector layer by searching through complete attribute table - limitations and improvements

Question

In a case were I have a shapefile with a lot of attributes (predominantly text), also I don't have any information about the structure and I don't have any idea in which attribute I have to search/filter to extract/find information, I have the possibility to search the whole attribute table.

I want to keep it simple: I get a shapefile, I don't know anything about it and I want to search the attribute table, filter the layer without creating extra files, to convert it or to import the data into a database. I'm experienced in dealing with spatial databases, etc..

I know about creating virtual layer with queries in QGIS, but this creates a new layer and could be slow and susceptible.

I decided to use Python/PyQGIS trying to get what I want.

1st version:

geo_lyr = QgsMapLayerRegistry.instance().mapLayersByName('geo_polygons')[0]

strat_list = ['Löß', 'löß', 'Löss', 'löss', 'Loeß', 'loeß', 'Loess', 'loess']

geo_list = []
for field in geo_lyr.fields():
    if field.type() == 10:
        for name in strat_list:
            layer_subset = geo_lyr.getFeatures( QgsFeatureRequest().setFilterExpression ( u'%s LIKE \'%s%s%s\'' % (field.name(),'%', name, '%')))
            for i in layer_subset:
                if i["OBJECTID"] not in geo_list:
                    geo_list.append(i["OBJECTID"])
                else:
                    pass

id_input_str = ','.join(str(e) for e in geo_list)
geo_lyr.setSubsetString(u'"OBJECTID" IN %s%s%s' % ('(',id_input_str,')'))

For example: I have a geology layer with polygons and attributes. I want to search for a bunch of words within the attribute table. I don't have any idea about the data so I search for (Löß, löß, loess, ...). For every text attribute (field.type() == 10 representing 9 fields) I search for the strings which are elements of the list strat_list. The hits (OBJECTIDS) are appended to a list.

With this list I can now apply a filter with geo_lyr.setSubsetString().

Problems:

1. It is not very fast.

2. If the input string for geo_lyr.setSubsetString() reaches a certain size there is an error OGR[3] error 1:SQL Expression Parsing Error: memory exhausted. .... There seems to be a limit between strings with 29543 and 29549 characters. So we have a limit here using an ID input for filtering. SOLVED.

2nd version

geo_lyr = QgsMapLayerRegistry.instance().mapLayersByName('geo_polygons')[0]

strat_list = ['Löß', 'löß', 'Löss', 'löss', 'Loeß', 'loeß', 'Loess', 'loess']

strat_filter_list = []
for field in geo_lyr.fields():
    if field.type() == 10:
        strat_str = u' OR '.join('"'+ field.name()+ '" LIKE \'%'+ i + '%\'' for i in strat_list)
        strat_filter_list.append(strat_str)

filter_input_str = ' OR '.join(strat_filter_list)
geo_lyr.setSubsetString(filter_input_str)

The first part of the script creates a list with input strings for every attribute like: "attribute1" like '%Löß%' OR "attribute1" LIKE '%löß%' OR .... Then the list elements are joined and the string can be used in geo_lyr.setSubsetString().

The length of the input string of the 2nd version is much smaller then the input string in the first version. But it could reach the limit until memory is exhausted, when there are quite more attributes and/or different strings to search.

UPDATE

I searched one more time for the OGR Error. I found this : http://www.samuelbosch.com/2016/08/workaround-for-sql-expression-parsing.html.

To avoid the error you have to insert line breaks into the filter_input_str.

For the first version I've create chunks of IDs separated by line breaks.

n = 100
id_chunk = [geo_list[i * n:(i + 1) * n] for i in range((len(geo_list) + n - 1) // n )]

id_input_str = []
for chunk in id_chunk:
    id_chunk_str = '"OBJECTID" IN (' + ','.join(str(e) for e in chunk) + ')' + '\r\n'
    # '\r\n' is the line break
    id_input_str.append(id_chunk_str)

filter_input_str = ' OR '.join(id_input_str)    

geo_lyr.setSubsetString(filter_input_str)

Instead of one (possibly to long) row, I have multiple lines with chunks as the input for my filter. The query builder looks like this:

For the second version I can add the line break after every field input.

filter_input_str  = '\r\nOR '.join(strat_str_list)

My question remains:

Is there another (maybe faster) way to filter the layer by searching through the whole layer? Maybe there is no alternative using temporary layer like a CSV-Layer etc.

Answer 1

If you want to iterate through large datasets, there's a couple of methods you could use:

• QgsFeatureRequest.setFlags(QgsFeatureRequest.NoGeometry) to avoid calling the geometry for each feature;
• QgsFeatureRequest.setSubsetOfAttributes() to only call values from a defined field list.

So essentially you would iterate only the fields of interest and their associated values without having to call their geometries.

Modifying the first part, you would:

1. Define your list of fields you are interested in (i.e. all string fields and OBJECTID);
2. Set the flags for QgsFeatureRequest to filter only these fields;
3. Set an additional flag to ignore geometries;
4. Iterate your filtered attribute table for the keywords in your strat_list and obtain the features' OBJECTID value.

---

import fnmatch
geo_lyr = QgsMapLayerRegistry.instance().mapLayersByName('geo_polygons')[0]

field_list = [field.name() for field in geo_lyr.fields() if field.typeName() == 'String']
for field in geo_lyr.fields():
    if field.name() == 'OBJECTID':
        field_list.append(field.name())

request = QgsFeatureRequest().setFlags(QgsFeatureRequest.NoGeometry).setSubsetOfAttributes(field_list, geo_lyr.fields())

strat_list = ['Löß', 'löß', 'Löss', 'löss', 'Loeß', 'loeß', 'Loess', 'loess']
geo_list = []
for i, f in enumerate(geo_lyr.getFeatures(request)):
    for field in field_list:
        if field != 'OBJECTID':
            for string in strat_list:
                keyword = '*' + string + '*'
                if fnmatch.filter([str(f[field])], keyword):
                    if f['OBJECTID'] not in geo_list:
                        geo_list.append(f['OBJECTID'])

---

Then you can add your updated second part of the script to create the query:

n = 100
id_chunk = [geo_list[i * n:(i + 1) * n] for i in range((len(geo_list) + n - 1) // n )]

id_input_str = []
for chunk in id_chunk:
    id_chunk_str = '"OBJECTID" IN (' + ','.join(str(e) for e in chunk) + ')' + '\r\n'
    id_input_str.append(id_chunk_str)

filter_input_str  = '\r\nOR '.join(id_input_str)
geo_lyr.setSubsetString(filter_input_str)