mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-12-20 03:05:51 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/innodb_gis/r/point_basic.result
Aleksey Midenkov a518e1dd42 MDEV-20865 Store foreign key info in TABLE_SHARE 
1. Access foreign keys via TABLE_SHARE::foreign_keys and
   TABLE_SHARE::referenced_keys;

   foreign_keys and referenced_keys are lists in TABLE_SHARE.

2. Remove handler FK interface:

   - get_foreign_key_list()
   - get_parent_foreign_key_list()
   - referenced_by_foreign_key()

3. Invalidate referenced shares on:

   - RENAME TABLE
   - DROP TABLE
   - RENAME COLUMN
   - ADD FOREIGN KEY

   When foreign table is created or altered by the above operations
   all referenced shares are closed. This blocks the operation while
   any referenced shares are used (when at least one its TABLE
   instance is locked).

4. Update referenced shares on:

   - CREATE TABLE

   On CREATE TABLE add items to referenced_keys of referenced
   shares. States of referenced shares are restored in case of errors.

5. Invalidate foreign shares on:

   - RENAME TABLE
   - RENAME COLUMN

   The above-mentioned blocking takes effect.

6. Check foreign/referenced shares consistency on:

   - CHECK TABLE

7. Temporary change until MDEV-21051:

   InnoDB fill foreign key info at handler open().

FOREIGN_KEY_INFO is refactored to FK_info holding Lex_cstring.

On first TABLE open FK_info is loaded from storage engine into
TABLE_SHARE. All referenced shares (if any exists) are closed. This
leads to blocking of first time foreign table open while referenced
tables are used.

MDEV-21311 Converge Foreign_key and supplemental generated Key together

mysql_prepare_create_table() does data validation and such utilities
as automatic name generation. But it does that only for indexes and
ignores Foreign_key objects. Now as Foreign_key data needs to be
stored in FRM files as well this processing must be done for it like
for any other Key objects.

Replace Key::FOREIGN_KEY type with Key::foreign flag of type
Key::MULTIPLE and Key::generated set to true. Construct one object
with Key::foreign == true instead of two objects of type
Key::FOREIGN_KEY and Key::MULTIPLE.

MDEV-21051 datadict refactorings

- Move read_extra2() to datadict.cc
- Refactored extra2_fields to Extra2_info
- build_frm_image() readability

MDEV-21051 build_table_shadow_filename() refactoring

mysql_prepare_alter_table() leaks fixes

MDEV-21051 amend system tables locking restriction

Table mysql.help_relation has foreign key to mysql.help_keyword. On
bootstrap when help_relation is opened, it preopens help_keyword for
READ and fails in lock_tables_check().

If system table is opened for write then fk references are opened for
write.

Related to: Bug#25422, WL#3984
Tests: main.lock

MDEV-21051 Store and read foreign key info into/from FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream
containing foreign key structures. Referenced tables store there only
hints about foreign tables (their db and name), they restore full info
from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in
extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign
keys. Until InnoDB storage of foreign keys is removed, FK names must
be unique across the database: the FK name must be based on table
name.

3. Keep stored data in sync on DDL changes. Referenced tables update
their foreign hints after following operations on foreign tables:

  - RENAME TABLE
  - DROP TABLE
  - CREATE TABLE
  - ADD FOREIGN KEY
  - DROP FOREIGN KEY

Foreign tables update their foreign info after following operations on
referenced tables:

  - RENAME TABLE
  - RENAME COLUMN

4. To achieve 3. there must be ability to rewrite extra2 section of
FRM file without full reparse. FRM binary is built from primary
structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table()
does. Shadow FRM is new FRM file that replaces the old one.

CREATE TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_create_table() translates them to FK_info, assigns
     foreign_id if needed;

  3. build_frm_image() writes two FK_info lists into FRM's extra2
     section, for referenced keys it stores only table names (hints);

  4. init_from_binary_frm_image() parses extra2 section and fills
     foreign_keys and referenced_keys of TABLE_SHARE.

     It restores referenced_keys by reading hint list of table names,
     opening corresponding shares and restoring FK_info from their
     foreign_keys. Hints resolution is done only when initializing
     non-temporary shares. Usually temporary share has different
     (temporary) name and it is impossible to resolve foreign keys by
     that name (as we identify them by both foreign and referenced
     table names). Another not unimportant reason is performance: this
     saves spare share acquisitions.

ALTER TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_alter_table() prepares action lists and share list
     of foreigns/references;

  3. mysql_prepare_alter_table() locks list of foreigns/references by
     MDL_INTENTION_EXCLUSIVE, acquires shares;

  4. prepare_create_table() converts key_list into FK_list, assigns
     foreign_id;

  5. shadow FRM of altered table is created;

  6. data is copied;

  7. altered table is locked by MDL_EXCLUSIVE;

  8. fk_handle_alter() processes action lists, creates FK backups,
     modifies shares, writes shadow FRMs;

  9. altered table is closed;

  10. shadow FRMs are installed;

  11. altered table is renamed, FRM backup deleted;

  12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups
      deleted;

On FK backup system:

In case of failed DDL operation all shares that was modified must be
restored into original state. This is done by FK_ddl_backup (CREATE,
DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).

On STL usage:

STL is used for utility not performance-critical algorithms, core
structures hold native List. A wrapper was made to convert STL
exception into bool error status or NULL value.

MDEV-20865 fk_check_consistency() in CHECK TABLE

Self-refs fix

Test table_flags fix: "debug" deviation is now gone.

FIXMEs: +16 -1
2025-09-02 13:24:36 +03:00

1602 lines
51 KiB
Text
Raw Permalink Blame History

#
# Test for basic POINT operation
#
CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(0 0)')),
(ST_PointFromText('POINT(10 -20)'), ST_PointFromText('POINT(10 -20)')),
(ST_PointFromText('POINT(3.1415926 3.535897)'), ST_PointFromText('POINT(-3.932626 -3.488272)')),
(ST_PointFromText('POINT(-111.9876 234.1357)'), ST_PointFromText('POINT(-957.1914 958.1919)'));
SELECT ST_X(p1), ST_Y(p2) FROM gis_point;
ST_X(p1) ST_Y(p2)
0 0
10 -20
3.1415926 -3.488272
-111.9876 958.1919
DROP TABLE gis_point;
#
# Test when POINT is not on any indexes
#
CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
(ST_PointFromText('POINT(105.34523342 103.18492302)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(200.32247328 101.86728201)')),
(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)'));
EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using temporary; Using filesort
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
ST_AsText(p1) ST_AsText(p2)
POINT(100.32374832 101.23741821) POINT(100.32374832 101.98527111)
POINT(100.32374832 101.23741821) POINT(100.32374832 101.23741821)
POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201)
POINT(105.34523342 103.18492302) POINT(100.32374832 101.23741821)
SELECT ST_AsText(p1) FROM gis_point WHERE p1 = p2;
ST_AsText(p1)
POINT(100.32374832 101.23741821)
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p1 = ST_PointFromText('POINT(100.32374832 101.23741821)');
ST_AsText(p1) ST_AsText(p2)
POINT(100.32374832 101.23741821) POINT(100.32374832 101.23741821)
POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201)
POINT(100.32374832 101.23741821) POINT(100.32374832 101.98527111)
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p2 = ST_PointFromText('POINT(200.32247328 101.86728201)');
ST_AsText(p1) ST_AsText(p2)
POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201)
DROP TABLE gis_point;
#
# Test some ALTER TABLE operations on POINT tables
#
CREATE TABLE t1 (
p POINT NOT NULL,
g GEOMETRY NOT NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 11)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 12)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 13)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 14)'));
SELECT ST_AsText(p), ST_AsText(g) FROM t1;
ST_AsText(p) ST_AsText(g)
POINT(0 1) POINT(10 11)
POINT(1 1) POINT(10 12)
POINT(1 0) POINT(10 13)
POINT(0 0) POINT(10 14)
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g');
name mtype prtype len
p 14 1535 12
g 14 1535 12
ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p);
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
name mtype prtype len
p 14 1535 12
g 14 1535 12
p1 14 1279 12
p2 14 1279 12
# NOT NULL POINT will use ''
SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = '';
Expect 4
0
SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 = '';
Expect 4
0
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
ST_AsText(p) ST_AsText(p1)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p2;
ST_AsText(p) ST_AsText(p1)
ALTER TABLE t1 DROP COLUMN p2;
# NULLABLE POINT will use NULL
ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2);
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
name mtype prtype len
p 14 1535 12
g 14 1535 12
p1 14 1279 12
p2 14 1279 12
SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL;
Expect 4
4
UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)');
UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)');
ALTER TABLE t1 DROP COLUMN p2;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
name mtype prtype len
p 14 1535 12
g 14 1535 12
p1 14 1279 12
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
ST_AsText(p) ST_AsText(p1)
INSERT INTO t1 VALUES (ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
SELECT ST_AsText(p1) FROM t1;
ST_AsText(p1)
POINT(10 20)
POINT(10 20)
POINT(10 20)
POINT(10 20)
POINT(10 19)
DELETE FROM t1 WHERE p1 = ST_PointFromText('POINT(10 19)');
SELECT ST_AsText(p1) FROM t1;
ST_AsText(p1)
POINT(10 20)
POINT(10 20)
POINT(10 20)
POINT(10 20)
# Add spatial keys on the table
ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
name mtype prtype len
p 14 1535 12
g 14 1535 12
p1 14 1279 12
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
ST_AsText(p) ST_AsText(p1)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
ST_AsText(p) ST_AsText(p1)
POINT(0 1) POINT(10 20)
POINT(1 1) POINT(10 20)
POINT(1 0) POINT(10 20)
POINT(0 0) POINT(10 20)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
ST_AsText(p) ST_AsText(p1)
POINT(0 1) POINT(10 20)
POINT(1 1) POINT(10 20)
POINT(1 0) POINT(10 20)
POINT(0 0) POINT(10 20)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
ST_AsText(p) ST_AsText(p1)
POINT(0 0) POINT(10 20)
# Drop spatial keys on the table
ALTER TABLE t1 DROP KEY p, DROP KEY p1;
ERROR 42000: Can't DROP INDEX `p1`; check that it exists
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
name mtype prtype len
p 14 1535 12
g 14 1535 12
p1 14 1279 12
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
ST_AsText(p) ST_AsText(p1)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
ST_AsText(p) ST_AsText(p1)
POINT(0 1) POINT(10 20)
POINT(1 1) POINT(10 20)
POINT(1 0) POINT(10 20)
POINT(0 0) POINT(10 20)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
ST_AsText(p) ST_AsText(p1)
POINT(0 1) POINT(10 20)
POINT(1 1) POINT(10 20)
POINT(1 0) POINT(10 20)
POINT(0 0) POINT(10 20)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
ST_AsText(p) ST_AsText(p1)
POINT(0 0) POINT(10 20)
TRUNCATE t1;
ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
name mtype prtype len
g 14 1535 12
ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
name mtype prtype len
g 14 1535 12
p 14 1279 12
p1 14 1279 12
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`g` geometry NOT NULL,
`p` point DEFAULT NULL,
`p1` point DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.6 0.6)'), ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 20)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.7 0.7)'), ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 21)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.8 0.8)'), ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 22)'));
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
ST_AsText(p) ST_AsText(p1)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
ST_AsText(p) ST_AsText(p1)
POINT(1 1) POINT(10 20)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
ST_AsText(p) ST_AsText(p1)
POINT(0 1) POINT(10 19)
POINT(1 1) POINT(10 20)
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
ST_AsText(p) ST_AsText(p1)
POINT(0 0) POINT(10 22)
ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
name mtype prtype len
g 14 1535 12
p1 14 1279 12
pp 14 1279 12
UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)');
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1;
ST_AsText(pp) ST_AsText(p1)
POINT(0 1) POINT(5 5)
POINT(1 1) POINT(5 5)
POINT(1 0) POINT(5 5)
POINT(0 0) POINT(5 5)
ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
name mtype prtype len
g 14 1535 12
p1 14 1279 12
pp 14 1279 12
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
ST_AsText(pp) ST_AsText(p1)
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
ST_AsText(pp) ST_AsText(p1)
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')) ORDER BY pp;
ST_AsText(pp) ST_AsText(p1)
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(pp, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')) ORDER BY pp;
ST_AsText(pp) ST_AsText(p1)
POINT(0 0) POINT(5 5)
DROP TABLE t1;
#
# Test when the POINT is on B-TREE
#
CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
SELECT ST_AsText(p) FROM gis_point;
ST_AsText(p)
POINT(10 10)
POINT(20 10)
POINT(20 20)
POINT(10 20)
SELECT ST_AsText(p) FROM gis_point WHERE p = ST_PointFromText('POINT(20 20)');
ST_AsText(p)
POINT(20 20)
INSERT INTO gis_point VALUES
(201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
(202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
(203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
(204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
(205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
(206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
'The ORDER BY will use filesort'
EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using filesort
SELECT ST_AsText(p) FROM gis_point ORDER BY p;
ST_AsText(p)
POINT(10 10)
POINT(10 20)
POINT(20 10)
POINT(20 20)
POINT(101.43284962 100.45892392)
POINT(101.43284962 100.45892392)
POINT(100.32374832 101.23741821)
POINT(102.43287328 100.23489233)
POINT(103.4371864 105.248206478)
POINT(103.4371864 105.248206478)
SELECT ST_AsText(p), COUNT(*) FROM gis_point GROUP BY p;
ST_AsText(p) COUNT(*)
POINT(10 10) 1
POINT(10 20) 1
POINT(20 10) 1
POINT(20 20) 1
POINT(101.43284962 100.45892392) 2
POINT(100.32374832 101.23741821) 1
POINT(102.43287328 100.23489233) 1
POINT(103.4371864 105.248206478) 2
TRUNCATE gis_point;
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
# Check if we can create prefix index on POINT
ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
INSERT INTO gis_point VALUES
(105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
(106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
SELECT ST_AsText(p) FROM gis_point;
ST_AsText(p)
POINT(10 10)
POINT(20 10)
POINT(20 20)
POINT(10 20)
POINT(25 15)
POINT(25 25)
SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
ST_AsText(p) ST_AsText(g)
POINT(10 10) NULL
POINT(20 10) NULL
POINT(20 20) NULL
POINT(10 20) NULL
UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
ST_AsText(g)
POINT(200 200)
POINT(200 200)
POINT(200 200)
POINT(200 200)
POINT(200 200)
# Check the information schema tables
SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g');
table_name column_name data_type column_type
gis_point p point point
gis_point g point point
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name LIKE 'test/g%' AND c.name IN ('p','g');
name mtype prtype len
p 14 1279 12
g 14 1279 12
SELECT length(p) FROM gis_point;
length(p)
25
25
25
25
25
25
ALTER TABLE gis_point DROP COLUMN g, ALGORITHM = COPY;
TRUNCATE gis_point;
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
# Check if we can create prefix index on POINT
ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
INSERT INTO gis_point VALUES
(105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
(106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
SELECT ST_AsText(p) FROM gis_point;
ST_AsText(p)
POINT(10 10)
POINT(20 10)
POINT(20 20)
POINT(10 20)
POINT(25 15)
POINT(25 25)
SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
ST_AsText(p) ST_AsText(g)
POINT(10 10) NULL
POINT(20 10) NULL
POINT(20 20) NULL
POINT(10 20) NULL
UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
ST_AsText(g)
POINT(200 200)
POINT(200 200)
POINT(200 200)
POINT(200 200)
POINT(200 200)
CHECK TABLE gis_point;
Table Op Msg_type Msg_text
test.gis_point check status OK
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/gis_point' AND c.name IN ('p','g');
name mtype prtype len
p 14 1279 12
g 14 1279 12
DROP TABLE gis_point;
#
# Check the mtype of other geometry data types should be 15
#
CREATE TABLE g (
geom GEOMETRY NOT NULL,
l LINESTRING NOT NULL,
poly POLYGON NOT NULL,
mp MULTIPOINT NOT NULL,
ml MULTILINESTRING NOT NULL,
mpoly MULTIPOLYGON NOT NULL,
gc GEOMETRYCOLLECTION NOT NULL
) ENGINE=InnoDB;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/g';
name mtype prtype len
geom 14 1535 12
l 14 1535 12
poly 14 1535 12
mp 14 1535 12
ml 14 1535 12
mpoly 14 1535 12
gc 14 1535 12
DROP TABLE g;
#
# check the mtype and len with CREATE TABLE AS
#
CREATE TABLE t1 (
p POINT NOT NULL,
g GEOMETRY NOT NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
SELECT t.name, c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name LIKE 'test/t%' AND c.name IN ('p','g');
name name mtype prtype len
test/t1 p 14 1535 12
test/t1 g 14 1535 12
test/t2 p 14 1535 12
test/t2 g 14 1535 12
DROP TABLE t1,t2;
#
# Test when POINT is used in spatial index
#
SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))');
SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
SET @p1 = ST_PointFromText('POINT(0 0)');
SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))');
CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
(ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
(ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
(ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
(ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
(ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
(ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
(ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
(ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
(ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
(ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
(ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
(ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
CREATE TABLE gis_point1 SELECT * FROM gis_point;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/gis_point' AND c.name IN ('p1','p2');
name mtype prtype len
p1 14 1535 12
p2 14 1535 12
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1;
ST_AsText(p1) ST_AsText(p2)
POINT(1 2) POINT(-1 -3)
POINT(2 4) POINT(-2 -6)
POINT(3 6) POINT(-3 -9)
POINT(4 8) POINT(-4 -12)
POINT(5 10) POINT(-5 -15)
POINT(6 12) POINT(-6 -18)
POINT(7 14) POINT(-7 -21)
POINT(8 16) POINT(0 0)
POINT(9 18) POINT(-4 2)
POINT(10 21) POINT(-6 3)
POINT(20.5 41) POINT(-8 4)
POINT(26.25 57) POINT(1 2)
POINT(32.1234 64.2468) POINT(-1 -1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1;
ST_AsText(p1)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Contains(@poly2, p2);
ST_AsText(p2)
POINT(-1 -3)
POINT(-2 -6)
POINT(-3 -9)
POINT(-4 -12)
POINT(-5 -15)
POINT(0 0)
POINT(-1 -1)
SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Equals(p2, @p1);
ST_AsText(p2)
POINT(0 0)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
ST_AsText(p1)
POINT(20.5 41)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(10 21)
POINT(20.5 41)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
ST_AsText(p1)
POINT(3 6)
# Check functions that use MBR, with line type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
ST_AsText(p1)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls3);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @ls1);
ST_AsText(p1)
# Check functions that use MBR, with polygon type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
ST_AsText(p1)
POINT(1 2)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
ST_AsText(p1)
# Check functions that use MBR, with point type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @p1);
ST_AsText(p1)
# Check with MBR functions point type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
# Check with MBR functions point polygon data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
# Check with MBR functions polygon type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
ST_AsText(p1)
POINT(1 2)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @poly1);
ST_AsText(p1)
# Check with MBR functions line type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
ST_AsText(p1)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @ls1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
ST_AsText(p1)
DROP TABLE gis_point1;
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point;
ST_AsText(p1) ST_AsText(p2)
POINT(1 2) POINT(-1 -3)
POINT(2 4) POINT(-2 -6)
POINT(3 6) POINT(-3 -9)
POINT(4 8) POINT(-4 -12)
POINT(5 10) POINT(-5 -15)
POINT(6 12) POINT(-6 -18)
POINT(7 14) POINT(-7 -21)
POINT(8 16) POINT(0 0)
POINT(9 18) POINT(-4 2)
POINT(10 21) POINT(-6 3)
POINT(20.5 41) POINT(-8 4)
POINT(26.25 57) POINT(1 2)
POINT(32.1234 64.2468) POINT(-1 -1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1;
ST_AsText(p1)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2);
ST_AsText(p2)
POINT(-1 -3)
POINT(-2 -6)
POINT(-3 -9)
POINT(-4 -12)
POINT(-5 -15)
POINT(0 0)
POINT(-1 -1)
SELECT ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1);
ST_AsText(p2)
POINT(0 0)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
ST_AsText(p1)
POINT(20.5 41)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(10 21)
POINT(20.5 41)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
ST_AsText(p1)
POINT(3 6)
# Check functions that use MBR, with line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
ST_AsText(p1)
# Check functions that use MBR, with polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
POINT(9 18)
POINT(8 16)
POINT(7 14)
POINT(6 12)
POINT(1 2)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
ST_AsText(p1)
# Check functions that use MBR, with point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
POINT(9 18)
POINT(8 16)
POINT(7 14)
POINT(6 12)
POINT(5 10)
POINT(4 8)
POINT(3 6)
POINT(2 4)
POINT(1 2)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
ST_AsText(p1)
# Check with MBR functions point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
POINT(9 18)
POINT(8 16)
POINT(7 14)
POINT(6 12)
POINT(5 10)
POINT(4 8)
POINT(3 6)
POINT(2 4)
POINT(1 2)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
# Check with MBR functions point polygon data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
POINT(9 18)
POINT(8 16)
POINT(7 14)
POINT(6 12)
POINT(5 10)
POINT(4 8)
POINT(3 6)
POINT(2 4)
POINT(1 2)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
# Check with MBR functions polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
POINT(9 18)
POINT(8 16)
POINT(7 14)
POINT(6 12)
POINT(1 2)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
ST_AsText(p1)
# Check with MBR functions line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
ST_AsText(p1)
POINT(32.1234 64.2468)
POINT(26.25 57)
POINT(20.5 41)
POINT(10 21)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
ST_AsText(p1)
# Modify the storage engine to Myisam, Check the spatial functions
ALTER TABLE gis_point ENGINE Myisam;
# Check functions that use MBR, with line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
ST_AsText(p1)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
ST_AsText(p1)
# Check functions that use MBR, with polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
ST_AsText(p1)
POINT(1 2)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
ST_AsText(p1)
# Check functions that use MBR, with point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
ST_AsText(p1)
# Check with MBR functions point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
# Check with MBR functions point polygon data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
ST_AsText(p1)
# Check with MBR functions polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
ST_AsText(p1)
POINT(3 6)
POINT(4 8)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
ST_AsText(p1)
POINT(2 4)
POINT(5 10)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
ST_AsText(p1)
POINT(1 2)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
ST_AsText(p1)
# Check with MBR functions line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
ST_AsText(p1)
POINT(1 2)
POINT(2 4)
POINT(3 6)
POINT(4 8)
POINT(5 10)
POINT(6 12)
POINT(7 14)
POINT(8 16)
POINT(9 18)
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
ST_AsText(p1)
POINT(10 21)
POINT(20.5 41)
POINT(26.25 57)
POINT(32.1234 64.2468)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
ST_AsText(p1)
# No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
ST_AsText(p1)
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
ST_AsText(p1)
# Modify the storage engine to InnoDB again, do following testing
ALTER TABLE gis_point ENGINE InnoDB;
CHECK TABLE gis_point;
Table Op Msg_type Msg_text
test.gis_point check status OK
The ORDER BY for spatial index will use filesort
EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using filesort
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
ST_AsText(p1) ST_AsText(p2)
POINT(2 4) POINT(-2 -6)
POINT(3 6) POINT(-3 -9)
POINT(4 8) POINT(-4 -12)
POINT(5 10) POINT(-5 -15)
POINT(6 12) POINT(-6 -18)
POINT(7 14) POINT(-7 -21)
POINT(8 16) POINT(0 0)
POINT(9 18) POINT(-4 2)
POINT(10 21) POINT(-6 3)
POINT(1 2) POINT(-1 -3)
POINT(26.25 57) POINT(1 2)
POINT(20.5 41) POINT(-8 4)
POINT(32.1234 64.2468) POINT(-1 -1)
Try to do IDU on the table and verify the result
DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
Expect (32.1234 64.2468)
POINT(32.1234 64.2468)
UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
Expect (32.1234 64.2468) AND (20.5 41)
POINT(32.1234 64.2468)
POINT(20.5 41)
CHECK TABLE gis_point;
Table Op Msg_type Msg_text
test.gis_point check status OK
Use a trx to test the IDU on the table and verify the result
START TRANSACTION;
DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
Expect (32.1234 64.2468)
POINT(32.1234 64.2468)
UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
Expect (32.1234 64.2468) AND (20.5 49)
POINT(32.1234 64.2468)
POINT(20.5 49)
ROLLBACK;
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
Expect (32.1234 64.2468) AND (20.5 41)
POINT(32.1234 64.2468)
POINT(20.5 41)
CHECK TABLE gis_point;
Table Op Msg_type Msg_text
test.gis_point check status OK
DROP TABLE gis_point;
#
# Test inserting/updating different type data into POINT field
#
CREATE TABLE gis_point (i INT, p POINT) ENGINE=InnoDB;
CREATE TABLE geom (i INT, g GEOMETRY NOT NULL, SPATIAL KEY(g)) ENGINE=InnoDB;
INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)'));
INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)'));
INSERT INTO gis_point VALUES(2, NULL);
ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY;
SELECT t.name, c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/gis_point' AND c.name='p';
name name mtype prtype len
test/gis_point p 14 1279 12
SELECT i, ST_AsText(p) FROM gis_point;
i ST_AsText(p)
0 POINT(1 1)
1 POINT(2 2)
2 NULL
UPDATE gis_point SET p = NULL WHERE p = ST_PointFromText('POINT(1 1)');
UPDATE gis_point SET p = ST_PointFromText('POINT(1 2)') WHERE p = ST_PointFromText('POINT(2 2)');
UPDATE gis_point SET p = ST_PointFromText('POINT(1 1)') WHERE p IS NULL;
SELECT i, ST_AsText(p) FROM gis_point;
i ST_AsText(p)
0 POINT(1 1)
1 POINT(1 2)
2 POINT(1 1)
INSERT INTO geom VALUES(0, ST_PointFromText('POINT(0 0)'));
INSERT INTO geom VALUES(1, ST_PointFromText('POINT(10 10)'));
INSERT INTO geom VALUES(2, ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'));
SELECT ST_AsText(g) FROM geom;
ST_AsText(g)
POINT(0 0)
POINT(10 10)
POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
SELECT ST_AsText(p) FROM gis_point;
ST_AsText(p)
POINT(1 1)
POINT(1 2)
POINT(1 1)
DELETE FROM geom WHERE g = ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
From GEOMETRY to POINT, now ALL the data are POINT
ALTER TABLE geom MODIFY g POINT NOT NULL;
SHOW CREATE TABLE geom;
Table Create Table
geom CREATE TABLE `geom` (
`i` int(11) DEFAULT NULL,
`g` point NOT NULL,
SPATIAL KEY `g` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT ST_AsText(g) FROM geom;
ST_AsText(g)
POINT(0 0)
POINT(10 10)
From POINT to GEOMETRY, all data are POINT
ALTER TABLE geom MODIFY g GEOMETRY NOT NULL;
SHOW CREATE TABLE geom;
Table Create Table
geom CREATE TABLE `geom` (
`i` int(11) DEFAULT NULL,
`g` geometry NOT NULL,
SPATIAL KEY `g` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
TRUNCATE TABLE geom;
From GEOMETRY to POINT, the table is empty
ALTER TABLE geom MODIFY g POINT NOT NULL;
SHOW CREATE TABLE geom;
Table Create Table
geom CREATE TABLE `geom` (
`i` int(11) DEFAULT NULL,
`g` point NOT NULL,
SPATIAL KEY `g` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT ST_AsText(g) FROM geom;
ST_AsText(g)
CHECK TABLE gis_point;
Table Op Msg_type Msg_text
test.gis_point check status OK
CHECK TABLE geom;
Table Op Msg_type Msg_text
test.geom check status OK
DROP TABLE gis_point, geom;
#
# Test when a geom field, such as POINT, is also a primary key
#
CREATE TABLE t1 (
a INT NOT NULL,
p POINT NOT NULL,
l LINESTRING NOT NULL,
g GEOMETRY NOT NULL,
PRIMARY KEY(p),
SPATIAL KEY `idx2` (p),
SPATIAL KEY `idx3` (l),
SPATIAL KEY `idx4` (g)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(
1, ST_GeomFromText('POINT(10 10)'),
ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'),
ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))'));
INSERT INTO t1 VALUES(
2, ST_GeomFromText('POINT(20 20)'),
ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POINT(20 20) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 27 NULL # Using where
UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY(a);
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY(p);
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`p` point NOT NULL,
`l` linestring NOT NULL,
`g` geometry NOT NULL,
PRIMARY KEY (`p`(25)),
SPATIAL KEY `idx2` (`p`),
SPATIAL KEY `idx3` (`l`),
SPATIAL KEY `idx4` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY(p);
EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY,idx2 PRIMARY 27 const #
SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
a ST_AsText(p)
2 POINT(30 30)
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
a ST_AsText(p) ST_AsText(l) ST_AsText(g)
1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1;
#
# Test for foreign keys.
#
CREATE TABLE parent(p POINT, PRIMARY KEY(p)) ENGINE=InnoDB;
CREATE TABLE child(p POINT NOT NULL) ENGINE=InnoDB;
ALTER TABLE parent ADD SPATIAL INDEX idx1(p);
ALTER TABLE child ADD SPATIAL INDEX idx2(p);
SHOW CREATE TABLE parent;
Table Create Table
parent CREATE TABLE `parent` (
`p` point NOT NULL,
PRIMARY KEY (`p`(25)),
SPATIAL KEY `idx1` (`p`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SHOW CREATE TABLE child;
Table Create Table
child CREATE TABLE `child` (
`p` point NOT NULL,
SPATIAL KEY `idx2` (`p`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
ERROR 42000: Incorrect foreign key definition for 'p': foreign key by blob or geometry is not supported
show warnings;
Level Code Message
Error 1239 Incorrect foreign key definition for 'p': foreign key by blob or geometry is not supported
ALTER TABLE parent DROP INDEX idx1;
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
ERROR 42000: Incorrect foreign key definition for 'p': foreign key by blob or geometry is not supported
show warnings;
Level Code Message
Error 1239 Incorrect foreign key definition for 'p': foreign key by blob or geometry is not supported
ALTER TABLE child DROP INDEX idx2;
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
ERROR 42000: Incorrect foreign key definition for 'p': foreign key by blob or geometry is not supported
show warnings;
Level Code Message
Error 1239 Incorrect foreign key definition for 'p': foreign key by blob or geometry is not supported
DROP TABLE child, parent;
#
# Bug#28763: Selecting geometry fields in UNION caused server crash.
#
CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB;
SELECT f1 FROM t1 UNION SELECT f1 FROM t1;
f1
INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'),
ST_GeomFromText('POINT(2 2)'));
SELECT ST_AsText(f2),ST_AsText(f3) FROM t1;
ST_AsText(f2) ST_AsText(f3)
POINT(1 1) POINT(2 2)
SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t;
ST_AsText(a)
POINT(1 1)
POINT(2 2)
CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1;
DESC t2;
Field Type Null Key Default Extra
a geometry YES NULL
SELECT ST_AsText(a) FROM t2;
ST_AsText(a)
POINT(1 1)
POINT(2 2)
DROP TABLE t1, t2;
Reference in a new issue View git blame Copy permalink