4

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?

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

4
  • Ok, so apparently our version of mysql does support st_union. All the linestrings are on lgate0832feature.thegeom If I do select st_union(thegeom) from lgate083feature LIMIT 0, 1000 I get Error Code: 1582. Incorrect parameter count in the call to native function 'st_union' 0.089 sec I can do SELECT CONCAT('MULTILINESTRING(', GROUP_CONCAT(REPLACE(AsText(thegeom), 'LINESTRING', '')), ')') FROM lgate083feature; – pranahata Jul 26 '15 at 12:13
  • Ok, so apparently our version of mysql does support st_union. All the linestrings are on lgate0832feature.thegeom If I do select st_union(thegeom) from lgate083feature LIMIT 0, 1000 I get Error Code: 1582. Incorrect parameter count in the call to native function 'st_union' 0.089 sec I can do SELECT CONCAT('MULTILINESTRING(', GROUP_CONCAT(REPLACE(AsText(thegeom), 'LINESTRING', '')), ')') FROM lgate083feature; To get the values as a string but i would like to get them as a geometry so the spatial viewer of this mysqlworkbench here renders the data – pranahata Jul 26 '15 at 12:19
  • You need to use the GeomFromText function. See the edited answer. – nagytech Jul 26 '15 at 22:02
  • Thanks, it looks like the GeoFromText is not working for me or the concatenated MultiLineString text is not well formed. If I run SELECT CONCAT('MULTILINESTRING(', GROUP_CONCAT(REPLACE(AsText(thegeom), 'LINESTRING', '')), ')') FROM linksregister.lgate083feature; I get a multilinestring text, but if i wrap it into GeomFromText as in SELECT GeomFromText(CONCAT('MULTILINESTRING(', GROUP_CONCAT(REPLACE(AsText(thegeom), 'LINESTRING', '')), ')')) FROM linksregister.lgate083feature I get a null – pranahata Jul 28 '15 at 1:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.