Query layers

Query layers are an integration method that allows ArcGIS to connect to relational databases as data sources using direct SQL queries rather than connecting through an enterprise geodatabase. Query layers are a general concept that allow for access and display of tabular data from various database sources, which may or may not include spatial data, but which can be read directly from that source without need for an ETL process or data load through other methods.

Query layers provide a flexible method to work with these database systems, and allow ArcGIS to integrate with other business systems and data storage offerings for primarily read-only use cases. Under certain conditions, query layers can be made editable by publishing them into a feature access-enabled map service to create a feature service.

Query layer patterns in ArcGIS

Query layers have two primary use patterns: Authoring and use in ArcGIS Pro, and publishing to ArcGIS Enterprise to use with ArcGIS clients. The sequential steps involved in both use patterns are similar.

The first step in authoring a query layer is the design process – what goal is the query layer designed to accomplish, and how can that be achieved efficiently and effectively? This process can be carried out with the support of data analysts, architects or database administration professionals inside your organization.

The main goal is to define a SQL query that returns data quickly, returns an appropriate and concise set of columns and rows, and includes data types for columns that are compatible with ArcGIS. Database administrators can also assist with patterns like materialized views, which can dramatically improve the query performance from ArcGIS to a complex view, such as one that includes many joins. Importantly, ensure that the query returns a column that contains unique, consistent values for each row in the query result. This uniqueness is required for ArcGIS to work with the query.

The second step to work with query layers is to create a database connection to the system storing the source data. The supported databases for query layers are defined in the ArcGIS Pro documentation, and include all of the databases used to host an enterprise geodatabase along with other options like cloud data warehouses or document databases. The database connection process includes an authentication step which supports specific scenarios, so be sure that your database supports standard database authentication to enable query layer access. Other authentication methods may be supported but should be carefully validated with ArcGIS documentation.

The third step is to create the query layer in ArcGIS Pro, using the Add Query Layer interface. In this dialog, provide the previously-developed SQL query and specify which column to use as a unique identifier. If a spatial column is returned, the interface will prompt for a spatial reference definition. After completion, the layer should load to the active map. Be careful with layers that contain many results as they may result in slow draw times if the SQL query is complex or requires searching across many source tables. See other best practices for query layer creation in the ArcGIS Pro documentation.

Once the query layer definition is complete and the layer is added to the map, it can be visualized and worked with like any other layer in ArcGIS Pro. Query layers can be symbolized, can have popups defined, and can be used as a source for analytical tools, exported to print products, or used to create vector tile layer outputs. Query layers are persisted when a Map is saved with a Project, and any user that opens that map will be able to see the same information as long as they have network access to the same database and the .sde file is persisted.

Optionally, query layers can also be published to ArcGIS Enterprise to create a map image layer, also known as a map service. This process follows standard publishing patterns with ArcGIS Enterprise and requires data store registration, cartographic design, and overall map service design processes just like other map services. There are also some specific considerations for query layers published from cloud data warehouses related to performance and caching.

Full support

Partial support

Query layers published to ArcGIS Enterprise as map image layers can be consumed in ArcGIS Online applications, but be aware of cross-system authentication patterns and ensure access is managed correctly.

Example use cases

Query layers can be used to meet a wide variety of business requirements, especially within environments where multiple departments or groups use similar database technologies deployed within the same network. Some representative examples of using query layers in geospatial systems are provided below:

• A land information management system managed by the GIS team of a county government is the primary system of record for parcel boundaries. The appraiser’s office uses a CAMA system from another provider, built on SQL Server, the same database used as an enterprise geodatabase by the GIS team. By creating a linked server in SQL Server, the GIS team can author queries that combine data from the CAMA system with parcels from the enterprise geodatabase to provide a single feature layer that shows all relevant fields.
• A commercial organization stores nationwide sales information in a cloud data warehouse. These rows number into the millions of transactions, and are updated daily. The geospatial team works with a data analyst to define some time-based analytical queries that display sales by state, county and ZIP code for each month. These queries independently can take 10-15 seconds to complete, but by using the materialized view feature of query layers from cloud data warehouses, the data can display quickly and will update regularly behind the scenes.
• A transportation agency has access to a commercial feed of traffic sensor information. This dataset is re-loaded into a PostgreSQL database nightly. The agency can author a query based on the current map scale (using query layer parameters) to join the data to an appropriate street class or functional class for smaller or larger map scales. This approach respects appropriate data density, improves map performance, and reduces compute load on the database.

Best practices

• Build efficient SQL queries for query layers to minimize processing time and resource usage. This is especially relevant for query layers from cloud data warehouses, which often record cost based on compute time.
• Ensure that query results include only one geometry or geography column, and that column contains only one type of geometry or geography, not a mix of points, lines and polygons.
• Ensure that your database configuration includes sufficient indexing on columns used in WHERE clauses and joins.
• Limit the number of records returned by using appropriate filters and pagination. Use LIMIT clauses during query development to check a few rows at a time for the expected data values, then expand the query.
• Validate the spatial references and coordinate systems of any spatial columns to ensure that the data display in the proper location in mapping use cases.
• Handle null values and data type mismatches appropriately in queries.
• Use query layer parameters to introduce scale and extent-based logic or create your own parameters to support common workflows.
• Test queries directly from an independent SQL client against the database before creating the query layer.
• Document query definitions, dependencies, and update procedures so other teams can assist with maintenance and future changes.
• Ensure that the relevant client (ArcGIS Pro or ArcGIS Server) operates from a network location with low latency and high bandwidth to the database source. This ensures that authoring, publishing and client consumption are all able to reach expected performance levels.