Register an ST_Geometry column

If you use SQL to create a table that contains an ST_Geometry column, you can register the column to use a specific spatial reference and dimensionality. That way, when you insert records through SQL, you cannot accidentally insert records that use a different spatial reference.

Register an ST_Geometry column in PostgreSQL

Use st_register_spatial_column to register the ST_Geometry column in a PostgreSQL table created with SQL. Syntax for the st_register_spatial_column function is as follows:

SELECT st_register_spatial_column (
 '<database_name>',
 '<schema_name>',
 '<table_name>',
 '<spatial_column_name>',
 <srid>,
 <coordinate_dimension>
);

The SRID you specify must exist in the public.sde_spatial_references table in PostgreSQL. The coordinate dimension indicates whether the data has only x,y coordinates (2), x,y,z coordinates (3), x,y,z,m coordinates (4), or x,y,m coordinates (5). By default, if you do not specify a coordinate dimension, the data is registered as having only x,y dimensions

The following steps provide an example of how to register an ST_Geometry column in PostgreSQL to use a specific SRID and dimensionality:

1. Open a command prompt or shell prompt.
2. Log in to an SQL editor, and connect to the database that contains the table for which you want to register the ST_Geometry column.

   In this example, user horace is connecting to database spatdat.

   psql spatdat horace

3. At the SQL prompt, call the st_register_spatial_column function to register the spatial column of a table to use a specific SRID and dimensionality.

   Here, the geo column of the waypoints table in schema cleo is registered with an SRID of 104199 and x,y,z dimensionality:

   SELECT sde.st_register_spatial_column(
    'spatdat',
    'cleo',
    'waypoints',
    'geo',
    104199,
    3
   );
   

   User horace must have at least SELECT privileges on the cleo.waypoints table to register the spatial column.

4. To confirm the table was registered properly, you can use the st_isregistered_spatial_column and st_get_coord_dimension functions to return the registered information.

   The st_isregistered_spatial_column function returns 1 (true) if the column is registered with the specified SRID.

   SELECT sde.st_isregistered_spatial_column(
    'spatdat',
    'cleo',
    'waypoints',
    'geo',
    104199
   );
   
   st_isregistered_spatial_column
   --------------------------------
   1
   

   The st_get_coord_dimension function returns which coordinate dimensions the table can store:

   SELECT sde.st_get_coord_dimension(
    'cleo',
    'waypoints',
    'geo',
    104199
   );
   
   st_get_coord_dimension
   --------------------------------
   xyz
   

Register an ST_Geometry column in SQLite

Use AddGeometryColumn to add an ST_Geometry column to a table in SQLite and register it with a specific SRID and coordinate dimension. The following is the syntax for AddGeometryColumn:

SELECT AddGeometryColumn (
 <'main'|null>,
 <table_name>,
 <spatial_column_name>,
 <srid>,
 <geometry_type>
 <coordinate_dimension>,
 <'null'|'not null'>
);

When you connect to SQLite through a SQL client, you are connecting to main. You can specify main or use null, which assumes you are connecting to main. Coordinate dimension is either xy (2), xyz (3), xyzm (4), or xyzm (5). If you specify a dimension of xy or 2, you do not have to include the dimension in the geometry type. If you specify any other coordinate dimensions, you must also include that information when you specify the geometry type. You can type the geometry type or the code for the geometry type. Possible values are as follows:

Geometry type values	Code
st_geometry or geometry	0
st_point or point	1
st_linestring or linestring	2
st_polygon or polygon	3
st_multipoint or multipoint	4
st_multilinestring or multilinestring	5
st_multipolygon or multipolygon	6
st_geometryz or geometryz	1000
st_pointz or pointz	1001
st_linestringz or linestringz	1002
st_polygonz or polygonz	1003
st_multipointz or multipointz	1004
st_multilinestringz or multilinestringz	1005
st_multipolygonz or multipolygonz	1006
st_geometrym or geometrym	2000
st_pointm or pointm	2001
st_linestringm or linestringm	2002
st_polygonm or polygonm	2003
st_multipointm or multipointm	2004
st_multilinestringm or multilinestringm	2005
st_multipolygonm or multipolygonm	2006
st_geometryzm or geometryzm	3000
st_pointzm or pointzm	3001
st_linestringzm or linestringzm	3002
st_polygonzm or polygonzm	3003
st_multipointzm or multipointzm	3004
st_multilinestringzm or linestringzm	3005
st_multipolygonzm or multipolygonzm	3006

See Create tables with an ST_Geometry column for an example of creating a table in SQLite and using AddGeometryColumn to add and register the ST_Geometry column.

Register an ST_Geometry column in Oracle

In Oracle, creating a spatial index on the ST_Geometry column registers the column to use a specific spatial reference. See Create spatial indexes on tables with an ST_Geometry column for an example of using SQL to create a spatial index in Oracle.