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
	Line
	Polygon
	Annotation
	Dimension
	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.

Note:

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

  Note:

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

Note:

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>