Registering 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. To do this, use the sde.st_register_spatial_column function. The syntax for using this 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. 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:

Steps:

1. Open a command prompt or shell prompt and log in to psql. 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.

2. 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.

3. 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