ArcGIS Server Banner

Creating a table with an ST_Geometry column

Creating a table with an ST_Geometry column
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note: This topic was updated for 9.3.1.

A spatial table is a table that includes one or more spatial columns. To create a spatial table, include a spatial column in the column clause of the CREATE TABLE statement. The following statements create two tables with an ST_Geometry column:

CREATE TABLE sensitive_areas (area_id integer, name varchar(128), 
area_size float, type varchar(10), zone st_geometry);

CREATE TABLE hazardous_sites (row_id integer NOT NULL, site_id integer, 
name varchar(40), location st_geometry);

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. ST_Geometry accepts geometries of any supported subtype.

In the example above, the tables created are designed to store data about sensitive areas and hazardous waste sites. In the sensitive_areas table, data about schools, hospitals, and playgrounds are stored, while the hazardous_sites table stores locations of hazardous waste sites. The ST_Geometry data type is used to store the location of sensitive areas (in a column named zone), while hazardous sites are stored as points using the ST_Geometry type in a column called location.

Object names

By default, database object names are stored in all uppercase in Oracle and all lowercase in PostgreSQL. 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.

Multiple spatial columns

It is also possible to create multiple ST_Geometry columns in the same table. However, ArcGIS does not support multiple geometry columns in the same table. ArcGIS can access tables with multiple type columns through spatial views that contain only one ST_Geometry column. For more information on spatial views, see Using spatial views on tables with an ST_Geometry column.

Unique row ID column

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 an ST_Geometry column to an existing table in an ArcSDE geodatabase, it may also add a unique identifying column. This column is often called 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(38) UNIQUE NOT NULL in Oracle or INTEGER UNIQUE NOT NULL in PostgreSQL.

For details on this column, see Registering tables to be used by ArcGIS Desktop.