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.

style a feature layer

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

The following are required for this tutorial:

An ArcGIS account to access your API keys. If you don't have an account, sign up for free.
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 project, set your API key.

An API Key enables access to services, web maps, and web scenes hosted in ArcGIS Online.

Go to your developer dashboard to get your API key. For these tutorials, use your default API key. It is scoped to include all of the services demonstrated in the tutorials.
In IntelliJ IDEA's Project tool window, open src/main/java/com.example.app and double-click App.

In the start() method, set the API key property on the ArcGISRuntimeEnvironment with your API key. Replace YOUR_API_KEY with your actual API Key. Be sure to surround your API Key with quotes, because the parameter passed to setApiKey is a string.

App.java
Use dark colors for code blocks
Change lineChange lineChange lineChange line
  @Override
  public void start(Stage stage) {
    // set the title and size of the stage and show it
    stage.setTitle("Display a map tutorial");
    stage.setWidth(800);
    stage.setHeight(700);
    stage.show();

    // create a JavaFX scene with a stack pane as the root node, and add it to the scene
    StackPane stackPane = new StackPane();
    Scene scene = new Scene(stackPane);
    stage.setScene(scene);

    // Note: it is not best practice to store API keys in source code.
    // The API key is referenced here for the convenience of this tutorial.
    String yourApiKey = "YOUR_API_KEY";
    ArcGISRuntimeEnvironment.setApiKey(yourApiKey);

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() life-cycle 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
Change lineChange line
  @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 72000, which is more appropriate to this tutorial.

App.java
Expand
Use dark colors for code blocks
Change line
    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 IntelliJ IDEA's'Project tool window, open src/main/java/com.example.app and double-click App. Add the following imports, replacing those from the Display a map tutorial.

App.java
Use dark colors for code blocks
Change lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange lineChange line
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
Add line.
public class App extends Application {

  private ComboBox<String> parcelsComboBox;

  private MapView mapView;

Create a setupUI() method, 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
Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.
   // 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
Add line.Add line.Add line.Add line.Add line.Add line.Add line.
    // 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 line.Add line.Add line.Add line.Add line.Add line.Add line.
    // 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() life-cycle function, call the setupUI() function you just created.

App.java
Expand
Use dark colors for code blocks
Add line.Add line.
    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

The method 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() method 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
Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.
  // 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
Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.
      // 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 parameter. 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
Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.
      // 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.setId() 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 the 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 method 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
Add line.Add line.Add line.Add line.Add line.Add line.Add line.
   // 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 selection 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

Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.Add line.
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 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 </