Rules to follow when creating spatial tables to be used with a geodatabase

The following is a list of things to keep in mind when creating spatial tables with third-party tools that you will use with ArcGIS:

• Your table needs a unique, non-null integer column to be used as a unique identifier in ArcGIS.

  ArcSDE requires a registered, unique feature identifier column in the spatial table to perform spatial queries, log file queries, single-row operations, and multiversioned database operations. When you add a spatial column to an existing table in an ArcSDE geodatabase, it may also add a unique identifying column. This column name is often OBJECTID, but it can have another name.

  An existing column can also be used for the unique identifying column as long as it is indexed and declared as NUMBER or INTEGER, UNIQUE, and NOT NULL. See What is an ObjectID? for more information on this column.

• Avoid having multiple spatial columns in the same table.

  It is possible to create multiple spatial columns in the same table. However, ArcGIS does not support multiple geometry columns in the same table. To use tables that have multiple spatial columns, create a spatial view that includes only one of the spatial columns. See Using spatial views on tables with an ST_Geometry column for more information on creating a spatial view on tables that contain ST_Geometry columns.

• Do not use mixed-case object names.

  By default, database object names are stored in all uppercase in Oracle and DB2 and all lowercase in PostgreSQL and Informix. It is possible when creating tables using SQL to force the database to store mixed-case names. However, if you plan to use your data with ArcGIS, do not do this. The ArcGIS suite of software does not allow for case sensitivity. If you create mixed-case object names, queries executed using such applications as ArcSDE commands or ArcMap will fail. Similarly, if you create object names in all uppercase in a PostgreSQL database or object names in all lowercase in an Oracle database, ArcGIS queries will fail.

  SQL Server databases used with ArcGIS are case insensitive. Therefore, this rule is not relevant for SQL Server databases.

• The records you insert into a spatial table must have a type that matches the type defined for the spatial column.

  A spatial column can only accept data of the type required by the spatial column. For example, a column of ST_Polygon type rejects integers, characters, and even other types of geometry such as ST_LineString. However, ST_Geometry accepts geometries of any supported subtype.

  SQL Server spatial type columns are always either geometry or geography; there are no subtypes or metadata about the entity type. Therefore, this rule does not apply to SQL Server databases.

• Use only one spatial reference for the data in the table.

  The geodatabase applies one spatial reference to the entire table. When you register the table with ArcSDE, you specify which spatial reference to use, and you can only specify one. If you register the table with one spatial reference but some of the data is actually in a different spatial reference, data does not render properly and you may even receive an error message.