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 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.
-
Go to the Create an API key tutorial to obtain a new API key access token. 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.
-
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 theArcGISRuntimeEnvironment
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.javaUse dark colors for code blocks ArcGISRuntimeEnvironment.setApiKey("YOUR_ACCESS_TOKEN");
-
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 toQuery a feature layer (
.SQ L) App.javaUse 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();
-
Modify the
Viewpoint
constructor call so it passes ascale
parameter of 72000, which is more appropriate to this tutorial.App.javaUse dark colors for code blocks ArcGISMap map = new ArcGISMap(BasemapStyle.ARCGIS_TOPOGRAPHIC); mapView.setMap(map); mapView.setViewpoint(new Viewpoint(34.02700, -118.80543, 72000));
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.javaUse 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 Combo
control that presents a list of predefined attribute queries for the parcels dataset.
-
Create a private field named
parcels
in theCombo Box App
class.App.javaUse dark colors for code blocks public class App extends Application { private ComboBox<String> parcelsComboBox; private MapView mapView;
-
Create a
setup
method, and define the label for the combo box and the strings (as anU I() Observable
>) 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".List <String App.javaUse 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); }
-
Configure the
V
that will contain the label and the combo box.Box App.javaUse 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);
-
Add the label and the combo box to the
V
namedBox controls
. Then addV Box controls
to the stack pane.V Box App.javaUse 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));
-
In the
start()
life-cycle function, call thesetup
function you just created.U I() App.javaUse 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);
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 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
query
method that takes three parameters. Access the map's operational layers, and useFeature Layer() Layer.getId()
to retrieve the feature layer you wish to query. Then get the layer's feature table.App.javaUse 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(); } }
-
Clear any previous selections from the feature layer. Then create query parameters, using the
where
andExpression query
parameters passed into the function.Extent App.javaUse 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);
-
Call
query
, passing in theFeatures Async() 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.javaUse 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(); } });
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 query
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
create
and pass in the map. Create a service feature table from a feature service URL. Then useFeature Layer() Layer.setId()
to set an ID for use when querying the layer.App.javaUse 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"); }
-
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
selected
.Item Property() Then add a different listener that handles changes to the selected item property. (It is the
Invalidation
, 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, callListener query
with layer ID "Parcels", the combo box choice, and the extent.Feature Layer() App.javaUse 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); }); } });
-
Add the feature layer to the map's operational layers.
App.javaUse dark colors for code blocks // add the layer to the map map.getOperationalLayers().add(layer);
-
Set the color in which features returned by the query are highlighted. You set the selection properties by calling
map
. The highlight color is yellow.View.get Selection Properties().set Color( Color. Yellow) App.javaUse dark colors for code blocks // add the layer to the map map.getOperationalLayers().add(layer); mapView.getSelectionProperties().setColor(Color.YELLOW);
-
In the
start()
life-cycle method, callcreate
.Feature Layer() App.javaUse 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);
-
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, double-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: