0

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;
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.

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.