Database and cloud data warehouse data in ArcGIS

ArcGIS applies certain rules when connecting to a data source. The following apply when ArcGIS connects to and reads data in a database or cloud data warehouse:

Object names

Database management systems and cloud data warehouse providers have different definitions of acceptable characters for object names. Sometimes the database allows you to use unsupported characters in object names if you provide the object name enclosed in delimiters, such as double quotation marks. However, ArcGIS doesn't delimit object names so cannot recognize these names. See Object name limits for more information.

Name length

ArcGIS can read and (in some cases) create database objects such as tables, views, and users. The maximum name length that ArcGIS allows for these objects is listed in the following table, along with exceptions when applicable.

Note:

If your database allows fewer characters than what ArcGIS allows, you are limited to the number of characters allowed by the database. Consult the documentation for your database management system for its object name limits.

The number of characters listed assumes the use of single-byte characters.

Object typeMaximum characters created by ArcGIS*Maximum characters read by ArcGISExceptions

Database name

Not applicable for any database except SQLite.

250 when creating in SQLite.

250 when reading from SQLite.

31 when reading from other databases.

127 when reading from Amazon Redshift and Snowflake.

ArcGIS can read the maximum database name allowed by Oracle.

Database name values do not apply for SAP HANA.

The database name for SQLite consists of the file path, file name, and the file extension. If the SQLite database is configured to use the ST_Geometry or SpatiaLite spatial type, the file extension is (.sqlite). If the SQLite database is configured as an OGC GeoPackage, the file extension is (.gpkg).

Table, feature class, or view name

128

158 when creating in SQLite.

128

158 when reading from SQLite.

127 when reading from Redshift.

Index name

16 in all databases except SQL Server and SQLite, for which you can create index names of up to 128 characters.

Up to the database limit.

Index names do not apply in SAP HANA or cloud data warehouses.

Field (column) name

31 when creating in SQL Server, PostgreSQL, SAP HANA,Teradata, and SQLite.

30 when creating in Db2 and Oracle.

31 when reading from PostgreSQL and SQLite.

30 when reading from Db2 and Oracle 12.1 or earlier releases.

128 when reading from Snowflake or Google BigQuery.

127 when reading from Redshift.

Up to the database limit for other supported databases.

User name

ArcGIS only creates users in the following databases. Maximum user name lengths created are shown.

  • Oracle—30 in 12.1 or lower releases; 31 in 12.2 and higher releases
  • PostgreSQL—31
  • SAP HANA—31
  • SQL Server—128

Depends on how you connect; the Database Connection dialog box only allows 31 characters.

The Create Database Connection geoprocessing tool accepts up to the database limit but is only supported with databases, not cloud data warehouses.

User name values do not apply to SQLite or BigQuery.

Password

ArcGIS only creates passwords for users in the following databases. Maximum password lengths created are shown.

  • Oracle—30
  • PostgreSQL—75
  • SAP HANA—31
  • SQL Server—128

256 for all supported databases and cloud data warehouses except Teradata, for which only 31 characters are supported and Redshift, for which only 64 characters are supported.

Password values do not apply to SQLite or BigQuery.

*You cannot use ArcGIS to create objects in cloud data warehouses.

For BigQuery, the maximum supported default dataset name length is 31 characters. The maximum supported project name length is 30 characters.

Geometry validation

When you create data in a database using an ArcGIS client, ArcGIS validates the geometry based on specific rules. If the geometry you construct in an ArcGIS client is not valid, ArcGIS will not allow you to insert it to the database.

Esri and IBM ST_Geometry constructor functions and ArcGIS API use the Esri shape library to validate geometry data when the feature is created. Therefore, even if you create an Esri or IBM ST_Geometry shape using SQL, validation rules are applied and invalid geometries will not be committed to the database.

Other spatial types, such as SDO_Geometry, PostGIS geometry or geography, and Microsoft SQL Server geometry or geography, have their own constructor functions and use their own geometry verification rules. These rules may not be the same as those applied by ArcGIS. ArcGIS clients verify geometry when the data is read and will not display geometries that violate ArcGIS geometry validation rules. If you connect to a database that contains data created outside ArcGIS, you need to be aware of the rules ArcGIS applies.

Validation rules for points

  • The area and length of points are 0.0.
  • A single point's envelope is equal to the point's x,y values.
  • The envelope of a multipart point is the minimum bounding box.

Validation rules for simple lines or linestrings

  • Each part must have at least two distinct points.
  • Each part may not intersect itself. The start and end points can be the same, but the resulting ring is not treated as a polygon.
  • Parts may touch each other at the end points.
  • The length is the sum of all the parts.

Validation rules for lines or spaghetti strings

  • Lines can intersect themselves.
  • Each part must have at least two distinct points.
  • The length is the sum of all the parts.

Validation rules and operations for polygons

  • Dangles are not valid.
  • Line segments that compose the polygon are verified to be closed (z-coordinates at start and end points must also be the same) and don't cross.
  • For polygons with holes, holes must reside wholly inside the outer boundary. Any holes that are outside the outer boundary are invalid.
  • A hole that touches an outer boundary at a single common point is converted into an inversion of the polygon.
  • Multiple holes that touch at common points are combined into a single hole.
  • Multipart polygons cannot overlap. However, two parts may touch at a point.
  • Multipart polygons cannot share a common boundary.
  • If two rings have a common boundary, they are merged into one ring.
  • The total geometry perimeter, including the boundaries of all holes in donut polygons, is calculated and stored as the length of the geometry.
  • The area is calculated.
  • The envelope is calculated.
  • The points of a polygon are stored counterclockwise, although ArcGIS clients can return points in either direction. If you generate a polygon that contains clockwise coordinates, ArcGIS changes the rotation to ensure that the coordinates follow a counterclockwise direction.

    Polygon vertices stored in a counterclockwise direction

    In this example, the starting point is a and the correct coordinate description is a,b,c,d,a.

  • A polygon can have inversions, which are empty spaces inside the polygon that touch the outer boundary.

    Vertex direction for a polygon with inversions

  • Inverted polygons have an interior ring that touches the outer boundary. These are not considered donut polygons.
  • When a polygon has a donut hole, the outer boundary is read first. The points of the holes are stored in the opposite direction of the outer boundary.

    In the following figure, the outer boundary is described as a,b,c,d,a, and the hole is described as e,f,g,h,e. For the entire polygon, the feature is stored as a,b,c,d,a,e,f,g,h,e.

    Vertex direction for a polygon with a hole

  • If two holes touch at one point, they are treated as one hole, not two.

    Vertex direction for polygon with two holes that share a common point

    In this example, the two inner areas are a single hole because they share a common point, g.

  • The combination of polygons and polygons with holes can become fairly complex as shown in the next graphic. The outer boundary is a lake with an island. There is a small lake on the island.

    Nested polygons

    It is possible to represent these polygons as one, two, or three features. You could store each polygon as a separate feature. The larger lake is a donut polygon. The boundary of the large lake is the outer boundary while the island is the boundary of the single donut hole. The island is also a donut polygon. The boundary of the island is the outer boundary while the small lake is the hole. The small lake is a single polygon.

    If the lakes are the important features, one feature with a hole represents the big lake, while a second shape represents the small lake. You could also use a multipart polygon to represent the feature. The first polygon part is the big lake with a donut hole representing the island. The second part is the small lake.

  • Nil geometries are valid and act as placeholders. They allow you to have an attribute row without a corresponding geometry. One example that would result in a nil geometry is a request for the intersection of two nonintersecting polygons.

    Nonintersecting polygons result in a nil geometry

    Because areas A and B do not touch, the result of an intersection query produces an empty geometry.

  • Features have dimensions. A feature with only x,y-coordinates is considered two-dimensional. A feature that has z-coordinates or measures is three-dimensional. A feature can also have a nil measure.

Spatial metadata

Geodatabases store metadata about the spatial data it contains. ArcGIS clients can read information about the feature class's entity type (for example, points, lines, or polygons), dimensionality (x, y, z, and m coordinates), spatial reference, unique identifier, and feature class extent from geodatabase system tables. These same tables are not available when ArcGIS connects to a feature class in a database. For this reason, ArcGIS must get that information from other sources.

When you drag a feature class (spatial table) into a map in ArcGIS Pro, it creates a query layer. To define the query layer, ArcGIS Pro does the following:

  1. It queries the database for information about the spatial table, such as dimensionality, spatial reference, and geometry type.
  2. If that information is not defined for the table as a whole, ArcGIS Pro reads the first row in the table and uses the information from that feature.
  3. If the spatial reference, geometry type, or dimensionality cannot be determined by looking at the first row—for example, if the table doesn't contain data or the row is using a custom spatial reference—you will be prompted to supply this information to use for the layer definition.
  4. ArcGIS Pro queries the database to find a not null column that can be used as a unique identifier (ObjectID) for the rows in the specific table. If a qualifying row cannot be found, you are prompted to define an ObjectID for the table.
  5. Lastly, ArcGIS Pro calculates the extent of the layer when you add it to the map.

Because what gets displayed in the map is based on a query, you can open the query layer definition and change it. For example, if your feature class contains both points and polygons, you can open the query layer definition and choose which geometry type to display. Similarly, if your feature class contains multiple spatial columns, you can open the query layer definition and change the query to include a different spatial columns instead. Be aware, though, that the query cannot be altered to display unsupported data types.