(mysql.info) creating-spatial-indexes

(mysql.info) optimizing-spatial-analysis

(mysql.info) using-a-spatial-index

 
 16.6.1 Creating Spatial Indexes
 -------------------------------
 
 MySQL can create spatial indexes using syntax similar to that for
 creating regular indexes, but extended with the `SPATIAL' keyword.
 Currently, spatial columns that are indexed must be declared `NOT NULL'.
 The following examples demonstrate how to create spatial indexes:
 
    * With `CREATE TABLE':
 
           CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
 
    * With `ALTER TABLE':
 
           ALTER TABLE geom ADD SPATIAL INDEX(g);
 
    * With `CREATE INDEX':
 
           CREATE SPATIAL INDEX sp_index ON geom (g);
 
 For `MyISAM' tables, `SPATIAL INDEX' creates an R-tree index. For other
 storage engines that support spatial indexing, `SPATIAL INDEX' creates
 a B-tree index. A B-tree index on spatial values will be useful for
 exact-value lookups, but not for range scans.
 
 To drop spatial indexes, use `ALTER TABLE' or `DROP INDEX':
 
    * With `ALTER TABLE':
 
           ALTER TABLE geom DROP INDEX g;
 
    * With `DROP INDEX':
 
           DROP INDEX sp_index ON geom;
 
 Example: Suppose that a table `geom' contains more than 32,000
 geometries, which are stored in the column `g' of type `GEOMETRY'. The
 table also has an `AUTO_INCREMENT' column `fid' for storing object ID
 values.
 
      mysql> DESCRIBE geom;
      +-------+----------+------+-----+---------+----------------+
      | Field | Type     | Null | Key | Default | Extra          |
      +-------+----------+------+-----+---------+----------------+
      | fid   | int(11)  |      | PRI | NULL    | auto_increment |
      | g     | geometry |      |     |         |                |
      +-------+----------+------+-----+---------+----------------+
      2 rows in set (0.00 sec)
 
      mysql> SELECT COUNT(*) FROM geom;
      +----------+
      | count(*) |
      +----------+
      |    32376 |
      +----------+
      1 row in set (0.00 sec)
 
 To add a spatial index on the column `g', use this statement:
 
      mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
      Query OK, 32376 rows affected (4.05 sec)
      Records: 32376  Duplicates: 0  Warnings: 0

Info Catalog

(mysql.info) optimizing-spatial-analysis

(mysql.info) using-a-spatial-index

automatically generated byinfo2html