The geometry_columns table
When you create a table with a spatial column, or add a spatial column to an existing table, ESRI client software also requires an entry to the geometry_columns table. The geometry_columns table is a metadata table that stores the association between the spatial table and a spatial reference system.
The loadshp utility and the ESRI shp2sde command automatically create the entries in the geometry_columns table when you load data.
To update the geometry_columns table manually, insert a new row. The row must contain values for the following columns:
- f_table_catalog: The database name
- f_table_schema: The database owner name
- f_table_name: The table name
- f_geometry_column: The name of the spatial column
- geometry_type: The ID of the geometry type
- srid: The ID of the spatial reference system
The following table shows valid entries for the geometry_type column
and the shapes they represent.
| Geometry type ID | Spatial data type |
|---|---|
| 0 | ST_Geometry |
| 1 | ST_Point |
| 2 | ST_Curve |
| 3 | ST_LineString |
| 4 | ST_Surface |
| 5 | ST_Polygon |
| 6 | ST_GeomCollection |
| 7 | ST_MultiPoint |
| 8 | ST_MultiCurve |
| 9 | ST_MultiLineString |
| 10 | ST_MultiSurface |
| 11 | ST_MultiPolygon |
Example
For the hazardous sites and sensitive
areas example, the INSERT statements for the zone and location columns are:
INSERT INTO geometry_columns
(f_table_catalog, f_table_schema, f_table_name,
f_geometry_column, geometry_type, srid)
VALUES ("mydatabase", -- database name
"ralph", -- user name
"sensitive_areas", -- table name
"zone", -- spatial column name
5, -- column type (5 = polygon)
5); -- srid
INSERT INTO geometry_columns
(f_table_catalog, f_table_schema, f_table_name,
f_geometry_column, geometry_type, srid)
VALUES ("mydatabase", -- database name
"ralph", -- user name
"hazardous_sites", -- table name
"location", -- spatial column name
1, -- column type (1 = point)
5); -- srid