Create a spatial column

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

Prerequisites

Before you use the GIS features, you must 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 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.

The following result indicates that the data file is imported successfully:

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 run the following statement to create a table with a spatial column:

CREATE TABLE geom (g GEOMETRY);

You can run the following statement to add or drop a spatial column from an existing table:

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

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

-- 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, which can store any geometry value.
);

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 choose a spatial index during queries. However, 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 an SRID can use a spatial index.

• After you specify the SRID for a spatial column, inserting other SRID values will result in an error.

The following constraints apply to the SRID:

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

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