ALTER TABLE - ADD clause for SPATIAL indexes

This ADD clause of the MySQL statement ALTER TABLE is used to add a SPATIAL index to a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
ADD SPATIAL [INDEX|KEY] [index] (column,...)

Explanation

This ADD clause the MySQL statement, ALTER TABLE is used to add a SPATIAL index. A SPATIAL index can indexonly spatial columns. A spatial index is used in a table that holds data based on the Open Geospatial Consortium (http://www.opengis.org) data for geographical and global positioning satelite (GPS) systems. For our purposes here, this clause is necessary to add an index for spatial extensions. For MyISAM tables, the RTREE index type is used. The BTREE is used by other storage engines that use non-spatial indexes of spatial columns.

Examples

Below is an example of this MySQL statement:

ALTER TABLE squares
ADD SPATIAL INDEX square_index (square_points);

SHOW INDEXES FROM squares \G

*************************** 1. row ***************************
       Table: squares
  Non_unique: 1
    Key_name: square_index
Seq_in_index: 1
 Column_name: square_points
   Collation: A
 Cardinality: NULL
    Sub_part: 32
      Packed: NULL
        Null: 
  Index_type: SPATIAL
     Comment:

Notice that when we created the MySQL table, we specified that the column square_points is NOT NULL. This is required to be able to index the column. See the CREATE INDEX - Creating SPATIAL indexes statement in our MySQL documentation for an explanation and more examples related to spatial indexes.