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:

Steps:
  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.

Related Topics

6/19/2015