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:
It is not very fast.
If the input string for
geo_lyr.setSubsetString()
reaches a certain size there is an errorOGR[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.