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 must begin with a letter and cannot contain spaces, back slashes, or reserved DBMS keywords. Some allow special characters such as forward slashes (/), underscores (_), dollar signs ($), dashes (-), dots (.), or mixed cases. Sometimes the DBMS allows you to use special characters, reserved keywords, or force mixed, upper-, 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, databases, users*, roles, or other object names that require delimiters if you will be using it with ArcGIS. The object will be created in the database, but you cannot access it from ArcGIS.

*SQL Server user names containing special characters are delimited to fully support Active Directory Groups and Windows Authenticated logins.

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

The number of characters listed assumes the use of ANSI text. UTF-8 characters use at least twice as many bytes as ANSI characters, thereby reducing the number of allowed characters.

	ALTIBASE	DB2	Informix	Netezza	Oracle	PostgreSQL	SAP HANA	SQL Server	Teradata
Database name	31	8	31	31	30	31	31	31	31
Field (column) name	31	31	31	31	30	31	31	31	31
Index name	40	128	128	NA	30	63	127	128	128
Password	22 (ALTIBASE on Windows) 16 (ALTIBASE on non-Windows operating systems)	31	31	31	31	31	127	31	30
Table or feature class name	40	128	128	128	30	63	127	128	128
User name	31	30	31	31	30	31	31	31	31
View name	40	128	128	128	30	63	127	128	128

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, 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 ArcMap, it creates a query layer. To define the query layer, ArcMap 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, ArcMap 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. ArcMap 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 will be prompted to define an ObjectID for the table.
5. Lastly, ArcMap calculates the extent of the layer when you add it to the map.

Because what gets displayed in ArcMap is based on a query layer, 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 one of the other spatial columns instead. Be aware, though, that the query cannot be altered to display unsupported data types.