Developing spatial and attribute queries
The ArcGIS Mobile SDK allows you to perform spatial and attribute queries for functions such as identify and selection, and to locate features based on field values. This topic introduces spatial and attribute queries then shows you how to return features from queries.
Queries are performed against features, of a given layer, that are currently in the map cache. Since features in the cache may not represent all the features available on the server, you need to consider the content of the cache before performing these queries. If you expect to be doing queries in the field, in a disconnected environment, and want to work against all possible features of a given layer, you should pre-cache those features and layers on the device before going to the field. However, if you are working in a connected environment, you may request features from the server and then query the cache using the same query criteria. This is described in the final section below.
Queries can return records as a FeatureDataTable, an implementation of an ADO data table, or as a FeatureDataReader, an implementation of an ADO DataReader. FeatureDataReader objects access data much faster than FeatureDataTables but are read-only and can only read data sequentially, hence they are suited for functions like identify. In comparison, FeatureDataTables are editable, random access and can be bound to other controls such as data grids.
To query data you need a feature layer and a query filter. The query filter contains the spatial and/or attribute criteria for the query.
Spatial queries
Spatial queries require a filter geometry, such as a polygon, and a geometric relationship enumeration for a given feature layer. For example, return all the records for roads within the current map extent. The geometric relationship enumerations are based on the DE-9IM matrix and include Contain, Cross, Disjoint, Intersect, Overlap, Touch, Within and Any. The GeometricRelationship class contains more information on these relationships.
The following example shows a spatial query returning a FeatureDataTable. It will find features that are completely contained within an interactive rectangle on the map then flashes the geometry for the first returned record.
// Mouse down map coordinate private Coordinate m_startCoordinate; // Mouse up map coordinate private Coordinate m_endCoordinate; private void map1_MouseDown(object sender,ESRI.ArcGIS.Mobile.MapMouseEventArgs e) { // Anchor coordinate used to create an envelope to query features m_startCoordinate = e.MapCoordinate; } private void map1_MouseUp(object sender,ESRI.ArcGIS.Mobile.MapMouseEventArgs e) { // Last coordinate used to create an envelope to query features m_endCoordinate = e.MapCoordinate; // Finds features that are completely contain in the envelope FindFeaturesInEnvelope(); } private void FindFeaturesInEnvelope() { // Creates a new envelope based on those coordinates Envelope envelope = new Envelope(m_startCoordinate, m_endCoordinate); // Retrieves the first layer in the map FeatureLayer featureLayer = map1.MapLayers[0].Layer as FeatureLayer; if (featureLayer == null) return; // Creates a instance of a query filter. Query filter uses the dragged map rectangle and // it will search for all features in layer index = 0, that are contained in this geometry. QueryFilter spatialQueryFilter = new QueryFilter(envelope, EsriGeometricRelationship.Contain); // Querying the feature layer FeatureDataTable featureDataTable = featureLayer.GetDataTable(spatialQueryFilter); // If feature layer data table has no rows back if (featureDataTable.Rows.Count == 0) { MessageBox.Show("No features found"); return; } // If rows back, grabs the first row and geometry column in order to get the feature's geometry // and highlight it in order to locate the feature in the map Geometry geometry = featureDataTable.Rows[0][featureDataTable.GeometryColumnIndex] as Geometry; // if no geometry if (geometry == null) return; // If geometry is valid, the map will rendered it 3 times every 150 milliseconds. map1.FlashGeometry(Pens.Red, (SolidBrush)Brushes.Yellow, 15, 150, 3, geometry); }
The next example shows a spatial query returning a FeatureDataReader. This example shows map tip functionality by querying the layer display field for each feature under the mouse and displaying the value as a tooltip.
// FeatureLayer to be queried private FeatureLayer m_toolTipFeatureLayer; // FeautureLayer's display field index private int m_displayFieldIndex; private void map1_MouseMove(object sender, MapMouseEventArgs e) { // If no feature layer, selects the first one if (m_toolTipFeatureLayer == null) { // Retrieves the first layer in the map m_toolTipFeatureLayer = map1.MapLayers[0].Layer as FeatureLayer; // Retrieves the layer's display field m_displayFieldIndex = m_toolTipFeatureLayer.DisplayColumnIndex; } if (m_toolTipFeatureLayer == null) return; // Converts 5 pixels to map units int delta = map1.ToMap(5); // Creates an envelope using the current mouse map coordinate as the center // and the delta as the envelope's width and height Envelope envelope = new Envelope(e.MapCoordinate, delta, delta); // Creates a instance of a query filter. Query filter uses the dragged map rectangle and // it will search for all features in layer index = 0, that are contained in this geometry. QueryFilter spatialQueryFilter = new QueryFilter(envelope, EsriGeometricRelationship.Intersect); //Querying the feature layer. This requires the use of the using keyword to ensure that the reader will be dispose // It specifies when the reader should be release. using (FeatureDataReader reader = m_toolTipFeatureLayer.GetDataReader(spatialQueryFilter, null)) { // Moves the cursor to the next record while (reader.Read()) //Associates the tooltip to the map control and // sets the tooltip text to be the feature's layer display field value toolTip1.SetToolTip(map1, reader.GetValue(m_displayFieldIndex).ToString()); } }
Attribute queries
Attribute queries are used to select features based on an item value and are performed by providing a where clause and a flag to indicate case sensitivity in a query filter.
The where clause supports the following operators:
- Logical: Boolean, AND, OR, NOT
- Comparison: <, >, <=, >=, <>, =, IN, LIKE
- Arithmetic: + (addition), - (subtraction), * (multiplication), / (division), % (modulus)
- String: || (string concatenation) Sqlite doesn't support + operator for string concatenation
- Wildcard characters: When querying the mobilecache you may use the * and % characters combined with the LIKE operator. For queries against the server (as described in following sections) you have to use the wild card specific to the underlying DBMS. For example, use * for personal GDB and % for SDE, or if you want to query with a wildcard that represents only one character, use ? for personal GDB, _ for Oracle, or [] for SQL Server. The wildcard can be a prefix, suffix or both, but never in the middle of the string. _ used to represent only one character but can be any.
- Aggregates: Sum, Avg, Min, Max, Count, StdDev, Var
- Functions: CONVERT, LEN, ISNULL, IIF, TRIM, SUBSTRING, DATE If you want to filter features that match certain attribute's criteria with case sensitivity.
- With Sqlite implementation Date attribute in the WhereClause should be in one of the following formats
- QueryFilter filter = new QueryFilter("Sale_Date > strftime('%Y-%m-%d %H:%M:%S','2009-11-16 20:10:32','-10 hours') ", true);
- QueryFilter filter = new QueryFilter("Sale_Date < '1999-11-17'", true);
- QueryFilter filter = new QueryFilter("Sale_Date < Date('1999-11-17 12:00:00')", true);
- QueryFilter filter = new QueryFilter("Sale_Date > '1997-02-12' and Sale_Date < Date('1997-02-12','+2 years','+12 months')", true);
- dt = new DateTime(); String date1 = dt.ToString("yyyy-MM-dd"); QueryFilter filter = new QueryFilter(String.Format("Date(Sale_Date) > ('{0}')",date1), true);
The following example shows an attribute query returning a FeatureDataTable. It will search for features that contain a letter 'a' in a certain field and highlight the geometry of the first returned feature.
private void button1_Click(object sender, EventArgs e) { // Retrieves the first layer in the map FeatureLayer featureLayer = map1.MapLayers[0].Layer as FeatureLayer; if (featureLayer == null) return; // Creates a where clause string. // Use % or * to find a string that contains an a character string whereClause = featureLayer.DisplayColumnName + " like '%a%'"; // Creates a instance of a query filter. Query filter uses the dragged map rectangle and // it will search for all features in layer index = 0, that are contained in this geometry. QueryFilter queryFilter = new QueryFilter(whereClause, true); // Querying the feature layer FeatureDataTable featureDataTable = featureLayer.GetDataTable(queryFilter ); // If feature layer data table has no rows back if (featureDataTable.Rows.Count == 0) { MessageBox.Show("No features found"); return; } // If rows back, grabs the first row and geometry column in order to get the feature's geometry // and highlight it in order to locate the feature in the map Geometry geometry = featureDataTable.Rows[0][featureDataTable.GeometryColumnIndex] as Geometry; // if no geometry if (geometry == null) return; // If geometry is valid, the map will rendered it 3 times every 150 milliseconds. map1.FlashGeometry(Pens.Red, (SolidBrush)Brushes.Yellow, 15, 150, 3, geometry); }
The following example shows an attribute query returning a FeatureDataReader. It will search for features that contain a letter 'a' in a certain field and highlight the geometry of the first returned feature.
private void button1_Click(object sender, EventArgs e) { // Retrieves the first layer in the map FeatureLayer featureLayer = map1.MapLayers[5].Layer as FeatureLayer; if (featureLayer == null) return; // Creates a where clause string. // Use % as the wildcard char for data coming from SDE, and // * for data coming from a personal geodatabase // the _ character represents one character only. string whereClause = featureLayer.DisplayColumnName + " like '_a%'"; // Creates a instance of a query filter. Query filter uses the dragged map rectangle and // it will search for all features in layer index = 0, that are contained in this geometry. QueryFilter queryFilter = new QueryFilter(whereClause, true); Geometry geometry = null; // Querying the feature layer using (FeatureDataReader reader = featureLayer.GetDataReader(queryFilter, null)) { // Moves the cursor to the next record while (reader.Read()) { // Retrieves the feature’s geometry geometry = reader.GetGeometry(); if (geometry != null) // If geometry is valid, the map will rendered once for 100 milliseconds. map1.FlashGeometry(Pens.Red, (SolidBrush)Brushes.Yellow, 15, 100, 1, geometry); } } }