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 for the business tenant. After you connect to the server, run the following command to import the default_srs_data_mysql.sql file into the database:

## $password must be replaced with the password used to log in to the database.
## $localhost must be replaced with the IP address used to log in to the database.
## $tenant_name must be replaced with the tenant name used to log in to the database.
## $port must be replaced with the port number of the database.
$python /home/admin/oceanbase/bin/import_srs_data.py -p'$password' -h $localhost -P $port -t $tenant_name -f /home/admin/oceanbase/etc/default_srs_data_mysql.sql

The following output indicates that the data file is imported:

INFO: succeed to import srs data
INFO: oceanbase.__all_spatial_reference_systems old result rows -- 1
INFO: oceanbase.__all_spatial_reference_systems new result rows -- 5152

Examples

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:

-- Create a table with various geometry types
CREATE TABLE spatial_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    geom_point POINT,                    -- Point type
    geom_linestring LINESTRING not NULL SRID 4326,          -- Line type
    geom_polygon POLYGON,                -- Polygon type
    geom_multipoint MULTIPOINT,          -- Multi-point type
    geom_multilinestring MULTILINESTRING,-- Multi-line type
    geom_multipolygon MULTIPOLYGON,      -- Multi-polygon type
    geom_geometrycollection GEOMETRYCOLLECTION, -- Geometry collection type
    geom_geometry GEOMETRY               -- General geometry type, can store any geometry value.
);

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.