Version: V1.0.0

Create a spatial column

You can use the CREATE TABLE or ALTER TABLE statement to create a spatial column.

To create a table with a spatial column, use the CREATE TABLE statement. The syntax is as follows:

CREATE TABLE geom (g GEOMETRY);

To add or drop a spatial column from an existing table, use the ALTER TABLE statement. The syntax is as follows:

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

Here is an example:

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:

When you define a spatial column, you can explicitly specify the SRID. If the SRID is not specified for a column, the optimizer does not use the spatial index during queries. However, the index records are still generated during inserts and updates.
After you define a spatial column with the NOT NULL constraint and specify the SRID, you can define a spatial index on the column. In other words, only columns with an SRID can use a spatial index.
If you define an SRID for a spatial column, inserting a value with a different SRID will result in an error.

The following constraints apply to the SRID: