Create a query layer for data in a document database—ArcGIS AllSource

You can drag a spatial table from a database connection (.dbconn) in the Catalog pane onto the map to automatically create a query layer that accesses data in a document database. Alternatively, use the Add Data button on the Map tab to create a query layer in a map in ArcGIS AllSource.

Tip:

Instructions for creating a query layer for a table in a supported document database are described below. See Create a query layer for data in a relational database or Create a query layer for data in a cloud data warehouse for instructions to create a query layer to access those source types.

Workflow summary

The following is a summary of what you must do to create a query layer of data from supported document databases:

Connect to the database—You can create a database connection in the Catalog pane, add it to the project, and browse to it from the New Query Layer dialog box. Alternatively, you can create the database connection from the New Query Layer dialog box. See Connect to Elasticsearch from ArcGIS AllSource or Connect to OpenSearch from ArcGIS AllSource for instructions.
Define the query—Choose the table and the fields to include in the query. Query layers use SQL statements. However, Elasticsearch and OpenSearch are not relational databases, so only a subset of SQL statements are supported when creating a query layer from data in those databases. You cannot include join or relate clauses, and WHERE clauses are limited.
Validate the query—The validation process checks the syntax of the query statement. It also uses the properties of the first row returned in the table to determine the validity of the query. A query layer cannot be added if the query statement is not valid.
Specify a unique identifier—All features in the map must have a unique identifier. The query layer must contain a unique identifier field that contains a value for each feature. By default, the query layer uses a system ID field as the unique identifier field for each feature. If you prefer to use a different ID field, you can choose it. If you choose a field, it must be an integer field that contains unique values.
Define the geometry type—The geometry type of the layer indicates whether the layer stores point, multipoint, line, or polygon features. By default, the geometry type of the first row is used to determine what geometry type exists in the table. Because ArcGIS supports only one geometry type per table, only features of that geometry type will draw on the map. You can specify a different geometry type than what is present in the first row returned by the query layer.
For example, if the first row returned by the query is a line feature, only line features will draw on the map. If the table contains polygon features, you can define the geometry type of the layer to be polygons, and only the polygon features returned by the query will draw on the map.
Nested geometry fields are not supported.

Supported WHERE clause formats

Not all SQL WHERE clause statements are supported when querying a document database from ArcGIS. The following syntax can be used in query layers accessing document databases:

<FIELD_NAME | LITERAL>'<='<FIELD_NAME | LITERAL>
<FIELD_NAME | LITERAL>'>='<FIELD_NAME | LITERAL>
<FIELD_NAME | LITERAL>'<'<FIELD_NAME | LITERAL>
<FIELD_NAME | LITERAL>'>'<FIELD_NAME | LITERAL>
<FIELD_NAME | LITERAL>'='<FIELD_NAME | LITERAL>
<FIELD_NAME | LITERAL>'<>'<FIELD_NAME | LITERAL>
<BOOLEAN EXPRESSION> AND | OR <BOOLEAN EXPRESSION>
NOT <BOOLEAN EXPRESSION>
<FIELD_NAME> LIKE <STRING>
<FIELD_NAME> NOT LIKE <STRING>
<FIELD_NAME> IN ( <LITERAL>, <LITERAL>, ... )
<FIELD_NAME> NOT IN ( <LITERAL>, <LITERAL>, ... )
<FIELD_NAME> BETWEEN <FIELD_NAME | LITERAL> AND <FIELD_NAME | LITERAL>
<FIELD_NAME> NOT BETWEEN <FIELD_NAME | LITERAL> AND <FIELD_NAME | LITERAL>

The syntax below are also supported; however, as the supported document databases do not support null values in fields, their use is slightly different than what the same clause returns when defined for a table in a relational database or data warehouse.

<FIELD_NAME> IS NULL—Returns rows that are missing a value for the specified field
<FIELD_NAME> IS NOT NULL—Returns rows that have a value for the specified field

Create a query layer for a table in Elasticsearch or OpenSearch

To add a query layer to a map that accesses data in Elasticsearch or OpenSearch, either drag the table onto the map or define a query layer on the New Query Layer dialog box.

Drag the table onto the map

When you drag a table onto the map from a database connection file (.dbconn) in the Catalog pane, ArcGIS AllSource automatically creates a query layer that selects all rows and fields from that table. It also sets the system-maintained _id field as the unique identifier field for the layer.

ArcGIS AllSource calculates the extent of the layer when you drag it into the map. ArcGIS AllSource also identifies the geometry type of the first row and sets that as the geometry type for the query layer. Only features of that geometry type will draw in the map.

After you add the layer to the map, you can alter the query layer if needed.

Define a query on the New Query Layer dialog box

Follow these steps to define a query layer in a map in an ArcGIS AllSource project using the New Query Layer dialog box:

Open the map in ArcGIS AllSource to which you want to add the query layer.
Click the arrow on the Add Data button in the Layer group on the Map ribbon and choose Query Layer from the drop-down menu.
The New Query Layer dialog box appears.
Specify a connection by doing one of the following:
- If the connection file (.dbconn) already exists in the project, select it from the drop-down list.
- If the connection does not exist in the project, click the new database connection button and create a connection. After you create the connection, choose it from the drop-down list.
In the Name text box, specify a name for the query layer to be created.
This is the name that will appear in the Contents pane of the map.
Type or paste an SQL query in the Query text box.
To see a list of tables (indices) and their fields, check the List of Tables check box. You can double-click a table in the List of Tables section to add all the fields to the Query text box and edit the query in the Query text box. For example, you can delete the fields from the query that you do not want included and add WHERE clauses to the query to limit the data included in the query layer.
You can use a WHERE clause as described in the section above, but no other clauses are supported.
Choose how the layer's spatial properties will be defined.
- Choose Let ArcGIS Pro discover spatial properties for the layer (the default option) to allow ArcGIS AllSource to use the spatial properties of the first row returned by the query. ArcGIS AllSource discovers these properties when you validate the SQL statement.
- Choose Define spatial properties for the layer if you want the layer to include features with spatial properties that are different than those of the first row.
Click Validate to ensure that the query syntax is correct and that ArcGIS can use the query.
The validation process runs the query in the database to confirm that the result set from the query meets the data modeling standards enforced by ArcGIS. A query layer is not added to the map until it is valid.
Rules for validation are as follows:
- The result set must have, at most, one spatial field.
- The result set must have only one shape type.
- The result set cannot have any field types not supported by ArcGIS.
If the validation fails for any reason, an error message is returned so you can modify the query.
Validation is especially important when working with data in databases that do not enforce the same standards as ArcGIS.
When the query passes validation, click Next.
To choose a unique identifier field other than the feature's internal ID field, check the box next to an integer field that contains unique values that ArcGIS can use as an ID for each field.
You do not have to choose a unique identifier field. If you do not specify a field, a system ID field is used.

Note:

M- and z-values are not supported.

You cannot define a spatial reference for the layer; SRID 4326, GCS WGS 1984 is always used.

Choose the geometry type from the drop-down menu.
This may already be set if you chose the Let ArcGIS Pro discover spatial properties for the layer option.
If you chose the Let ArcGIS Pro discover spatial properties for the layer option in step 6, click Finish to add the query layer to the map.

The next two steps only apply if you chose the Define spatial properties for the layer option.

In the Layer Extent Properties section, specify the query layer's spatial extent by choosing one of the following:
- Input Extent—When you click Next, you must type the extent. The extent you specify must be valid and include all the features in the table.
- Use Map Extent—The extent of the current map will be used for the query layer extent. Click Next to review the map extent values.
- Use Spatial Reference Extent—The extent of the spatial reference of the data is used for the query layer extent. For some spatial references, this is the entire globe. Click Next and review the spatial reference extent values.
When you finish setting or reviewing the layer extent, click Finish to add the query layer to the map.

For information about changing query layer properties, see Modify a query layer.

Feedback on this topic?