Learn how to execute a SQL query to access polygon features in a feature service.
A feature service hosted in a portal can contain a large number of features. To access a subset of the features, you can execute a SQL or spatial query, or both at the same time. Your query can return the attributes, geometry, or both attributes and geometry for each record. SQL and spatial queries are useful when a feature layer is very large and you just want to access a subset of the 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 service contains over 2.4 million features. The resulting features are displayed as a Geo
using CesiumJS.
Prerequisites
An ArcGIS Location Platform or ArcGIS Online account.
Steps
Get the starter app
Select a type of authentication below and follow the steps to create a new application.
Set up authentication
Create developer credentials in your portal for the type of authentication you selected.
Set developer credentials
Use the API key or OAuth developer credentials created in the previous step in your application.
Get a Cesium ion access token
All Cesium applications must use an access token provided through Cesium ion. This token allows you to access assets such as Cesium World Terrain in your application.
-
Go to your Cesium ion dashboard to generate an access token. Copy the key to your clipboard.
-
Create a
cesium
variable and replaceAccess Token YOUR
with the access token you copied from the Cesium ion dashboard._CESIUM _ACCESS _TOKEN Use dark colors for code blocks <script> /* Use for API key authentication */ const accessToken = "YOUR_ACCESS_TOKEN"; // or /* Use for user authentication */ // const session = await arcgisRest.ArcGISIdentityManager.beginOAuth2({ // clientId: "YOUR_CLIENT_ID", // Your client ID from OAuth credentials // redirectUri: "YOUR_REDIRECT_URL", // The redirect URL registered in your OAuth credentials // portal: "YOUR_PORTAL_URL" // Your portal URL // }) // const accessToken = session.token; Cesium.ArcGisMapService.defaultAccessToken = accessToken; const cesiumAccessToken = "YOUR_CESIUM_ACCESS_TOKEN"; </script>
-
Configure
Cesium.
with the Cesium access token to validate the application.Ion.default Access Token Use dark colors for code blocks <script> /* Use for API key authentication */ const accessToken = "YOUR_ACCESS_TOKEN"; // or /* Use for user authentication */ // const session = await arcgisRest.ArcGISIdentityManager.beginOAuth2({ // clientId: "YOUR_CLIENT_ID", // Your client ID from OAuth credentials // redirectUri: "YOUR_REDIRECT_URL", // The redirect URL registered in your OAuth credentials // portal: "YOUR_PORTAL_URL" // Your portal URL // }) // const accessToken = session.token; Cesium.ArcGisMapService.defaultAccessToken = accessToken; const cesiumAccessToken = "YOUR_CESIUM_ACCESS_TOKEN"; Cesium.Ion.defaultAccessToken = cesiumAccessToken; </script>
Add references to ArcGIS REST JS
-
In the
<head
element, reference the> feature-service
andrequest
packages from ArcGIS REST JS.Use dark colors for code blocks <script src="https://cesium.com/downloads/cesiumjs/releases/1.121/Build/Cesium/Cesium.js"></script> <link href="https://cesium.com/downloads/cesiumjs/releases/1.121/Build/Cesium/Widgets/widgets.css" rel="stylesheet"> <script src="https://unpkg.com/@esri/arcgis-rest-request@4/dist/bundled/request.umd.js"></script> <script src="https://unpkg.com/@esri/arcgis-rest-feature-service@4/dist/bundled/feature-service.umd.js"></script>
-
In the
<body
, create an> arcgis
using your access token to authenticate requests to the feature service.Rest. Api Key Manager Use dark colors for code blocks /* Use for API key authentication */ const accessToken = "YOUR_ACCESS_TOKEN"; // or /* Use for user authentication */ // const session = await arcgisRest.ArcGISIdentityManager.beginOAuth2({ // clientId: "YOUR_CLIENT_ID", // Your client ID from OAuth credentials // redirectUri: "YOUR_REDIRECT_URL", // The redirect URL registered in your OAuth credentials // portal: "YOUR_PORTAL_URL" // Your portal URL // }) // const accessToken = session.token; Cesium.ArcGisMapService.defaultAccessToken = accessToken; const authentication = arcgisRest.ApiKeyManager.fromKey(accessToken);
Create a query selector
ArcGIS feature layers support a standard SQL query where clause. Use a drop-down <select
element to provide a list of SQL queries for the LA County Parcels feature layer.
-
Create a
<select
HTML element that allows the user to select different SQL queries. Construct each SQL query based on the values of attributes in the LA County Parcels layer, such as> Use
andType Tax
.Rate Area Use dark colors for code blocks <body> <div id="cesiumContainer"></div> <select id="query-select"> <option value="">Choose a WHERE clause...</option> <option value="UseType = 'Residential'">UseType = 'Residential'</option> <option value="UseType = 'Government'">UseType = 'Government'</option> <option value="UseType = 'Irrigated Farm'">UseType = 'Irrigated Farm'</option> <option value="TaxRateArea = 10853">TaxRateArea = 10853</option> <option value="TaxRateArea = 10860" selected>TaxRateArea = 10860</option> <option value="TaxRateArea = 08637">TaxRateArea = 08637</option> <option value="Roll_LandValue > 1000000">Roll_LandValue > 1000000</option> <option value="Roll_LandValue < 1000000">Roll_LandValue < 1000000</option> </select> <script>
-
Style the
<select
element so that it displays properly in your application.> Use dark colors for code blocks <style> html, body, #cesiumContainer { width: 100%; height: 100%; padding: 0px; margin: 0px; } #query-select { position: absolute; top: 8px; left: 8px; padding: 4px 8px; font-size: 16px; border-radius: 0; } </style>
Query the parcel layer
When the user selects a SQL query, use the ArcGIS REST JS query
operation to query the LA County Parcels feature layer.
-
Copy the URL of the LA County Parcels feature layer.
Use dark colors for code blocks const layerName = "LA_County_Parcels"; const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/" + layerName + "/FeatureServer/0";
-
Add the data attribution for the feature layer source.
- Go to the LA County Parcels item.
- Scroll down to the Credits (Attribution) section and copy its value.
- Create an
attribution
property and paste the attribution value from the item.Use dark colors for code blocks const layerName = "LA_County_Parcels"; const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/" + layerName + "/FeatureServer/0"; // Attribution text retrieved from https://arcgis.com/home/item.html?id=a6fdf2ee0e454393a53ba32b9838b303 viewer.creditDisplay.addStaticCredit(new Cesium.Credit("County of Los Angeles Office of the Assessor", false));
-
Define a new
perform
function that takes a SQL query string as input.Query Use dark colors for code blocks const layerName = "LA_County_Parcels"; const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/" + layerName + "/FeatureServer/0"; // Attribution text retrieved from https://arcgis.com/home/item.html?id=a6fdf2ee0e454393a53ba32b9838b303 viewer.creditDisplay.addStaticCredit(new Cesium.Credit("County of Los Angeles Office of the Assessor", false)); function performQuery(whereClause) { }
-
Query the feature layer using the
query
operation. Pass theFeatures where
paramater as well as yourClause authentication
object. Format results asgeojson
.Use dark colors for code blocks const layerName = "LA_County_Parcels"; const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/" + layerName + "/FeatureServer/0"; // Attribution text retrieved from https://arcgis.com/home/item.html?id=a6fdf2ee0e454393a53ba32b9838b303 viewer.creditDisplay.addStaticCredit(new Cesium.Credit("County of Los Angeles Office of the Assessor", false)); function performQuery(whereClause) { arcgisRest.queryFeatures({ url: layerURL, authentication, f: "geojson", returnGeometry: true, where: whereClause, }) }
-
Add an event listener to your
<select
HTML element. When the selected value changes, call the> perform
function.Query Use dark colors for code blocks const layerName = "LA_County_Parcels"; const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/" + layerName + "/FeatureServer/0"; // Attribution text retrieved from https://arcgis.com/home/item.html?id=a6fdf2ee0e454393a53ba32b9838b303 viewer.creditDisplay.addStaticCredit(new Cesium.Credit("County of Los Angeles Office of the Assessor", false)); function performQuery(whereClause) { arcgisRest.queryFeatures({ url: layerURL, authentication, f: "geojson", returnGeometry: true, where: whereClause, }) } const select = document.getElementById("query-select"); select.addEventListener("change", () => { if (select.value !== "") { const whereClause = select.value; performQuery(whereClause); } });
Set the query extent
The set of features returned by ArcGIS REST JS may not be visible from the current camera position. Add the camera position to the REST request as a geometry
object to only return features that intersect the current viewable extent.
The maximum number of features returned by a query for hosted feature layers is 2000. To return more, you need to check if the request exceeded the maximum feature amount with exceeded
, then use the result
parameter to make multiple requests with the appropriate offset values. To learn more, visit the REST API documentation.
-
Calculate the current viewable extent using the
compute
function. Convert the result from radians to latitude and longitude in degrees.View Rectangle() Use dark colors for code blocks function performQuery(whereClause) { const rect = viewer.camera.computeViewRectangle(); const boundsRadian = [rect.east, rect.south, rect.west, rect.north]; const boundsDegree = []; for (coordinate of boundsRadian) { boundsDegree.push(Cesium.Math.toDegrees(coordinate)); } arcgisRest.queryFeatures({ url: layerURL, authentication, f: "geojson", returnGeometry: true, where: whereClause, }) }
-
Set the
geometry
parameter of your query to the extent, and specify the geometry type asesri
. Perform a spatial intersection to only return features that overlap with the extent.Geometry Envelope Use dark colors for code blocks arcgisRest.queryFeatures({ url: layerURL, authentication, f: "geojson", returnGeometry: true, where: whereClause, geometry: boundsDegree, geometryType: "esriGeometryEnvelope", spatialRel: "esriSpatialRelIntersects", inSR: 4326, })
Display results
After you perform a SQL query, add the resulting features to the scene as GeoJSON.
-
Add the service response service to the scene as a
Geo
.Json Data Source Use dark colors for code blocks arcgisRest.queryFeatures({ url: layerURL, authentication, f: "geojson", returnGeometry: true, where: whereClause, geometry: boundsDegree, geometryType: "esriGeometryEnvelope", spatialRel: "esriSpatialRelIntersects", inSR: 4326, }) .then((response) => { console.log(response) Cesium.GeoJsonDataSource.load(response, { outline: true, }).then((data) => { viewer.dataSources.add(data); }) })
-
In the event listener, remove all previous query results by calling
data
.Sources.remove All Use dark colors for code blocks const select = document.getElementById("query-select"); select.addEventListener("change", () => { viewer.dataSources.removeAll(); if (select.value !== "") { const whereClause = select.value; performQuery(whereClause); } });
Run the app
Run the app.
When the scene loads, you should be able to use the query selector to display parcels. Click on a parcel to show a pop-up with the feature's attributes.
What's next?
Learn how to use additional ArcGIS location services in these tutorials: