Skip To Content

Understanding data in relational databases

Note:

Database connections are supported in Insights Enterprise and Insights Local.

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

Insights for ArcGIS 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 the case where 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, 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 creates a Relational Database Connection item in the portal’s geoinformation model as part of My Content. This item can subsequently be shared with others. It is important to note that sharing a database connection item is only sharing 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.

When connecting to a database, things can go wrong. For more information, see Troubleshoot a database connection.

Caution:

If you're having trouble using a database connection item that previously worked in Insights, you may need to update the connection. Do not delete the database connection. When Insights creates a dataset from a database table (or multiple database tables in the case of a joined dataset), a database connection is required. Deleting the database connection will cause any dependent datasets to be inoperable. This caution is especially important if the database connection has been shared with others. Only when you're sure no datasets are dependent, or you purposefully want to disable upstream datasets, should you delete a relational database connection.

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. Note that datasets created from a database connection are dependent upon the schema, naming conventions, and existing spatial data objects (geometry types and spatial reference identifiers) of the database. It is imperative that the tables and views referenced by a dataset are not renamed or deleted, as this will break the dataset. Likewise, field names and data types must remain static for a dataset to be functional.

Geodatabases

Insights Enterprise and Insights Local allow 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 either a hosted or registered 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. To do this, 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.

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 either 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

Tip:

If Insights could not find a primary key, but could find a unique index, there can be unexpected results if there are any 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 not have null values and ideally would be flagged in the database as not null. Insights will not enforce this for you.

Spatial datasets

There is no requirement in Insights that database tables be spatially enabled. A spatially-enabled table contains a field that Insights interprets as a location field. When a location field is detected in a dataset (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 portal organization’s 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. Because it has fewer vertices and line segments, simplified data will draw much faster and the results analysis will be returned sooner. Getting this right for your needs may take some experimentation.

  • Spatial joins at ETL time

    Spatial joins at run time can be expensive. Because spatial data does not change all that much, 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.