|
SRID is not preserved in UNION:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a POINT REF_SYSTEM_ID=10);
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
|
SELECT G_TABLE_NAME,G_GEOMETRY_COLUMN,SRID FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS;
|
+--------------+-------------------+------+
|
| G_TABLE_NAME | G_GEOMETRY_COLUMN | SRID |
|
+--------------+-------------------+------+
|
| t1 | a | 10 |
|
| t2 | a | 0 |
|
+--------------+-------------------+------+
|
The expected SRID value is 10 for both columns.
The exact geometry type and SRID are not preserved when creating a table using UNION and VIEW:
DROP TABLE IF EXISTS t1;
|
DROP VIEW IF EXISTS v1;
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t1 (p POINT REF_SYSTEM_ID=10);
|
CREATE VIEW v1 AS SELECT p FROM t1;
|
CREATE TABLE t2 AS SELECT MIN(p) AS p FROM t1 UNION SELECT p FROM v1;
|
SHOW CREATE TABLE t2;
|
SELECT G_TABLE_NAME,G_GEOMETRY_COLUMN,SRID FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS;
|
+-------+----------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`p` geometry DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------+
|
+--------------+-------------------+------+
|
| G_TABLE_NAME | G_GEOMETRY_COLUMN | SRID |
|
+--------------+-------------------+------+
|
| t1 | p | 10 |
|
| t2 | p | 0 |
|
| v1 | p | 10 |
|
+--------------+-------------------+------+
|
The expected column type for t2.p would be POINT.
The expected SRID value would be 10 for all three columns.
|