Skip To Content

Understanding data in relational databases

Note:

Database connections are supported in Insights in ArcGIS Enterprise and Insights desktop.

Relational databases

Data in relational databases is stored in tables. Each table is a set of rows and columns. Each column has a type, and many databases support one or more native spatial data types.

The data type determines the following:

  • Which values can be stored in a column
  • Which operations can be performed on the data in that column
  • How the data is physically stored in the database

ArcGIS Insights supports direct access to specific data types from a list of supported database management systems. When accessing a database table directly through an add data workflow, Insights filters out any unsupported data types.

Note:

Database tables accessed through Insights are read-only and cannot be edited. This includes cases in which a dataset has been shared with others in your organization as a feature layer, and edits are being attempted in a client application other than Insights.

Tip:

When using data from a database causes an error in Insights in ArcGIS Enterprise, detailed information is added to the ArcGIS Server logs on your portal’s hosting server site. Work with your ArcGIS Server administrator to diagnose and resolve any issues.

Connecting to a database

Before you can use data from a database in Insights, you must create a database connection. Before you create a database connection, certain prerequisites must be met.

The process of establishing a database connection in Insights in ArcGIS Enterprise creates a Relational Database Connection item in the portal’s Content tab and the Connections tab on the Insights home page. This item can subsequently be shared with others. Sharing a database connection item only shares the ability to browse the contents of the database. The credentials used when establishing the connection are never exposed to those with whom the item has been shared.

If an issue arises while creating a database connection, see Troubleshoot a database connection.

Caution:

If you're having trouble using a database connection that previously worked in Insights, you may need to update the connection. Deleting the database connection will render any dependent datasets inoperable. You should only delete a relational database connection when you are sure no datasets are dependent, or you purposefully want to disable upstream datasets.

While database connections update to reflect the current state of the database, datasets reflect the schema of the table or view at the time of dataset creation. Datasets created from a database connection are dependent on the schema, naming conventions, and existing spatial data objects (geometry types and spatial reference identifiers) of the database. Renaming or deleting tables and views referenced by a dataset will break the dataset. Likewise, field names and data types must remain static for a dataset to be functional.

Geodatabases

Insights allows you to create a connection to a supported Microsoft SQL Server, Oracle, SAP HANA, or PostgreSQL database that has a non-versioned enterprise geodatabase defined. If the geodatabase is versioned, the data must be unregistered as versioned to work in Insights. Currently, only geodatabase user tables not created under the sde user schema can be viewed and accessed from Insights. Insights does not work directly with file and personal geodatabases.

Additional database platforms

The ArcGIS platform supports many database management systems to which Insights does not support direct access. To use tables from these databases (geodatabase-enabled or not), they must first be made available in your portal as a feature layer.

Note:

The list of database management systems with which Insights can work directly will grow over time. Contact Esri Technical Support to provide feedback on your preferred vendor.

Uniqueness of rows

Insights requires the ability to uniquely identify each row in a dataset. The following logic is used in determining the columns to be used to identify uniqueness:

  1. Look for a primary key. If found, use the columns that define the primary key.
  2. Look for an index that is flagged as unique. If found, use the columns that define the index.
Tip:

There can be unexpected results if a unique index is used and there are null values in the chosen columns. Therefore, it is considered best practice to have a primary key defined on all tables. If it is not possible to define a primary key, the columns that participate in a unique index should be flagged in the database as not null.

If the above criteria are not met, Insights will use all nonspatial columns in the table to determine the uniqueness of rows. Spatially-enabled tables will be treated as nonspatial datasets under these conditions.

Each database has a limit on the number of columns that can be used in a primary key. The primary key operation will fail if there are still duplicate records after all the columns in the table are used or the column limit it reached for the database. The following table summarizes the limits on the number of columns that can be used to create a primary key:

DatabasePrimary key column limit
PostgreSQL

32

Microsoft SQL Server

16

SAP HANA

No limit

Oracle

32

Spatial datasets

Database tables do not need to be spatially enabled to be used in Insights. A spatially enabled table contains a field that Insights interprets as a location field. When a location field is detected in a table, Insights makes a number of assumptions described in the following sections.

Single spatial column

Insights only supports one spatial column per database table. You can choose which spatial field will be used as the location field by clicking the location field icon Location field next to the table name under Selected Data and choosing from the list of spatial fields.

Selecting one of two spatial fields to use as the location field

Supported geometry types

The databases Insights supports are compliant with the Open Geospatial Consortium (OGC) and International Organization for Standardization (ISO) standard for simple feature access. The following table lists supported OGC/ISO geometry types, along with how they are interpreted in Insights:

OGC/ISOGeometry Type

POINT

Point

LINESTRING

MULTILINESTRING

Line

POLYGON

MULTIPOLYGON

Area

Insights does not enforce the OGC/ISO standards. When an unsupported geometry type is encountered, an error will occur.

Same geometry type

It is expected that all geometries in a spatial column share the same geometry type; for example, all point, all line, or all area. The geometry type for a dataset is determined by querying the first row in the table where the spatial column is not a null value.

Insights does not check for the same geometry. Unexpected results or errors may occur if rows in the dataset fail to meet this expectation.

Same spatial reference

It is expected that all geometries in a spatial column share a common spatial reference identifier (SRID). The spatial reference of the dataset is determined by querying the first row in the table where the spatial column is not a null value.

Insights does not check for the same spatial reference. Unexpected results or errors may occur if rows in the database fail to meet this expectation.

Project on the fly

Insights displays spatial data in the spatial reference system of your default basemap. This is for display and query purposes only; the underlying data is not altered. If the underlying geographic coordinate systems of the two spatial reference systems are incompatible, alignment and accuracy problems may be observed. To ensure fast performance and accurate display of spatial data, the spatial reference system of your spatial datasets should match the spatial reference system of the default basemap.

Note:

If your data is from a SAP HANA database and it is not possible for the spatial reference system of your spatial datasets to match the spatial reference system of the default basemap, it is recommended that unbounded SRIDs be used for your spatial datasets. Unbounded SRIDs will ensure that your spatial data can be displayed even if the extent of your organization's default basemap exceeds the extent of the spatial reference system of your spatial data.

Spatial operations

When performing spatial aggregation or spatial filtering using two datasets from a database connection, the spatial data in both datasets must have the same spatial reference system. For SQL Server database connections, the data must also have the same data type (geography or geometry).

Coordinate dimensionality

Coordinate dimensionality is defined by the x-, y-, z-, and m-coordinates for each vertex in a geometry. Insights ignores any z- and m-coordinates returned from the database.

Optimizing database content for performance

Properly configured and tuned databases always perform best. The following represent some considerations database administrators should make for ensuring an optimal user experience in Insights:

  • Up-to-date database statistics

    Database statistics are used by the database management system optimizer to choose the optimal execution plan for the query being executed. Up-to-date statistics will help maintain query performance.

  • Primary key constraints

    A primary key constraint uniquely identifies each row in a database table. Although not a requirement, it is recommended that a primary key be defined on database tables. Further, a single integer type field is recommend for a primary key.

  • Use both attribute and spatial indexes

    If your database platform supports it, index any fields used for querying or rendering your data.

  • Common spatial reference

    If possible, keep all data in a common spatial reference, and ideally, use the same projection as your organization’s default basemap. This will avoid on-the-fly projection calculations when drawing data on a map and will prevent errors during spatial analysis.

  • Simplified data

    Use the most simplified or generalized version of spatial data that will satisfy the visualization and analysis requirements of your organization. Simplified data has fewer vertices and line segments than complex datasets, meaning it will draw faster and analysis results will be returned sooner.

  • Spatial joins at ETL time

    Spatial joins at run time can be expensive. Because spatial data does not change often, it’s worth it to pay the price of performing spatial joins on data in the database once, and, at run time, performing attribute joins to achieve the same results.