A quick tour of geodatabase system tables

The internal structure of the geodatabase is represented as a series of simple tables called the geodatabase system tables (the tables prefixed with GDB_), which track the contents of each geodatabase. They essentially describe the geodatabase schema that specifies all dataset definitions, rules, and relationships. These system tables contain and manage all the metadata required to implement geodatabase properties, data validation rules, and behaviors.

The internal structure of these tables was restructured beginning with the ArcGIS 10 release. The information related to the schema in the geodatabase, which prior to ArcGIS 10 was stored in over 35 geodatabase system tables, was consolidated into four main tables:

The GDB_Items and GDB_ItemRelationships tables represent the user-defined schema within a particular geodatabase. The contents of the tables are modified as the schema of the geodatabase is modified. The GDB_ItemTypes and GDB_ItemRelationshipTypes tables are static. The following diagram illustrates the relationships between these four tables:

Four primary geodatabase system tables

TipTip:

See the whitepaper XML Schema of the Geodatabase for XML definitions.

The GDB_Items table

A geodatabase is a container of geographic and nongeographic items. These items include tabular datasets, such as feature classes and tables; dataset containers, such as feature datasets, topologies, and geometric networks; datasets that control more advanced geodatabase behavior; and other items such as domains, toolboxes, and workspace extensions. Beginning with ArcGIS 10, all these items are stored within the geodatabase in the GDB_Items table, where each item is stored as a single row.

An item has the following attributes:

These seven attributes are those you are most likely to access using SQL.

The next attributes store information from an item's definition (such as a feature class's geometry type), which are duplicated outside the XML Definition to allow more efficient browsing of the geodatabase. Examples of these are the Dataset Subtype and Dataset Info properties, the codes of which are dependent on the item's type.

The GDB_ItemTypes table

The GDB_ItemTypes table contains the predefined set of recognized item types in the geodatabase. Item types are used to normalize the GDB_Items table (the type of an item in the GDB_Items table is represented as a UUID), to provide better support for future geodatabase functionality enhancements and define a type hierarchy. All parent types are abstract; therefore, parent items will not actually be found in the GDB_Items table.

The contents of the GDB_ItemTypes table are static; it is not modified as items are created or deleted.

An item type has the following attributes:

The GDB_ItemRelationships table

Nearly all geodatabase items have relationships with at least one other geodatabase item. For example, a stand-alone table is related to the root folder of a geodatabase, a domain is related to the feature classes or tables that rely on it for validation, and feature classes are related to the topology in which they participate. The GDB_ItemRelationships table is used to track these relationships.

Item relationships have the following attributes:

You would likely only access the first five attributes using SQL.

TipTip:

It is important to understand that an item relationship is not analogous to a relationship class. In fact, every relationship class has item relationships with two object classes (its source and destination classes) as well as an additional item relationship with its container (either the geodatabase's root folder or a feature dataset).

The GDB_ItemRelationshipTypes table

In the same way that a static collection of item types is used to classify items, a static collection of item relationship types is used to classify item relationships. For example, DatasetInFeatureDataset (indicating that a feature class, relationship class, or controller dataset exists inside of a feature dataset) and DomainInDataset (indicating that a dataset uses a domain for class-level or subtype-level validation) are two types of item relationships. Unlike item types, item relationship types do not have a hierarchy concept with parent and child types.

Item relationship types have the following attributes:

TipTip:

The Origin and Destination Item Type IDs are often identifiers of abstract item types. For example, the Origin Type ID of the DomainInDataset relationship type is Domain, which is an abstract item type with two subtypes: Range Domain and Coded Value Domain.

XML in geodatabase system tables

As mentioned in the attribute definitions for the geodatabase system tables, some of the fields use an XML data type. In ArcSDE geodatabases in IBM DB2, Microsoft SQL Server, and PostgreSQL, these columns use the native XML of the database management system and, therefore, can be queried using XPath expressions with SQL.

In geodatabases in Oracle and Informix, the XML columns use ArcSDE XML, which stores information as BLOBs in a series of separate tables. As such, they cannot be directly accessed with SQL.

To allow you to view the contents of the XML columns in the system tables in geodatabases in Oracle, two system views exist that store the contents from the geodatabase system table XML columns in a CLOB data type. The GDB_Items_vw shows the contents of the Definition, Documentation, and ItemInfo columns from the GDB_Items table in CLOB columns. The GDB_ItemRelationships_vw shows the contents of the Attributes column of the GDB_ItemRelationships table in a CLOB column. The contents of the CLOB columns can be read as text.

When querying these views, you extract the string from the CLOB column, convert it to an XML type, and execute an XPath query on it. Be aware that if you attempt to create an XML type for a column that contains a blank string, it will fail with the message "XML parsing failed" because Oracle does not support this.

To query the contents of the CLOB columns, you must configure the Oracle server to accept SQL connections. See Configuring the Oracle listener to use SQL for more information.

Related Topics


2/5/2013