How do I construct a LINESTRING by aggregating Points (ST_MakeLine) in MariaDB?

Question

Let's say I have a table with 3 points,

CREATE TABLE foo ( id int PRIMARY KEY, geog point );
INSERT INTO foo (id, geog) VALUES
  ( 1, Point(0,0) ),
  ( 2, Point(1,1) ),
  ( 3, Point(2,2) );

How can I construct a LINESTRING with those three points in those three different rows. The resulting WKT should be LINESTRING( 0 0, 1 1, 2 2 )?

---

PostGIS does this with a Spatial Aggregate called ST_MakeLine

SELECT ST_MakeLine(geog)
FROM foo
ORDER BY id;

Answer 1

Because MariaDB (and MySQL) lack all spatial aggregates, the only method is currently to create the WKT, and force MySQL to parse that WKT.

SELECT ST_AsText(
  ST_GeomFromText(CONCAT(
    'LINESTRING (',
    GROUP_CONCAT(CONCAT(ST_X(geog),' ',ST_Y(geog)) SEPARATOR ','),
    ')'
  ))
)
FROM foo
ORDER BY id;
-- returns LINESTRING (0 0,1 1,2 2) 

The inside part

GROUP_CONCAT(CONCAT(ST_X(geog),' ',ST_Y(geog)) SEPARATOR ',')

Creates the point list, and then we just wrap that in LINESTRING ( $pointlist ) and feed that to ST_GeomFromText()

---

ℹ There are type-specific versions of ST_GeomFromText, but at least in MariaDB they seem to do nothing different, except add confusion.