33

I need to style a survey parcel polygon shapefile, based on whether the polygon is a mineral claim or not. Unfortunately, the only information on whether a polygon is a mineral claim or not is contained in the "TITLE" field of the attribute table, which gives the full legal name of the surveyed parcel. For instance, 'DISTRICT LOT 5639, BEING AWARD NO. 2 MINERAL CLAIM, KDYD'. I need an expression that selects any feature containing the text 'MINERAL CLAIM' in the "TITLE" field.

Chris Paul Geo
  • 703
  • 3
  • 7
  • 13

2 Answers2

57

You just gotta use the LIKE operator.

For example, "TITLE" LIKE '%MINERAL CLAIM%'

The % symbol acts like a wildcard.

LIKE is case-sensitive, whereas ILIKE is not.

SaultDon
  • 10,389
  • 1
  • 43
  • 78
  • 1
    And be aware that this is a slow operation, you might want to use it once to generate a new column instead of having it as expression all the time. – bugmenot123 Jul 08 '15 at 09:00
  • It is slow for a big shape, so I simple copy/pasted the selection as a new vector layer. – Chris Paul Geo Jul 08 '15 at 16:17
  • @chris You can use that same query in other parts of QGIS like as a definition query or styled using rule based rendering - really depends on the reason why you need to apply the query (ie, analysis, visualization, exporting, etc). Selections are a bit intensive but if applied as a definition query then it only displays those features in the query on the canvas or makes them available for processing. Essentially what you did when copy/pasting the selection as a new vector layer. – SaultDon Jul 09 '15 at 02:44
  • Indexes cannot be used with LIKE so I always try to avoid doing them again and again. But yes, it might be irrelevant, definitely with small datasets there are other low-hanging fruit for speed. – bugmenot123 Jul 09 '15 at 12:22
  • 1
    @bugmenot123 I just learned, that if you have an index when your data is in postgresql, LIKE will use it under specific conditions (like where the % is in the query) and not do a sequential scan! http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html – SaultDon Dec 05 '16 at 19:47
4

I had this exact problem and solved it from the python console with regex. While regex can be tricky it's very powerful. And you'll be left with a tool you can use with more difficult match cases. Here are the docs. and here is a nice online machine for testing your regex strings.

Firstly here is the quick script I run to check my regex strings in qgis

import re
RES_STRING='MINERAL CLAIM'
REGEX_HAYSTACK='DISTRICT LOT 5639, BEING AWARD NO. 2 MINERAL CLAIM, KDYD'

REGEX_STRING=re.compile(RES_STRING)
print "searching for "+RES_STRING+" in "+REGEX_HAYSTACK
REGEX_MATCH = REGEX_STRING.search(REGEX_HAYSTACK)
if REGEX_MATCH:
    print "found '"+REGEX_MATCH.group()+"'"
else:
    print "No match found"

Once you're happy with your regex matching you could wrap it up in a function to provide a selection for all the features that match. Below is a function to do just that.

def select_by_regex(input_layer,attribute_name,regex_string):
    import re
    RES_STRING=regex_string
    attribute_name_idx = input_layer.fieldNameIndex(attribute_name)
    if attribute_name_idx<0:
        raise valueError("cannot find attribute"+attribute_name)
    else:
        fids=[]
        for feature in input_layer.getFeatures():
            REGEX_HAYSTACK=feature[attribute_name_idx]
            REGEX_STRING=re.compile(RES_STRING)
            REGEX_MATCH = REGEX_STRING.search(REGEX_HAYSTACK)
            if REGEX_MATCH:
                fids.append(feature.id())
            else:
                pass
        input_layer.setSelectedFeatures(fids)


#USAGE BIT
input_layer = QgsVectorLayer('path/to/shape/file.shp','layer name', 'ogr')
QgsMapLayerRegistry.instance().addMapLayer(input_layer)   
regex_string='MINERAL CLAIM'
attribute_name='TITLE'
select_by_regex(input_layer,attribute_name,regex_string)

You will need to save this into a file and run it from the qgis python ide.

(untested but pretty confident)

Mr Purple
  • 1,451
  • 12
  • 25
  • 2
    Great advice to learn regex, but overkill for the problem at hand. – alphabetasoup Jul 08 '15 at 02:25
  • 1
    @alpha-beta-soup true. In this case. However, VERY similar problems certainly would find it indispensible. lot numbers <6000? or first 2 mineral claims? It's just another (albeit much more complex/powerful) answer. Perhaps it will help someone else. – Mr Purple Jul 08 '15 at 02:31
  • 3
    Also note that QGIS has a built in regular expression match function - regexp_match. – ndawson Jul 08 '15 at 04:07
  • Certainly the more "in depth" answer. A little overkill for what I need, but appreciate it nonetheless. It will for sure help others in the future. – Chris Paul Geo Jul 08 '15 at 06:13