This topic describes how to create a spatial column in OceanBase AI Database.
Prerequisites
Before using GIS features, you must configure GIS metadata in the business tenant. After connecting to the server, execute the following command to import the default_srs_data_mysql.sql file into the database:
Notice
This import command can only be executed in the SYS tenant. When it is executed, data is written to the primary tenant and synchronized to the standby tenants.
-- 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 statement syntax, see LOAD MODULE DATA.
The following results indicate a successful import of the data file:
Query OK, 0 rows affected
Examples
OceanBase AI Database supports creating spatial columns using the CREATE TABLE or ALTER TABLE statement.
You can execute the CREATE TABLE statement to create a table with spatial columns. Syntax example:
CREATE TABLE geom (g GEOMETRY);
You can execute the ALTER TABLE statement to add or drop a spatial column in an existing table. Syntax example:
ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;
The following example shows how to create a table with various geometric types:
-- Create a table with various geometric 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, -- Multipoint type
geom_multilinestring MULTILINESTRING,-- Multiline type
geom_multipolygon MULTIPOLYGON, -- Multipolygon type
geom_geometrycollection GEOMETRYCOLLECTION, -- geometry collection type
geom_geometry GEOMETRY -- A general geometry type that can store any geometric value.
);
The constraints on creating a spatial column are as follows:
When defining a spatial column, you can explicitly specify the SRID. If no SRID is defined for the column, the optimizer will not use the spatial index during queries. However, index records are still generated during inserts and updates.
After a spatial column is specified with the
NOT NULLconstraint and an SRID, you can define a spatial index on it. In other words, only columns with an assigned SRID can use a spatial index.After defining an SRID for a spatial column, inserting other SRID values will result in an error.
The following table describes the constraints on the SRID column.
The
SRIDmust be explicitly specified for the spatial column.The
SRIDof all objects in the column must be the same.