Skip to content

SQL for GIS

Goal: Become fluent in the SQL WHERE clause — the language of attribute filtering in ArcGIS Pro and every spatial database.

What's covered

  • Why SQL matters for GIS analysts
  • The WHERE clause vocabulary
  • ArcGIS Pro–specific quirks
  • 10+ ready-to-paste examples

Why SQL for GIS?

Every "Select by Attributes" and Definition Query in ArcGIS Pro is just a SQL WHERE clause. Master it and you'll filter, count, and prepare data 10× faster than someone clicking through dialogs.

It's also the gateway to:

  • Spatial databases like PostGIS, Oracle Spatial, SQL Server Spatial.
  • pandas / geopandas — both speak SQL through .query() and SQL-like APIs.
  • Big data tools — BigQuery, Athena, Snowflake — all SQL.

The 10 SQL chapters

The 30-second cheat sheet

-- Basic
STATE = 'GA'

-- Combine
STATE = 'GA' AND POPULATION > 50000

-- List
STATE IN ('GA', 'FL', 'SC')

-- Range
POP BETWEEN 10000 AND 100000

-- Pattern
NAME LIKE 'A%'        -- starts with A
NAME LIKE '%ville%'   -- contains "ville"

-- Null
NOTES IS NULL
NOTES IS NOT NULL

-- Negation
NOT (LANDUSE = 'Industrial')

-- Numeric
DENSITY >= 1000

-- Date (file gdb)
DATE_FIELD >= timestamp '2024-01-01 00:00:00'

ArcGIS Pro vs database SQL

ArcGIS Pro accepts a SQL expression (the WHERE part), and the syntax depends on the data source:

Data source Quotes for fields Date literal
File geodatabase "FieldName" timestamp '2024-01-01 00:00:00'
Shapefile / dBASE "FieldName" date '2024-01-01'
Enterprise (SQL Server) [FieldName] '2024-01-01' (with right collation)
Enterprise (PostgreSQL) "FieldName" '2024-01-01'::date
Enterprise (Oracle) "FieldName" TO_DATE('2024-01-01','YYYY-MM-DD')

Strings always use single quotes ('GA').

→ Esri's reference: SQL reference for elements used in query expressions

Where SQL appears in ArcGIS Pro

Tool What it accepts
Select by Attributes A WHERE clause
Definition Query A WHERE clause (always-on filter)
Make Feature Layer (subset) A WHERE clause
Calculate Field (where applicable) Conditional logic
arcpy + SQL arcpy.management.SelectLayerByAttribute(layer, "NEW_SELECTION", where_clause)

You'll write the same syntax in all of them.

Beyond filtering: spatial SQL

Once you're comfortable with attribute SQL, the next level is spatial SQL in PostGIS:

-- All schools within 500 m of any fire station
SELECT s.name
FROM   schools s
JOIN   fire_stations f
  ON   ST_DWithin(s.geom, f.geom, 500);

-- Count crimes per neighborhood
SELECT n.name, COUNT(c.id) AS crime_count
FROM   neighborhoods n
LEFT JOIN crimes c
  ON   ST_Contains(n.geom, c.geom)
GROUP BY n.name;

PostGIS is the open-source standard. Strongly recommended once you're past the fundamentals.

Practice plan

  1. Read WHERE Clauses — 10 minutes.
  2. Open ArcGIS Pro and run Select by Attributes 5 times with each operator.
  3. Then go through LIKE, AND/OR, IN, IS NULL, BETWEEN, Dates.
  4. Finally, study the ArcGIS Pro SQL Examples — these are real workflow queries.