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 must have a unique, non-null column to be used as a unique identifier in ArcGIS.
ArcGIS requires a unique feature identifier column in the spatial table to perform spatial queries, log file queries, single-row operations, and versioned database operations.
If you want to register the table with the geodatabase, ArcGIS will add a unique identifying column called an ObjectID. This column is usually named OBJECTID, but it can have another name if a column with that name already exists in the table. If a unique, not null identifying number or integer column that is indexed exists in the table, you will be given the option to use it as the ObjectID. The geodatabase will maintain the values in the ObjectID column of all tables registered with the geodatabase.
If you want to view a spatial database table in ArcGIS Desktop, you can specify a column or multiple columns that, together, make a unique identifier for the table.
If you want to edit spatial database tables through a feature service and you don't intend to register the table with a geodatabase, the table must contain a unique, not null column that is maintained by the database. You can use the Add Incrementing ID Field geoprocessing tool to add a qualifying field to your table.
- 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, you can create a view that includes only one of the spatial columns. Alternatively, you can define a query layer that includes only one of the spatial 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 case sensitivity. If you create mixed-case object names, queries executed using ArcGIS clients 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.
- The records you insert into a spatial table must have an entity type that matches the type defined for the spatial column.
A spatial column can only accept data of the entity type required by the spatial column. For example, a column of ST_Polygon type rejects other types of geometry such as ST_LineString.
However, ST_Geometry accepts geometries of any supported subtype. Although you can insert different entities into an ST_Geometry column, ArcGIS will only work with one. When viewing data from feature classes in a database, ArcGIS reads the first row in the table and only displays that entity type. To see other entity types in the table, you can alter the query layer definition for the table to show a different entity type. If you register the spatial table with the geodatabase, you must choose one entity type for the table.
- Use only one spatial reference for the data in the table.
ArcGIS applies one spatial reference to the entire table. When viewing database feature classes, you must specify one spatial reference, and only data in that spatial reference will render. When you register the table with the geodatabase, you specify which spatial reference to use, and you can specify only 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 receive an error message.