ArcGIS Pro Tutorial: Learn to Build SQL Queries Using Select by Attributes in ArcGIS Pro | Part 1
Credit: Youtube Channel “Terra Spatial”
You can see all the tutorials from here: Techgeo Academy.
Introduction to SQL Queries in ArcGIS Pro
Building SQL queries in ArcGIS Pro is an essential skill for any GIS professional looking to efficiently analyze and filter spatial data. The Select by Attributes tool allows users to create powerful queries that can isolate specific features based on attribute values, making data management and analysis much more streamlined.
In this tutorial, we’ll explore the fundamentals of constructing SQL queries using the Select by Attributes functionality in ArcGIS Pro. This powerful tool enables you to work with your data more effectively by allowing you to select features that meet specific criteria, whether it’s filtering by numeric ranges, text values, or complex conditional statements.
Getting Started with Select by Attributes
Before diving into query construction, you’ll need to have a feature layer loaded in your ArcGIS Pro project. Once you have your data ready, follow these steps to access the Select by Attributes tool:
- In the Contents pane, right-click on your target feature layer
- Select “Selection” from the context menu
- Choose “Select by Attributes”
- The Select by Attributes dialog will appear, providing you with query building capabilities
The interface presents you with a user-friendly environment where you can construct your queries using either the interactive builder or by writing SQL expressions manually.
Understanding SQL Query Structure
ArcGIS Pro uses a SQL-like syntax for querying features. A basic query follows this structure:
"FieldName" Operator ValueFor example, if you want to select all features where the population is greater than 10,000, you would write:
"Population" > 10000Key components to remember:
- Field names should be enclosed in double quotes
- Text values should be enclosed in single quotes
- Numeric values don’t require quotes
- Use appropriate operators for different data types
Common SQL Operators in ArcGIS Pro
Understanding the operators available in ArcGIS Pro is crucial for building effective queries. Here are the most commonly used operators:
| Operator | Description | Example |
|---|---|---|
| = | Equal to | “Type” = ‘Residential’ |
| <> | Not equal to | “Status” <> ‘Complete’ |
| > | Greater than | “Area” > 1000 |
| < | Less than | “Elevation” < 500 |
| >= | Greater than or equal to | “Year” >= 2020 |
| <= | Less than or equal to | “Score” <= 80 |
| LIKE | Pattern matching | “Name” LIKE ‘A%’ |
| IN | Value in list | “State” IN (‘CA’, ‘NY’, ‘TX’) |
| IS NULL | Null values | “Address” IS NULL |
| BETWEEN | Range of values | “Age” BETWEEN 18 AND 65 |
Working with Different Data Types
Text Fields
When querying text fields, always enclose your values in single quotes. For partial matches, use the LIKE operator with wildcards:
"CityName" = 'Portland'
"Description" LIKE 'Industrial%'
"Category" IN ('TypeA', 'TypeB', 'TypeC')Numeric Fields
Numeric fields don’t require quotes around values:
"Population" > 50000
"Area" BETWEEN 100 AND 1000
"Elevation" >= 2000Date Fields
Date fields have specific formatting requirements and should be enclosed in date-specific delimiters:
"CreationDate" > DATE '2023-01-01'
"LastUpdate" BETWEEN DATE '2023-01-01' AND DATE '2023-12-31'Combining Multiple Conditions
For more complex queries, you can combine multiple conditions using logical operators:
AND Operator
The AND operator requires that all conditions be true:
"Population" > 10000 AND "State" = 'California'OR Operator
The OR operator requires that at least one condition be true:
"Type" = 'Residential' OR "Type" = 'Commercial'NOT Operator
The NOT operator negates a condition:
NOT "Status" = 'Inactive'
NOT "Area" IS NULLParentheses for Complex Queries
Use parentheses to control the order of operations in complex queries:
("Type" = 'Residential' OR "Type" = 'Commercial') AND "Area" > 1000Practical Examples
Selecting Features by Numeric Range
To select all parcels with an area between 500 and 2000 square feet:
"Area" BETWEEN 500 AND 2000Selecting Features by Text Pattern
To select all streets that start with “Oak”:
"StreetName" LIKE 'Oak%'Selecting Features with Multiple Conditions
To select commercial buildings constructed after 2010:
"BuildingType" = 'Commercial' AND "YearBuilt" > 2010Selecting Features with Null Values
To find records missing address information:
"Address" IS NULL OR "City" IS NULLBest Practices and Tips
- Always test your queries on a small dataset first
- Use field aliases when available to make queries more readable
- Be mindful of case sensitivity, especially with text queries
- Use parentheses to make complex queries clearer
- Save frequently used queries for future use
- Consider indexing frequently queried fields for better performance
Common Pitfalls to Avoid
- Forgetting to enclose text values in single quotes
- Mismatched data types (comparing text to numbers)
- Incorrect use of wildcards in LIKE statements
- Missing parentheses in complex logical expressions
- Using operators that don’t work with certain data types
Frequently Asked Questions
What is the difference between Select by Attributes and Select by Location?
Select by Attributes filters features based on their attribute values, while Select by Location filters features based on their spatial relationship to other features. For example, Select by Attributes might select all parcels with a value over $100,000, while Select by Location might select all parcels within a specific flood zone.
Why am I getting a syntax error when building my query?
Syntax errors are commonly caused by: missing quotes around text values, incorrect field names, missing spaces around operators, or mismatched parentheses. Double-check these elements in your query. Also ensure you’re using the correct delimiters for your data source type.
Can I save my SQL queries for future use?
Yes, ArcGIS Pro allows you to save query expressions. In the Select by Attributes dialog, you can click the “Save” button to store your query. You can also access previously saved queries through the “Load” button, making it easy to reuse complex queries.
How do I handle special characters in field names?
Field names with spaces or special characters should be enclosed in double quotes in your SQL expressions. For example: “First Name” = ‘John’ or “Area (sq ft)” > 1000. Avoid using special characters in field names when possible to prevent complications.
What’s the difference between selecting and querying in ArcGIS Pro?
Selecting highlights features temporarily within your current map session, while querying (using tools like Make Feature Layer) creates a new layer containing only the features that meet your criteria. Selections can be exported to create permanent datasets.
How can I improve query performance with large datasets?
Performance can be improved by: indexing frequently queried fields, using appropriate data types, limiting the number of records processed, creating spatial indexes for spatial queries, and filtering data at the source when possible through database connections.
Can I use SQL functions in my queries?
Limited SQL functions are available in ArcGIS Pro queries. Common functions include UPPER(), LOWER(), and SUBSTRING() for text manipulation. However, the available functions depend on your data source. File geodatabases support different functions than enterprise geodatabases.
How do I clear my current selection?
To clear a selection, right-click on the layer in the Contents pane, select “Selection,” and then choose “Clear Selection.” You can also use the “Clear Selected Features” button on the Map tab of the ribbon interface.
