Taming Lengthy Definition Queries

ArcGIS Definition Queries are a wonderful tool for segmenting data into different layers without having to save them into discrete files, and then manage all that extra stuff. Everyone should use them. We often don’t though because working with them is brain bendy and troublesome. Last year I shared a technique to help with the bendy part, How to debug definition queries in ArcMap?. Today’s tip is to help with one troublesome part:

Taming length when all you have is that tiny expression builder window!

The pain

(FUNCTION LIKE ‘mineral lick’) OR (TAXON LIKE ‘Grizzly Bear’ OR TAXON LIKE ‘Mountain Goat’ OR TAXON_GROUP2 LIKE ‘Raptor’ OR TAXON LIKE ‘Thinhorn Sheep’) OR (TAXON LIKE ‘Woodland Caribou’ AND SEASON NOT LIKE ‘winter (Oct-Apr)’ AND (WKA_LEVEL_CODE LIKE ‘2’ OR WKA_LEVEL_CODE IS NULL))

Only people who write these frequently and with this particular data will have a clue what this does without devoting significant time and synapse activity to unpacking what’s going on here. Save your future self the trouble.

The salve

SQL and related query languages ignore line wrap, spaces and indentation. So rearrange in a way that makes the logic apparent:

(FUNCTION LIKE 'mineral lick')
    OR (TAXON LIKE 'Grizzly Bear'
    OR TAXON LIKE 'Mountain Goat'
    OR TAXON_GROUP2 LIKE 'Raptor' 
    OR TAXON LIKE 'Thinhorn Sheep'
    )

OR (TAXON LIKE 'Woodland Caribou'
    AND SEASON NOT LIKE 'winter (Oct-Apr)'
           AND (WKA_LEVEL_CODE LIKE '2' OR WKA_LEVEL_CODE IS NULL)

That’s a big improvement, but we can do better: Use Notepad++ or other decent text editor, save the query with an `.sql` extension so it gives syntax highlighting and bracket matching, and copy>>paste into the Query Builder. It’s a bit more work but makes thinking through the queries a lot easier. An added side benefit is the queries are also saved outside the map, so there’s a backup.

FUNCTION LIKE 'mineral lick') 
  OR (TAXON LIKE 'Grizzly Bear'
  OR TAXON LIKE 'Mountain Goat'
  OR TAXON_GROUP2 LIKE 'Raptor' 
  OR TAXON LIKE 'Thinhorn Sheep'
  ) 
OR (TAXON LIKE 'Woodland Caribou'
  AND SEASON NOT LIKE 'winter (Oct-Apr)'
   AND (WKA_LEVEL_CODE LIKE '2' OR WKA_LEVEL_CODE IS NULL)
  )

The last query could be split like the rest, to have the operator at the beginning of the line. I find with only 2 conditions in the expression it’s simpler to have on a single line, but that’s a just a preference. Adjust to suit your own taste.