Collect several geometric columns of a table into a GEOMETRYCOLLECTION : GeometryCollection « Geometric « SQL / MySQL

1. SQL / MySQL
2. Geometric
3. GeometryCollection

Collect several geometric columns of a table into a GEOMETRYCOLLECTION

    
mysql>
mysql>
mysql>
mysql>
mysql> CREATE TABLE mytable (
    ->     id INT NOT NULL PRIMARY KEY,
    ->     border POLYGON NOT NULL,
    ->     ela LINESTRING NOT NULL,
    ->     ref POINT NOT NULL,
    ->     name VARCHAR(20),
    ->     SPATIAL KEY(border),
    ->     SPATIAL KEY(ela),
    ->     SPATIAL KEY(ref)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO mytable VALUES ( 1,
    -> GEOMFROMTEXT('POLYGON(
    '> (25 15,10 25,25 35,30 50,30 60,
    '> 35 65,40 55,45 30,55 20,25 15),
    '> (3395 2827,3861 2840,4094 2374,3395 2594,3395 2827)
    '> )'),
    -> GEOMFROMTEXT('LINESTRING(25 35, 45 30)'),
    -> GEOMFROMTEXT('POINT(2850 2350)'), 'gl_1');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO mytable VALUES ( 2,
    -> GEOMFROMTEXT('POLYGON((70 20,55 20,65 30,
    '> 65 50,70 55,80 50,85 40,80 30,
    '> 85 25,70 20))'),
    -> GEOMFROMTEXT('LINESTRING(65 35, 80 30)'),
    -> GEOMFROMTEXT('POINT(7250 2750)'), 'gl_2');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT ASTEXT(GEOMFROMTEXT(
    -> CONCAT('GEOMETRYCOLLECTION(',
    -> ASTEXT(border),',', ASTEXT(ref), ')')))
    -> AS collection FROM mytable;
+-----------------------------------------------------------------------------------------------------------------------
------------------------------------------+
| collection
                                          |
+-----------------------------------------------------------------------------------------------------------------------
------------------------------------------+
| GEOMETRYCOLLECTION(POLYGON((25 15,10 25,25 35,30 50,30 60,35 65,40 55,45 30,55 20,25 15),(3395 2827,3861 2840,4094 237
4,3395 2594,3395 2827)),POINT(2850 2350)) |
| GEOMETRYCOLLECTION(POLYGON((70 20,55 20,65 30,65 50,70 55,80 50,85 40,80 30,85 25,70 20)),POINT(7250 2750))
                                          |
+-----------------------------------------------------------------------------------------------------------------------
------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
  

Related examples in the same category