Attribute Queries¶

Goal: Understand the basic shape of a SQL query and which part ArcGIS Pro asks you to write.

The full SQL statement¶

SELECT name, population, density
FROM   cities
WHERE  state = 'GA'
  AND  population > 50000
ORDER BY population DESC;

In ArcGIS Pro, the layer is the table. So Pro only asks for the WHERE part:

state = 'GA' AND population > 50000

Pro implicitly does:

SELECT *
FROM   <your_layer>
WHERE  <what_you_typed>

That's why this whole section focuses on the WHERE clause.

Core types of conditions¶

Combining conditions¶

condition1 AND condition2          -- both must be true
condition1 OR condition2           -- either is true
NOT condition                      -- inverse
(condition1 AND condition2) OR c3  -- explicit precedence

Use parentheses generously. Don't trust operator precedence in SQL; be explicit.

A first query, end to end¶

"All Title I elementary schools in Fulton County with enrollment over 500."

TITLE1 = 'Y'
  AND LEVEL = 'Elementary'
  AND COUNTY = 'Fulton'
  AND ENROLLMENT > 500

Walk through:

1. TITLE1 = 'Y' → only Title I schools.
2. LEVEL = 'Elementary' → only elementary level.
3. COUNTY = 'Fulton' → in Fulton.
4. ENROLLMENT > 500 → big enough.

After running this in Select by Attributes, the count appears at the bottom-right of the map. Sanity-check it.

Quoting cheat sheet¶

What ArcGIS Pro does NOT support in WHERE¶

• SELECT columns — Pro returns whole rows; you can't pick columns.
• ORDER BY — sort with the table header instead.
• GROUP BY — use Summary Statistics tool instead.
• JOIN — use Add Join or Spatial Join instead.
• Sub-queries — usually no. Workaround: do a first selection, then a second.

If you need any of these, drop into actual SQL through:

• An enterprise database connection
• The Make Query Layer tool (queries against a database table)
• Python + arcpy.da.SearchCursor with custom logic
• geopandas.GeoDataFrame.query()

Practice¶

→ Next: WHERE Clauses.