4

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:

enter image description here

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.

|improve this question
  • If the data is always a shapefile, have you considered opening the DBF directly and using string search methods (possibly regex) line by line? Quick and dirty, but I expect that this would be much faster! – Andy Harfoot Mar 22 '19 at 15:06
  • Move the data into PosGIS, solve it, and (if needed), move it back to shapefile. – Fjellrev Mar 28 '19 at 15:40
5
+100

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

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)
|improve this answer
  • Indeed, a brilliant answer! – Taras Mar 22 '19 at 13:36
  • @Taras - Thank you but will have to wait and see if it helps the OP :) – Joseph Mar 22 '19 at 13:39
  • 1
    @Joseph. Thank you for your answer. Your solution is working for the exact case: attribute = element of strat_list (e.g. field1 = 'Löß'). As I've asked in the question I would like to check if an attribute is LIKE an element of strat_list. For example: field1 = 'Löß', field1='... Löß ...', field2='Lößlehm', etc. have to be results of the filter. – Stefan Mar 22 '19 at 13:52
  • @Stefan - Thanks for the clarification. I've edited the post which uses the fnmatch module to search for like terms. Although I haven't tested this on values which have accents. – Joseph Mar 22 '19 at 15:31
  • @Joseph. I've tested your edited code. In a test case (110.000 features, 9 string fields, 8 search strings) it took about 230sec and thus noticeable more time then my second version WITH setFlags(QgsFeatureRequest.NoGeometry) (160sec). Thank you for the hint: avoid calling the geometry for each feature. – Stefan Mar 25 '19 at 12:23

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.