Oracle 5.0, Reference Manual

Page: 1019 / 3232

Optimizing Spatial Analysis
999
• With
CREATE TABLE
:
CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=MyISAM;
• 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 storage engines that support
nonspatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial
values will be useful for exact-value lookups, but not for range scans.
For more information on indexing spatial columns, see Section 13.1.8, “
CREATE INDEX
Syntax” .
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
12.16.6.2. Using a Spatial Index
The optimizer investigates whether available spatial indexes can be involved in the search for queries
that use a function such as
MBRContains()
[996] or
MBRWithin()
[997] in the
WHERE
clause.
The following query finds all objects that are in the given rectangle:
mysql> SET @poly =
-> 'Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))';
mysql> SELECT fid,AsText(g) FROM geom WHERE
-> MBRContains(GeomFromText(@poly),g);
+-----+---------------------------------------------------------------+
| fid | AsText(g) |
+-----+---------------------------------------------------------------+