Create a spatial column

This topic describes how to create a spatial column in OceanBase Database.

Prerequisites

Before you use GIS features, you must configure GIS meta data in the business tenant. After you connect to the server, execute the following command to import the default_srs_data_mysql.sql file into the database:

-- module specifies the module to be imported.
-- tenant specifies the tenant to be imported.
-- infile specifies the relative path of the SQL file to be imported.
ALTER SYSTEM LOAD MODULE DATA module=gis tenant=mysql infile = 'etc/default_srs_data_mysql.sql';

For more information about the syntax of this statement, see LOAD MODULE DATA.

If the following result is returned, the data file is imported:

Query OK, 0 rows affected

Examples

OceanBase Database allows you to create a spatial column by using the CREATE TABLE or ALTER TABLE statement.

You can create a table with a spatial column by using the CREATE TABLE statement. Example:

CREATE TABLE geom (g GEOMETRY);

You can add or drop a spatial column in an existing table by using the ALTER TABLE statement. Example:

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

The following example shows how to create a table with various geometry types:

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

The following constraints apply to spatial columns:

• You can explicitly specify the SRID when you define a spatial column. If the SRID is not specified, the optimizer does not select a spatial index during queries. However, the index records are still generated during inserts and updates.

• After you specify the NOT NULL constraint and the SRID for a spatial column, you can define a spatial index on the column. In other words, only columns with the SRID can use spatial indexes.

• If you define the SRID for a spatial column, an error is returned when you insert a value with a different SRID.

The following constraints apply to the SRID:

• The SRID must be explicitly specified for a spatial column.

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