Mysql – How to group-by to get MultiPoint/GeometryCollection in MySQL

geometryMySQLspatial

I have a table x like this:

Columns:

SubjectID int(11)
Timestamp bigint(20) 
fix_geom point

enter image description here

When i try to group my fix_geom column to a MultiPoint (or GeometryCollection) by SubjectID, like:

SELECT SubjectID, ST_AsText(MultiPoint(fix_geom))
FROM x
WHERE SubjectID = 100
GROUP BY SubjectID;

i get a table with a multipoint geometry, but it contains just one single Point instead of the many i'd liked to see there…:

enter image description here

Can anybody tell me what i'm doing wrong?

Cheers and many thanks in advance,
Olaf

Best Answer

As Evan Carroll said in a comment: Unlike PostGIS, spatial aggregates are not available for MySQL.

The workaround left in a comment by danblack works:

It looks like you want MultiPoint to be an aggregate function, which is isn't. You'll probably need something like ST_GeomFromText(CONCAT('MultiPoint(', GROUP_CONCAT(...), ')')), where ... gets an X Y string from fix_geom.

Related Question