CREATE INDEX - Creating SPATIAL indexes

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).

CREATE SPATIAL INDEX index
ON table (column, . . .)

Explanation

SPATIAL indexes can index spatial columns only in MyISAM tables. This is available starting with version 4.1 of MySQL.

Examples

Below is an example in which a table is created and then an index:

CREATE TABLE squares 
(square_id INT, square_name VARCHAR(100), 
square_points POLYGON NOT NULL);

CREATE SPATIAL INDEX square_index 
ON squares (square_points);

Notice that when we created the table we specified that the column square_points is NOT NULL. This is required to be able to index the column. Let's insert two rows of data and run a SELECT statement:

INSERT INTO squares 
VALUES(1000, 'Red Square', 
(GeomFromText('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)))')) ),
(1000, 'Green Square', 
(GeomFromText('MULTIPOLYGON(((3 3, 3 5, 5 5, 4 3, 3 3)))')) );

Here we've added two squares by giving the five points of the polygon: the starting point (e.g, for the first row, x=0, y=0), the left top point (x=0, y=3), the right top point (x=3, y=3), the right bottom point (x=3, y=0), and the ending point (x=0, y=0) for good measure, which is the same as the starting point. So, the first row contains a square which is 3 by 3 in size and the second 2 by 2 in size. Using the AREA() function we can find the area of each:

SELECT square_name AS 'Square',
AREA(square_points) AS 'Area of Square'
FROM squares;

+--------------+----------------+
| Square       | Area of Square |
+--------------+----------------+
| Red Square   |              9 | 
| Green Square |              3 | 
+--------------+----------------+

If we want to find which square contains a given point on a cartesian plane (e.g., x=1, y=2), we can use the MBRContains() function like so:

SELECT square_name
FROM squares
WHERE 
MBRContains(square_points, GeomFromText('POINT(1 2)'));

+-------------+
| square_name |
+-------------+
| Red Square  | 
+-------------+

Too see how the index we added is involved, we would run an EXPLAIN statement using the same SELECT statement:

EXPLAIN SELECT square_name
FROM squares
WHERE 
MBRContains(square_points, GeomFromText('POINT(1 2)')) G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
         type: range
possible_keys: square_index
          key: square_index
      key_len: 32
          ref: NULL
         rows: 1
        Extra: Using where

Notice that the SQL statement is using the square_index spatial index that we created.