Convert Multipoint to Point in MySQL

Question

If many Points are stored as Geometry in a Field as Multipoint, how to SELECT (and convert) all Points?

But without a procedure, while or loop, because they are too slow, and no String Conversion.

For example: in the #1 Table

MULTIPOINT(48 16,49 16,50 16)

I want to achieveve a result like:

id |     point
1  |  POINT(48 16)
2  |  POINT(49 16)
3  |  POINT(50 16)

Answer 1

The only "trick" here is generating a sequence of natural numbers. For that - your best bet would be to create a numbers table that will simply hold numbers from 1 .. Maximum number of points you think would be possible for each MultiPoint:

CREATE TABLE `numbers` (
  id int,
  PRIMARY KEY (`id`)
);
INSERT INTO `numbers` VALUES (1),(2),(3),(4),(5); /* ... as many as needed */

Now that we solved that, you can use the following query to break the MultiPoint to points:

SELECT `numbers`.`id`, AsText(GeometryN(`geometryFieldName`, `numbers`.`id`))
FROM `geometryTableName`
INNER JOIN `numbers` ON
  (`numbers`.`id` <= NumGeometries(`geometryFieldName`))