ArcGIS attribute filtering: Selecting rows with specific event occurrences

Data and Databases
1

I’m working on a project in ArcGIS and need help with the ‘Extract by Attributes’ tool. My dataset has 27,000 rows and 10 columns. I want to pick out rows where an event (value 1) happens exactly 3 times across 8 specific columns.

Here’s what I’ve tried in the SQL where clause:

SUM(
  CASE WHEN col_2000 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2005 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2010 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2015 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2020 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2025 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2030 = 1 THEN 1 ELSE 0 END +
  CASE WHEN col_2035 = 1 THEN 1 ELSE 0 END
) = 3

But it’s not working. The tool doesn’t accept this SQL statement. What am I doing wrong? Any tips on how to fix this would be great!

2

hey, i had a similar issue before. try using the Field Calculator instead. create a new field and use this expression:

(!col_2000! + !col_2005! + !col_2010! + !col_2015! + !col_2020! + !col_2025! + !col_2030! + !col_2035!) = 3

then use Select by Attributes on this new field. hope this helps!

3

Ooh, interesting problem! have u tried using the ‘Calculate Field’ tool first? You could make a new field that counts the 1s across those columns, then use that for filtering. might be easier than cramming everything into one SQL statement. what do you think about that approach?

4

I’ve encountered this issue before, and I can suggest an alternative approach. Instead of using ‘Extract by Attributes’, try using the ‘Select Layer by Attribute’ tool first. The SQL syntax for this tool is more flexible. Use this expression:

(col_2000 + col_2005 + col_2010 + col_2015 + col_2020 + col_2025 + col_2030 + col_2035) = 3

This will select all rows where the sum of these columns equals 3. After running this selection, you can use the ‘Copy Features’ tool to extract the selected features into a new layer. This method is more efficient and should work with your dataset size.