Feature classes in a geodatabase in SQL Server

A feature class is a collection of geographic features, all of the same geometric type (point, line, polygon, multipoint, annotation, dimension, or multipatch). For example, you could store points for all the train depots along a railroad line in one feature class. In another feature class, you could store lines that represent all the railroad tracks.

This topic contains descriptions of the way a feature class appears in the Catalog window, in the database management system (DBMS), and in an XML document.

Feature classes in ArcGIS Desktop

There are seven types of feature classes you can create in ArcGIS Desktop: point, multipoint, line, polygon, annotation, dimension, and multipatch.

You can tell what type of feature class it is in the Catalog tree based on the icon. The following table shows the icon for each type of feature class.

Feature class icon

Type of feature class

Point or multipoint feature class icon

Point or multipoint

Line feature class icon

Line

Polygon feature class icon

Polygon

Annotation feature class icon

Annotation

Dimension feature class icon

Dimension

Multipatch feature class icon

Multipatch

In Microsoft SQL Server, the name of a feature class as it appears in the Catalog tree includes the name of the database the feature class is in, the name of the user who owns the feature class, and the name of the feature class itself.

For example, a feature class named parcels, owned by user sasha, in a geodatabase named geodata is listed as geodata.SASHA.PARCELS in the Catalog tree.

For a description of the different types of feature classes, see Feature class basics.

Feature classes stored in a Microsoft SQL Server database

Feature classes in SQL Server databases use one of three geometry storage types: binary, Microsoft geometry, or Microsoft geography.

All feature classes have a base table (also called a business table), which stores attribute information, an Object ID, and a Shape field. The Object ID and Shape fields can have different names, but they are required to be in the base table of a feature class.

Annotation and dimension feature classes have additional required fields in their base tables. See Managing annotation feature class properties and Dimension feature properties for a list of these fields.

NoteNote:

You will notice that once you have loaded data or run sdesetup, you have several i tables and stored procedures in your database. These stored procedures and i tables are used for generating feature IDs for feature classes. The i tables contain a number in their name. This number corresponds to the registration_id of the feature class in the SDE_table_registry table. Editing these tables or stored procedures is not supported and is highly discouraged.

Feature classes stored in a binary format in a SQL Server database

Feature classes stored in a binary format—either ArcSDE compressed binary or the OGC well-known binary representation of geometry—in a geodatabase in SQL Server consist of a base table and associated feature and spatial index tables described here.

By default, features are stored using the ArcSDE compressed binary type.

The base, f, and s tables for a feature class in SQL Server
  • The base table

    The base table is a DBMS table that stores attributes and is spatially enabled by adding a spatial column. The base table name is the name of the feature class. In the graphic above, the base table is the PARCELS table.

    Under the binary schema implementation, the spatial column in the base table (in the example above, the SHAPE field in the PARCELS table) contains a feature ID that uniquely references the spatial data. The feature ID joins the base table to the feature and spatial index tables.

    A database trigger is defined on the spatially enabled base table to maintain the relationship between records in the base table and the feature table.

    All nonversioned feature class base tables have a delete-update trigger and an insert trigger. The insert trigger ensures that the spatial column cannot have duplicate values in the base table, while the delete-update trigger manages activity against the spatial column in the base, feature, and spatial index tables.

    These triggers are automatically dropped whenever a feature class, either stand-alone or in a feature dataset, is multiversioned. They are re-created when a feature class is unregistered as versioned. If you edit one of these triggers, then multiversion the feature class, the trigger is dropped.

  • The feature table (F<layer_id>)

    The feature table stores the geometric shapes for each feature. This table is identified by the number in the Layer_ID column of the SDE_layers table. The relationship between the base and feature tables is managed through the Feature ID, or FID. This key, which is maintained by ArcSDE, is unique for the spatial column.

    Feature table definition

    Field name

    Field type

    Description

    Null?

    fid

    int

    Primary key; the unique ID that joins the feature table to the base table

    NOT NULL

    numofpts

    int

    The number of points defining the feature

    NOT NULL

    entity

    smallint

    The type of geometry stored in the spatial column (for example, point, line, or string)

    NOT NULL

    eminx

    float

    The minimum x-value in a feature; with eminy, emaxx, and emaxy, defines the envelope of a feature

    NOT NULL

    eminy

    float

    The minimum y-value in a feature; with eminx, emaxx, and emaxy, defines the envelope of a feature

    NOT NULL

    emaxx

    float

    The maximum x-value in a feature; with eminx, eminy, and emaxy, defines the envelope of a feature

    NOT NULL

    emaxy

    float

    The maximum y-value in a feature; with eminx, eminy, and emaxx, defines the envelope of a feature

    NOT NULL

    eminz

    float

    The minimum z-value in a three-dimensional feature

    emaxz

    float

    The maximum z-value in a three-dimensional feature

    min_measure

    float

    The minimum measure value in a feature

    max_measure

    float

    The maximum measure value in a feature

    area

    float

    The area of the geometry

    NOT NULL

    len

    float

    The length or perimeter of the geometry

    NOT NULL

    points

    varbinarymax

    Contains the byte stream of point coordinates that define the geometry

    As features are inserted or updated, values for such columns as the extents and numofpts are recalculated automatically.

  • The spatial index table (S<layer_id>)

    The spatial index of the binary implementation is the spatial index table. It stores references to shapes based on a simple, regular grid. This table is identified by the number in the Layer_ID column of the SDE_layers table.

    The spatial index contains an entry for each shape and grid cell combination to support spatial queries. A feature that crosses into three grid cells has three entries in the table. When a spatial query is performed, the grid cells within the search area are identified and used to return a list of candidate geometries.

    Spatial index table definition

    Field name

    Field type

    Description

    Null?

    sp_fid

    int

    Primary key; the unique ID that joins the spatial index table to the base table

    NOT NULL

    gx

    bigint

    The spatial index grid x-value

    NOT NULL

    gy

    bigint

    The spatial index grid y-value

    NOT NULL

    eminx

    bigint

    The minimum x-value in a feature; with eminy, emaxx, and emaxy, defines the envelope of a feature

    NOT NULL

    eminy

    bigint

    The minimum y-value in a feature; with eminx, emaxx, and emaxy, defines the envelope of a feature

    NOT NULL

    emaxx

    bigint

    The maximum x-value in a feature; with eminx, eminy, and emaxy, defines the envelope of a feature

    NOT NULL

    emaxy

    bigint

    The maximum y-value in a feature; with eminx, eminy, and emaxx, defines the envelope of a feature

    NOT NULL

    All feature class base tables have a DELETE-UPDATE trigger and an INSERT trigger. The INSERT trigger ensures that the spatial column cannot have duplicate values in the base table, while the DELETE-UPDATE trigger manages activity against the spatial column in the base, feature, and spatial index tables. These triggers are automatically dropped whenever a feature class, either stand-alone or in a feature dataset, is multiversioned. They are re-created when a feature class is unregistered as versioned. If you edit one of these triggers, then register the feature class as versioned, the trigger will be dropped. Maintaining the referential integrity between the base and feature table is important. You should not edit the records of either the feature table or the spatial index table. Several indexes and constraints have been added to the base, feature, and spatial index tables to ensure referential integrity is maintained. However, these indexes and constraints are removed when the feature class is converted to the load-only I/O mode, a state that allows for rapid insertion of data into the feature class.

    When the feature class is placed back into normal I/O mode—the state that allows users to query the feature class through ArcGIS—the indexes are created and the constraints are enabled. The conversion to normal I/O mode will fail if the unique indexes cannot be built on the base table's spatial column or the feature table's FID column. It will also fail if a value exists in the base table's spatial column that is not in the feature table's FID column. In this case, a reference to the offending base table record is loaded into the SDE_EXCEPTIONS table.

  • System tables associated with feature classes

    In the database, the GDB_ITEMS system table is the primary table that tracks datasets (such as feature classes and raster catalogs) in the geodatabase.

    View a diagram of a feature class using binary storage in SQL Server..

    NoteNote:

    You need Adobe Acrobat Reader to open the file.

    Dashed lines indicate implicit relationships between columns; solid lines indicate explicit relationships between columns.

    The database name fields in the GDB_ITEMS, SDE_table_registry, SDE_column_registry, and SDE_layers table all contain the same value, but to keep the diagram from being too cluttered, the implicit relationship between these fields is not shown. Similarly, the owner fields in all these tables also contain the same value, though the relationship is not drawn on the diagram.

Feature classes stored using the Microsoft geometry or geography type in a SQL Server database

In ArcGIS 9.3 and later releases, geodatabases stored in a SQL Server 2008 database can use the Microsoft geometry or geography storage types for feature storage. Feature classes that use one of these storage types also have a base table, as described in the previous section. However, for these storage types, the base table contains a column of type GEOMETRY or GEOGRAPHY, in which the feature class geometry is stored; there is no associated feature table.

Feature class tables that use geometry or geography storage also have associated system tables plus associated views.

View a diagram of a feature class using SQL Server geometry or geography storage..

NoteNote:

You need Adobe Acrobat Reader to open the file.

Dashed lines indicate implicit relationships between columns; solid lines indicate explicit relationships between columns.

The views used for the geometry or geography feature classes, ST_SPATIAL_REFERENCE_SYSTEMS and ST_GEOMETRY_COLUMNS, are not shown in the diagram because they are based on the system tables SDE_spatial_references and SDE_geometry_columns, which are already included in the diagram.

If the feature class is enabled to store CAD entities, a side table is created for the feature class to store CAD data, such as curves. All feature classes created through ArcGIS Desktop are set to store CAD entities. If data is imported or registered with ArcSDE using ArcSDE administration commands, you specify whether or not CAD data can be stored in the resultant feature class. The feature class diagram also shows a CAD table, SDE_geometry42. The 42 in the table name refers to the feature class's layer_id from the SDE_layers table.

Joins against the CAD side table are not enabled until CAD data, such as true curves, has been added to the feature class. Once enabled, queries against the feature class always include the CAD side table, even if the CAD data is no longer present.

Feature classes in an XML document:

Feature classes are defined within DataElement elements. The DataElement tags for a feature class are set to type esri:DEFeatureClass. Within the feature class DataElement are other elements that define the feature class, such as Field, Domain, ConfigurationKeyword, and SpatialReference elements.

The following is a small portion of the content of an XML document for the Parcels feature class:

<DataElement xsi:type="esri:DEFeatureClass">
  <CatalogPath>/V=dbo.DEFAULT/FC=geodata.SASHA.Parcels</CatalogPath>
  <Name>geodata.SASHA.Parcels</Name>
  <DatasetType>esriDTFeatureClass</DatasetType>
  <DSID>22</DSID>
  <Versioned>false</Versioned>
  <CanVersion>true</CanVersion>
  <ConfigurationKeyword/>
  <HasOID>true</HasOID>
  <OIDFieldName>objectid</OIDFieldName>
  <Fields xsi:type="esri:Fields">
    <FieldArray xsi:type="esri:ArrayOfField">
      <Field xsi:type="esri:Field">
        <Name>objectid</Name>
        <Type>esriFieldTypeOID</Type>
        <IsNullable>false</IsNullable>
        <Length>4</Length>
        <Precision>10</Precision>
        <Scale>0</Scale>
        <Required>true</Required>
        <Editable>false</Editable>
        <AliasName>OBJECTID</AliasName>
        <ModelName>OBJECTID</ModelName>
      </Field>

All other elements defining the feature class

</DataElement>

11/18/2013