Database data and ArcGIS

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

Object names

Database management systems have different definitions of acceptable characters for object names. Most names must begin with a letter and cannot contain spaces, backslashes, or reserved database management system keywords. Some databases allow special characters such as forward slashes (/), underscores (_), dollar signs ($), dashes (-), dots (.), or mixed cases. Sometimes the database allows you to use special characters or reserved keywords, or force mixed-case, uppercase, or lowercase names if you provide the object name enclosed in delimiters, such as double quotation marks.

However, ArcGIS does not delimit object names. Do not create any tables, feature classes, indexes, databases, users*, roles, or other object names that require delimiters if you will be using them with ArcGIS. The object will be created in the database, but you cannot access it from ArcGIS.

*Microsoft SQL Server user names containing special characters are delimited to fully support Active Directory Groups and Windows Authenticated logins; however, ArcGIS does not support user names containing single quotation marks or apostrophes.

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 type	Maximum characters created by ArcGIS	Maximum characters read by ArcGIS	Exceptions
Database name	NA	31	ArcGIS can read the maximum database name allowed by Oracle. Database name values are not applicable for SAP HANA.
Table, feature class, or view name	128	128
Index name	16 in all databases except SQL Server, for which you can create index names of up to 128 characters.	Up to the database limit for indexes created outside ArcGIS.	Index names are not applicable for SAP HANA.
Field (column) name	31 when creating in SQL Server, PostgreSQL, SAP HANA, and Teradata. 30 when creating in Db2 and Oracle.	31 when reading from PostgreSQL 30 when reading from Db2 and Oracle 12.1 or earlier releases. 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.	User name values are not applicable to SQLite.
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 except Teradata, for which only 31 characters are supported.	Password values are not applicable to SQLite.

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.

  

  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.

  

• 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.

  

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

  

  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.

  

  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.

  

  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.