ArcGIS Pro SQL Examples¶

Goal: A reference page of real, paste-ready queries for common GIS scenarios. Targets file geodatabase / shapefile syntax unless noted.

Cities & states¶

-- All cities in Georgia
STATE_NAME = 'Georgia'

-- All cities in GA, FL, or SC
STATE_NAME IN ('Georgia','Florida','South Carolina')

-- Major cities (>100K)
STATE_NAME = 'Georgia' AND POP2020 > 100000

-- Cities starting with "A"
STATE_NAME = 'Georgia' AND CITY_NAME LIKE 'A%'

-- Capitals
CAPITAL = 'Y'

-- Cities NOT in metro Atlanta
STATE_NAME = 'Georgia' AND METRO_AREA <> 'Atlanta'

Counties¶

-- One county
NAME = 'Fulton' AND STATE = 'GA'

-- Counties with > 100K population
POP2020 > 100000

-- Population in a range
POP2020 BETWEEN 50000 AND 250000

-- Negative population growth
POP_CHANGE_PCT < 0

-- Counties with no income data
MEDIAN_INC IS NULL

-- Top 10 counties by population (use sort, not SQL)
-- Sort the table by POP2020 DESC and pick top 10 manually

Census tracts¶

-- Tracts in Fulton County, GA
COUNTY_FIPS = '13121'

-- Low-income tracts
MEDIAN_INC < 40000

-- Below poverty line
PCT_POVERTY > 25

-- High population density
POP_DENSITY > 5000

-- High vacant housing
PCT_VACANT > 15

-- Tracts where pct minority > 50% and median income < 40K
PCT_MINORITY > 50 AND MEDIAN_INC < 40000

Parcels & land use¶

-- Residential parcels
LANDUSE LIKE 'Residential%'

-- Or LANDUSE_CODE list
LANDUSE_CODE IN (110, 120, 130)

-- Commercial OR industrial
LANDUSE IN ('Commercial','Industrial')

-- Vacant
VACANT = 'Y'

-- Built before 1940 (historic)
YEAR_BUILT > 0 AND YEAR_BUILT < 1940

-- High-value residential
LANDUSE = 'Residential' AND TOTAL_VALUE > 750000

-- Parcels missing assessment
ASSESSED_VAL IS NULL OR ASSESSED_VAL = 0

-- Big lots (>1 acre)
ACRES > 1

Schools¶

-- Title I schools
TITLE1 = 'Y'

-- Elementary or middle
LEVEL IN ('Elementary','Middle')

-- Public schools
TYPE = 'Public'

-- Charter schools (text contains)
NAME LIKE '%Charter%'

-- Schools above target enrollment
ENROLLMENT > CAPACITY

-- Schools open after 1990
YEAR_OPEN > 1990

-- Schools without a website
WEBSITE IS NULL OR WEBSITE = ''

Crime / incidents¶

-- Crime in 2024
INCIDENT_DATE >= timestamp '2024-01-01 00:00:00'
  AND INCIDENT_DATE <  timestamp '2025-01-01 00:00:00'

-- Crime in last 30 days (compute cutoff in Python first)

-- Specific incident types
INCIDENT_TYPE IN ('Robbery','Burglary','Theft')

-- Cleared cases
STATUS = 'Cleared'

-- Open / pending
STATUS IN ('Open','Pending')

-- Crime in a specific precinct
PRECINCT = 'B3'

Roads & transportation¶

-- Interstates
ROAD_TYPE = 'Interstate'

-- Highways and major arterials
FUNC_CLASS IN ('Highway','Arterial')

-- Roads named "Peachtree"
NAME LIKE '%Peachtree%'

-- Roads with speed limit > 50
SPEED_LIMIT > 50

-- Roads not maintained by GDOT
MAINTAINER <> 'GDOT'

-- Toll roads
TOLL = 'Y'

Hydrography¶

-- Major rivers
NAME IS NOT NULL AND TYPE = 'River'

-- Streams in study area
TYPE = 'Stream' AND COUNTY_FIPS = '13121'

-- Lakes > 50 acres
TYPE = 'Lake' AND ACRES > 50

-- Reservoirs
NAME LIKE '%Reservoir%' OR TYPE = 'Reservoir'

Environmental / hazards¶

-- 100-year floodplain
FLD_ZONE IN ('AE','A','AH','AO','VE')

-- Outside floodplain
FLD_ZONE = 'X' OR FLD_ZONE IS NULL

-- High wildfire risk
WUI_CLASS IN ('Very High','High')

-- Wetlands
WETLAND_TYPE IS NOT NULL

-- Brownfield sites
STATUS = 'Brownfield' AND CLEANUP_DATE IS NULL

Date-driven queries¶

-- Records added today (computed cutoff)
-- See date-filters.md for relative-date pattern

-- Issued in 2024
ISSUE_DATE >= timestamp '2024-01-01 00:00:00'
  AND ISSUE_DATE <  timestamp '2025-01-01 00:00:00'

-- Expired permits
EXPIRY_DATE < timestamp '2026-04-27 00:00:00'

-- Pending review (no decision yet)
DECISION_DATE IS NULL

Combined / advanced¶

-- Title I elementary schools in Fulton County with > 500 enrollment
TITLE1 = 'Y'
  AND LEVEL = 'Elementary'
  AND COUNTY = 'Fulton'
  AND ENROLLMENT > 500

-- Residential parcels valued > $500K and built after 2010
LANDUSE LIKE 'Res%'
  AND TOTAL_VALUE > 500000
  AND YEAR_BUILT > 2010

-- Crimes between 9pm and 5am, 2024
INCIDENT_DATE >= timestamp '2024-01-01 00:00:00'
  AND INCIDENT_DATE <  timestamp '2025-01-01 00:00:00'
  AND (
        EXTRACT(HOUR FROM INCIDENT_DATE) >= 21
     OR EXTRACT(HOUR FROM INCIDENT_DATE) <  5
  )
-- (EXTRACT works in PostgreSQL/Oracle/SQL Server with appropriate function)

-- Tracts with low income AND high minority share
MEDIAN_INC < 40000 AND PCT_MINORITY > 50

-- Either food desert criterion (USDA simplified)
(MEDIAN_INC < 38000 AND DIST_GROCER_MI > 1)
   OR (PCT_NO_VEHICLE > 30 AND DIST_GROCER_MI > 0.5)

Validation queries¶

Once a workflow is built, run these to sanity-check before publishing:

-- Records with null geometry are unmapped
SHAPE IS NULL                    -- not always supported in WHERE — use Repair Geometry

-- Records with default-flag values
ELEV = -9999

-- Suspect coordinates near (0,0)
CENTROID_X BETWEEN -1 AND 1 AND CENTROID_Y BETWEEN -1 AND 1

-- Duplicate IDs (use Statistics tool, not WHERE)

Tips for re-using these¶

Save your queries. Click ⚙ in the Select by Attributes dialog → Save.
Name them. "FoodDesert_LowIncome.expr" beats remembering the exact SQL.
Use as Definition Queries for layers you'll always filter the same way.
Bake into Python when the same query runs across many layers.

import arcpy
arcpy.management.SelectLayerByAttribute(
    in_layer_or_view="schools",
    selection_type="NEW_SELECTION",
    where_clause="TITLE1 = 'Y' AND LEVEL = 'Elementary'"
)

→ Now jump to a real project: Food Desert Analysis.