Create a spatial column

OceanBase Database allows you to use CREATE TABLE or ALTER TABLE to create spatial columns.

The syntax of the CREATE TABLE statement for creating a table with spatial columns is as follows:

CREATE TABLE geom (g GEOMETRY);

The syntax of the ALTER TABLE statement for adding a spatial column to or dropping a spatial column from a table is as follows:

ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;

Here is an example:

obclient> CREATE TABLE geom (
    p POINT SRID 0,
    g GEOMETRY NOT NULL SRID 4326
);
Query OK, 0 rows affected

Limitations on creating spatial columns are as follows:

• When you define a spatial column, you can explicitly specify the SRID. If no SRID is defined for a column, the optimizer does not select spatial indexes for a query, but it still generates index records for INSERT and UPDATE operations.

• If you have specified the NOT NULL constraint and an SRID for a spatial column, you can define spatial indexes on the spatial column. In other words, only columns defined with SRIDs can use spatial indexes.

• If you have defined an SRID for a spatial column, an error will be reported when you insert other SRID values.

SRIDs are subject to the following limitations:

• The specified SRID is displayed on the spatial column.

• The SRID must be the same for all objects in a column.