Create a spatial column

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

Prerequisites

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

## $password must be replaced with the password for logging in to the database.
## $localhost must be replaced with the IP address for logging in to the database.
## $tenant_name must be replaced with the tenant name for logging 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

If the following result is returned, 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 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. Here is an example:

CREATE TABLE geom (g GEOMETRY);

You can add or drop a spatial column from an existing table by using the ALTER TABLE statement. Here is an example:

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

The following example creates a table that contains various geometry types:

-- Create a table that contains 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 that can store any geometry value.
);

The following constraints apply to spatial columns:

• When you define a spatial column, you can explicitly specify the SRID. If no SRID is specified for a column, the optimizer does not use spatial indexes during queries. However, index records are still generated during inserts and updates.

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

• If you specify 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.

• All objects in the column must have the same SRID.