[GIS] MySQL select linestrings into a multilinestring

Question

I have a mysql column of linestring type with 2000 linestrings (high water mark values to be more specific).

I want to merge them into one multilinestring so i can see what the shape of these values are in MySQL workbench spatial viewer or somewhere else.

Is there a mysql function that can join all the linestrings into a linestring or any other way to do this?

Answer 1

There is a spatial function called ST_Union that would help, but you may not have the right version of MySQL to use it.

Here's a way that you can manipulate the WKT directly using GROUP_CONCAT. It will allow you to create a MULTILINESTRING. Alternatively, you could create a GEOMETRYCOLLECTION the same way.

Note that you may run out of memory, so have a look at the group_concat_max_len variable.

SET GLOBAL group_concat_max_len = 99999; 

use test_gdb;


CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `geom` GEOMETRY NULL,
  PRIMARY KEY (`id`));

INSERT INTO `test`
  (`geom`)
VALUES
  (LineStringFromText('LINESTRING(0 0,1 1,2 2,3 3,4 4)')),
  (LineStringFromText('LINESTRING(5 5,6 6,7 7,8 8,9 9)'));


SELECT
    GeomFromText(CONCAT('MULTILINESTRING(', GROUP_CONCAT(REPLACE(AsText(`geom`), 'LINESTRING', '')), ')'))
FROM 
    `test`;

DROP TABLE `test`;

I suppose an alternative to all this would be to view the table in QGIS directly:

Import MySQL Spatial vector layer in QGIS