Tutorial: Query a feature layer (SQL)

Learn how to execute a SQL query to access polygon features in a feature layer.

Python mapping widget showing parcels queried with a SQL expression in Malibu, CA

A feature layer can contain a large number of features. To access a subset of these features, you can execute an SQL or spatial query, either together or individually. The results can contain the attributes, geometry, or both for each record. SQL and spatial queries are useful when a feature layer is very large and you want to access only a subset of its data.

In this tutorial, you perform server-side SQL queries to return a subset of the features from the LA County Parcels feature service. The feature layer contains over 2.4 million features.

Prerequisites

The ArcGIS API for Python tutorials use Jupyter Notebooks to execute Python code. If you are new to this environment, please see the guide to install the API and use notebooks locally.

Steps

Import the arcgis.gis module.

Use dark colors for code blocks

from arcgis.gis import GIS

Create an anonymous connection to ArcGIS Online to access public data. Since this dataset is public you do not need credentials to access it. If it were a private dataset, you would be required to log in
Use dark colors for code blocks
```
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

from arcgis.gis import GIS

portal = GIS()
```

Access the data

Use the ContentManager class to access the dataset by Item ID.

Use dark colors for code blocks

portal = GIS()

parcel_layer_item = portal.content.get("a6fdf2ee0e454393a53ba32b9838b303")

parcel_layer = parcel_layer_item.layers[0]

Run the query

Create a string variable containing the SQL statement. This will query only the features of this feature layer thats UseType attribute is set as Residential. Pass this in the where parmeter to the query() method of the FeatureLayer object. The as_df parameter is set to False so that the results will be returned as FeatureSet. The return_all_records and result_record_cout parameters are used to only return the first 100 records satisifying the query. Set the out_fields as an array of field names to return in the results.
Use dark colors for code blocks
```
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

parcel_layer_item = portal.content.get("a6fdf2ee0e454393a53ba32b9838b303")

parcel_layer = parcel_layer_item.layers[0]

where_clause = "UseType = 'Residential'"

results = parcel_layer.query(
  where = where_clause,
  as_df = False,
  return_all_records = False,
  result_record_count = 100,
  out_fields = "APN, UseType"
)
```

Display the results

Use the map method to create a map widget. Use the add_layer() method to add the results featureset to the map and the zoom_to_layer() method to set the maps extent so the query results are visibile.s

Use dark colors for code blocks

where_clause = "UseType = 'Residential'"

results = parcel_layer.query(
  where = where_clause,
  as_df = False,
  return_all_records = False,
  result_record_count = 100,
  out_fields = "APN, UseType"
)

map = portal.map()
map

map.add_layer(results)
map.zoom_to_layer(results)

Optional: Export map widget state as a static .html file.

Use dark colors for code blocks

map = portal.map()
map

map.add_layer(results)
map.zoom_to_layer(results)

import os
file_path = os.path.join(os.getcwd(), "home", "query-a-feature-layer-sql.html")

map.export_to_html(file_path, title="Query a feature layer (SQL)")

When the map displays, you should see the results records displayed in the center of the map. Click on a parcel to show a pop-up with the features attributes.

What's next?

Query a feature layer (spatial)

Learn how to execute a spatial query to access polygon features from feature services.

Get global data

Query demographic information for locations around the world with the GeoEnrichment service.

Get local data

Query local analysis variables in select countries around the world with the GeoEnrichment service.

Add a layer from a portal item

Learn how to use a portal item to access and display point features from a feature service.