Query a feature layer (SQL)

Learn how to execute a SQL query to return features from a feature layer based on spatial and attribute criteria.

A feature layer can contain a large number of features stored in ArcGIS. You can query a layer to access a subset of its features using any combination of spatial and attribute criteria. You can control whether or not each feature's geometry is returned, as well as which attributes are included in the results. Queries allow you to return a well-defined subset of your hosted data for analysis or display in your ArcGIS Runtime app.

In this tutorial, you'll write code to perform SQL queries that return a subset of features in the LA County Parcel feature layer (containing over 2.4 million features). Features that meet the query criteria are selected in the map.

Prerequisites

Before starting this tutorial:

You need an ArcGIS Location Platform or ArcGIS Online account.
Confirm that your system meets the minimum system requirements.
An IDE for Java.

Steps

Open a Java project with Gradle

To start this tutorial, complete the Display a map tutorial, or download and unzip the Display a map solution into a new folder.
Open the build.gradle file as a project in IntelliJ IDEA.
If you downloaded the solution, get an access token and set the API key.
An API Key gives your app access to secure resources used in this tutorial.
1. Go to the Create an API key tutorial to obtain a new API key access token using your ArcGIS Location Platform or ArcGIS Online account. Ensure that the following privilege is enabled: Location services > Basemaps > Basemap styles service. Copy the access token as it will be used in the next step.
2. In the IntelliJ IDEA > Project tool window, open src/main/java/com.example.app and double-click App.
3. In the start() method, set the API key property on the ArcGISRuntimeEnvironment with your access token. Replace YOUR_ACCESS_TOKEN with your copied access token. Be sure to surround your access token with double quotes as it is a string.
  App.java
  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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 ArcGISRuntimeEnvironment.setApiKey("YOUR_ACCESS_TOKEN");
  Warning
  The access token is stored directly in the code as a convenience for this tutorial. Storing access tokens in the source code is not best practice.

Rename the app and modify the initial viewpoint

Modify the Display a map tutorial's App.java file for use in this tutorial by changing the app title and modifying the viewpoint.

In the start() lifecycle method, change the title that will appear on the application window to Query a feature layer (SQL).

App.java
Expand
Use dark colors for code blocks
  @Override
  public void start(Stage stage) {

    // set the title and size of the stage and show it
    stage.setTitle("Query a feature layer (SQL)");

    stage.setWidth(800);
    stage.setHeight(700);
    stage.show();
Expand

Modify the Viewpoint constructor call so it passes a scale parameter of 72.000.0, which is more appropriate to this tutorial.

App.java
Expand
Use dark colors for code blocks
    ArcGISMap map = new ArcGISMap(BasemapStyle.ARCGIS_TOPOGRAPHIC);
    mapView.setMap(map);

    mapView.setViewpoint(new Viewpoint(34.02700, -118.80543, 72000));
Expand

Add import statements

You will import the types needed in this tutorial.

In the IntelliJ IDEA > Project tool window, open src/main/java/com.example.app and click App. Add the following imports, replacing those from the Display a map tutorial.

App.java
Use dark colors for code blocks
package com.example.app;

import javafx.application.Application;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.control.Alert;
import javafx.scene.control.ComboBox;
import javafx.scene.control.SingleSelectionModel;
import javafx.scene.layout.Background;
import javafx.scene.layout.BackgroundFill;
import javafx.scene.layout.CornerRadii;
import javafx.scene.layout.StackPane;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
import javafx.scene.paint.Paint;
import javafx.scene.paint.Color;
import javafx.scene.control.Label;

import com.esri.arcgisruntime.ArcGISRuntimeEnvironment;
import com.esri.arcgisruntime.concurrent.ListenableFuture;
import com.esri.arcgisruntime.data.Feature;
import com.esri.arcgisruntime.data.FeatureQueryResult;
import com.esri.arcgisruntime.data.FeatureTable;
import com.esri.arcgisruntime.data.QueryParameters;
import com.esri.arcgisruntime.data.ServiceFeatureTable;
import com.esri.arcgisruntime.geometry.Envelope;
import com.esri.arcgisruntime.layers.FeatureLayer;
import com.esri.arcgisruntime.layers.Layer;
import com.esri.arcgisruntime.loadable.LoadStatus;
import com.esri.arcgisruntime.mapping.ArcGISMap;
import com.esri.arcgisruntime.mapping.BasemapStyle;
import com.esri.arcgisruntime.mapping.Viewpoint;
import com.esri.arcgisruntime.mapping.view.MapView;

public class App extends Application {

Add UI for selecting a predefined query expression

To make performing a query on a feature layer more flexible, add a JavaFx ComboBox control that presents a list of predefined attribute queries for the parcels dataset.

Create a private field named parcelsComboBox in the App class.

App.java
Use dark colors for code blocks
public class App extends Application {

  private ComboBox<String> parcelsComboBox;

  private MapView mapView;

Create a setupUI() function, and define the label for the combo box and the strings (as an ObservableList<String>) that the combo box will present to the user. These strings are the SQL expressions for querying the feature layer. Then create the combo box, set its width, and set the default value to index 0, whose value is "Select an expression".

App.java
Expand
Use dark colors for code blocks
   // Creates a UI with a combo box.
  private void setupUI(StackPane stackPane) {

    // create a label
    Label parcelsComboBoxLabel = new Label("WHERE EXPRESSION: ");
    parcelsComboBoxLabel.setTextFill(Color.WHITE);

    // create the list of expressions for the combo box and then create the combo box
    ObservableList<String> parcelsComboBoxList = FXCollections.observableArrayList("Select an expression",
        "UseType = 'Government'", "UseType = 'Residential'", "UseType = 'Irrigated Farm'", "TaxRateArea = 10853",
        "TaxRateArea = 10860", "Roll_LandValue > 1000000", "Roll_LandValue < 1000000");
    parcelsComboBox = new ComboBox<>(parcelsComboBoxList);
    parcelsComboBox.setMaxWidth(Double.MAX_VALUE);

    // set the default combo box value
    parcelsComboBox.getSelectionModel().select(0);

  }
Expand

Configure the VBox that will contain the label and the combo box.

App.java
Expand
Use dark colors for code blocks
    // set up the control panel UI
    VBox controlsVBox = new VBox(6);
    controlsVBox.setBackground(new Background(new BackgroundFill(Paint.valueOf("rgba(0, 0, 0, 0.3)"),
        CornerRadii.EMPTY, Insets.EMPTY)));
    controlsVBox.setPadding(new Insets(10.0));
    controlsVBox.setMaxSize(210, 50);
    controlsVBox.setVisible(true);
Expand

Add the label and the combo box to the VBox named controlsVBox. Then add controlsVBox to the stack pane.

App.java
Expand
Use dark colors for code blocks
    // add the label and combo box to the control box
    controlsVBox.getChildren().addAll(parcelsComboBoxLabel, parcelsComboBox);

    // add the control box to the stack pane and set its alignment and margins
    stackPane.getChildren().add(controlsVBox);
    StackPane.setAlignment(controlsVBox, Pos.TOP_LEFT);
    StackPane.setMargin(controlsVBox, new Insets(10,0,0,10));
Expand

In the start() lifecycle function, call the setupUI() function you just created.

App.java
Expand
Use dark colors for code blocks
    ArcGISMap map = new ArcGISMap(BasemapStyle.ARCGIS_TOPOGRAPHIC);
    mapView.setMap(map);

    mapView.setViewpoint(new Viewpoint(34.02700, -118.80543, 72000));

    // create a parcels combo box and label, and add to vertical controls box
    setupUI(stackPane);

Expand

Add code to execute the selected query for the current map extent

In this step, create a new function that queries a FeatureLayer (identified using its ID) using both attribute and spatial criteria. After clearing any currently selected features, a new query will be executed to find features in the map's current extent that meet the selected attribute expression. The features in the FeatureQueryResult will be selected in the parcels layer.

The function takes three arguments: the ID for the layer to query (string), a SQL expression that defines attribute criteria (string), and the area of the MapView currently being viewed ( Envelope ).

Create a queryFeatureLayer() function that takes three parameters. Access the map's operational layers, and use Layer.getId() to retrieve the feature layer you wish to query. Then get the layer's feature table.

App.java
Expand
Use dark colors for code blocks
  // Query the feature layer, providing a Where expression and the current extent.
  // Then select the features returned by the query.
  private void queryFeatureLayer(String featureLayerId, String whereExpression, Envelope queryExtent) {

    try {
      FeatureLayer featureLayerToQuery = null;

      // get the layer based on its Id
      for (Layer layer : mapView.getMap().getOperationalLayers()) {
        if (layer.getId().equals(featureLayerId)) {
          featureLayerToQuery = (FeatureLayer) layer;
          break;
        }
      }

      // check if feature layer retrieved based on layer Id
      if (featureLayerToQuery == null){
          String msg = "Specified Id did not match any feature layer in this map";
          new Alert(Alert.AlertType.ERROR, msg).show();
          return;
      }

      // get the feature table from the feature layer
      FeatureTable featureTableToQuery = featureLayerToQuery.getFeatureTable();

    } catch (Exception e) {
        // on any error, display the stack trace
        e.printStackTrace();
    }

  }
Expand

Clear any previous selections from the feature layer. Then create query parameters, using the whereExpression and queryExtent parameters passed into the function.

App.java
Expand
Use dark colors for code blocks
      // clear any previous selections
      featureLayerToQuery.clearSelection();

      // create a query for the state that was entered
      QueryParameters query = new QueryParameters();
      query.setWhereClause(whereExpression);
      query.setReturnGeometry(true);
      query.setGeometry(queryExtent);
Expand

Call queryFeaturesAsync(), passing in the query parameters. The call returns a listenable future that will contain the feature query result. Add a done listener, which will execute when the query is complete. The listener will select features from the feature layer you queried.

App.java
Expand
Use dark colors for code blocks
      // call query features
      ListenableFuture<FeatureQueryResult> future = featureTableToQuery.queryFeaturesAsync(query);

      // create an effectively final variable for access from add done listener
      FeatureLayer finalFeatureLayerToQuery = featureLayerToQuery;

      // add done listener to fire when the query returns
      future.addDoneListener(() -> {
        try {
          // check if there are some results
          FeatureQueryResult featureQueryResult = future.get();
          if (featureQueryResult.iterator().hasNext()) {
            for (Feature feature : featureQueryResult) {
              finalFeatureLayerToQuery.selectFeature(feature);
            }
          } else {
            String msg = "No parcels found in the current extent, using Where expression: " + whereExpression + ".";
            new Alert(Alert.AlertType.INFORMATION, msg).show();
          }
        }
        catch (Exception e) {
          String msg = "Feature search failed for: " + whereExpression + ". Error: " + e.getMessage();
          new Alert(Alert.AlertType.ERROR, msg).show();
          e.printStackTrace();
        }
      });
Expand

Create the parcels feature layer and add a selection listener to the combo box

Create the LA parcels FeatureLayer from the feature service. Providing a Layer ID allows for referencing the parcels layer from the layer collection when needed. Load the layer and define a done loading listener.

In the done loading listener, create a listener that handles changes to the current selection in the combo box. The selection listener gets the current combo box choice and the current extent and then calls queryFeatureLayer() with those parameters.

Then add the parcels feature layer to map's collection of data layers ( GeoModel.getOperationalLayers() ) and finally, define the selection color via the map view's SelectionProperties to distinguish selected features in the map.

Create a function named createFeatureLayer() and pass in the map. Create a service feature table from a feature service URL. Then use Layer.setId() to set an ID for use when querying the layer.

App.java
Expand
Use dark colors for code blocks
   // Create and load the parcels feature layer from the feature service. When the layer is loaded, query it based on
   // current extent and current selection in parcelsComboBox
  private void createFeatureLayer(ArcGISMap map){

    FeatureTable serviceFeatureTable =
        new ServiceFeatureTable("https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/LA_County_Parcels/FeatureServer/0");

    FeatureLayer layer = new FeatureLayer(serviceFeatureTable);

    // give the layer an ID, so we can easily find it later
    layer.setId("Parcels");

  }
Expand

Load the feature layer and add a done loading listener in which you do the following: get the selection model for the combo box and then the selectedItemProperty().

Then add a different listener that handles changes to the selected item property. (It is the InvalidationListener, since whenever the selected item property is changed to a new value, the property is considered "invalidated".) In the property listener, clear any current selection of features, get the current selction in the combo box and the current extent, and finally, call queryFeatureLayer() with layer ID "Parcels", the combo box choice, and the extent.

App.java
Expand
Use dark colors for code blocks
    // Load the layer and add a done loading listener, which runs only when the layer is completely loaded.
    layer.loadAsync();
    layer.addDoneLoadingListener( () -> {
      if (layer.getLoadStatus() == LoadStatus.LOADED) {
        // get the selected item property from the combo box, and add a listener that runs
        // when the property value is changed by the user.
        SingleSelectionModel<String> selectionModel = parcelsComboBox.getSelectionModel();
        selectionModel.selectedItemProperty().addListener(observable -> {
          if (selectionModel.getSelectedIndex() == 0) {
            layer.clearSelection();
            return;
          }
          String currentParcelsChoice = selectionModel.getSelectedItem();

          Envelope currentExtent =
              (Envelope) (mapView.getCurrentViewpoint(Viewpoint.Type.BOUNDING_GEOMETRY).getTargetGeometry());

          queryFeatureLayer(layer.getId(), currentParcelsChoice, currentExtent);
        });
      }
    });
Expand

Add the feature layer to the map's operational layers.

App.java
Expand
Use dark colors for code blocks
    //  add the layer to the map
    map.getOperationalLayers().add(layer);

Expand

Set the color in which features returned by the query are highlighted. You set the selection properties by calling mapView.getSelectionProperties().setColor(0xffffff00). The highlight color is yellow.

App.java
Expand
Use dark colors for code blocks
    //  add the layer to the map
    map.getOperationalLayers().add(layer);

    mapView.getSelectionProperties().setColor(0xffffff00);
Expand

In the start() lifecycle function, call createFeatureLayer().

App.java
Expand
Use dark colors for code blocks
    ArcGISMap map = new ArcGISMap(BasemapStyle.ARCGIS_TOPOGRAPHIC);
    mapView.setMap(map);

    mapView.setViewpoint(new Viewpoint(34.02700, -118.80543, 72000));

    // create a parcels combo box and label, and add to vertical controls box
    setupUI(stackPane);

    // create parcels feature layer
    createFeatureLayer(map);
Expand

Run the app. Ensure to run the app as a Gradle task and not as an application in your IDE. In the Gradle tool window, under Tasks > application, click run.

The app loads with the map centered on the Santa Monica Mountains in California with the parcels feature layer displayed. Choose an attribute expression, and parcels in the current extent that meet the selected criteria will display in the specified selection color.

What's next?

Learn how to use additional API features, ArcGIS location services, and ArcGIS tools in these tutorials: