Tutorial: Query features (SQL)

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 GeoJsonDataSource using CesiumJS.

Prerequisites

You need an ArcGIS Location Platform or ArcGIS Online account.

Steps

Create a new pen

To get started, either complete the Display a scene tutorial or .

Get an access token

You need an access token with the correct privileges to access the resources used in this tutorial.

Go to the Create an API key tutorial and create an API key with the following privilege(s):
- Privileges
  - Location services > Basemaps
  - Location services > Places
Copy the API key access token to your clipboard when prompted.

In CodePen, update the accessToken variable to use your access token.

Use dark colors for code blocks
      const accessToken = "YOUR_ACCESS_TOKEN";

      Cesium.ArcGisMapService.defaultAccessToken = accessToken;

      const cesiumAccessToken = "YOUR_CESIUM_ACCESS_TOKEN";

      Cesium.Ion.defaultAccessToken = cesiumAccessToken;

      const arcGisImagery = Cesium.ArcGisMapServerImageryProvider.fromBasemapType(Cesium.ArcGisBaseMapType.SATELLITE);

      const viewer = new Cesium.Viewer("cesiumContainer", {

        baseLayer: Cesium.ImageryLayer.fromProviderAsync(arcGisImagery),

      });

To learn about the other types of authentication available, go to Types of authentication.

Add references to ArcGIS REST JS

In the <head> element, reference the feature-service and request 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 arcgisRest.ApiKeyManager using your access token to authenticate requests to the feature service.

Use dark colors for code blocks
    const accessToken = "YOUR_ACCESS_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 UseType and TaxRateArea.

Expand
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 &gt; 1000000</option>
    <option value="Roll_LandValue < 1000000">Roll_LandValue &lt; 1000000</option>
  </select>

  <script>
Expand

Style the <select> element so that it displays properly in your application.

Expand
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>
Expand

Query the parcel layer

When the user selects a SQL query, use the ArcGIS REST JS queryFeatures operation to query the LA County Parcels feature layer.

Copy the URL of the LA County Parcels feature layer. Define a new performQuery function that takes a SQL query string as input.

Expand
Use dark colors for code blocks
    const layerName = "LA_County_Parcels";
    const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/"+layerName+"/FeatureServer/0";

    function performQuery(whereClause) {

    }
Expand

Query the feature layer using the queryFeatures operation. Pass the whereClause paramater as well as your authentication object. Format results as geojson.

Expand
Use dark colors for code blocks
    const layerName = "LA_County_Parcels";
    const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/"+layerName+"/FeatureServer/0";

    function performQuery(whereClause) {

      arcgisRest.queryFeatures({
        url: layerURL,
        authentication,
        f:"geojson",
        returnGeometry:true,

        where: whereClause,

      })

    }
Expand

Add an event listener to your <select> HTML element. When the selected value changes, call the performQuery function.

Expand
Use dark colors for code blocks
    const layerName = "LA_County_Parcels";
    const layerURL = "https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/"+layerName+"/FeatureServer/0";

    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);
        }

    });
Expand

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 exceededTransferLimit, then use the resultOffset 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 computeViewRectangle() function. Convert the result from radians to latitude and longitude in degrees.

Expand
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,

      })

    }
Expand

Set the geometry parameter of your query to the extent, and specify the geometry type as esriGeometryEnvelope. Perform a spatial intersection to only return features that overlap with the extent.

Expand
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,

      })
Expand

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 GeoJsonDataSource.

Expand
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);
          })

      })
Expand

In the event listener, remove all previous query results by calling dataSources.removeAll.

Expand
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);
        }

    });
Expand

Run the app

In CodePen, run your code to display the map.

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:

Query features (spatial)

Execute a spatial query to access polygon features from a feature service.

Style features

Use data-driven styling to apply symbol colors and styles to features.

Get global data

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