在MySQL中获取多点空间数据 [英] Fetching Multipoint Spatial Data in MySQL

问题描述

我没有找到任何答案来选择MySQL中 multipoint 数据类型中的每个点.我有包含许多点的多点，并且我想通过查询选择每个点，但无法弄清楚.有任何想法吗?

I didn't find any answer how to select every point in multipoint data type in MySQL. I have multipoint that contains many points and I want to select every point by query and I can't figure it out. Any ideas?

表结构:图片

示例数据:图片

在MySQL文档中，我仅找到了这一点，但没有帮助:

In MySQL documentation I've found only this, but it didn't help: https://dev.mysql.com/doc/refman/8.0/en/fetching-spatial-data.html

推荐答案

如果您使用的是MySQL> = 8.0.2，则选项可能是:

If you are using MySQL >= 8.0.2, an option may be:

DELIMITER //

CREATE PROCEDURE `sp_test`(`p_id` BIGINT UNSIGNED)
BEGIN
  SET @`sql` := CONCAT('
    WITH RECURSIVE `sequence` AS (
      SELECT 1 `seq`
      UNION ALL
      SELECT `seq` + 1 `seq`
      FROM `sequence`
      WHERE `sequence`.`seq` < ?
    )
    SELECT
      `sequence`.`seq` `id`,
      ST_AsText(ST_GeometryN(`points`, `sequence`.`seq`)) `point`
    FROM `sequence`
      INNER JOIN `tb_test` ON
        `tb_test`.`id` = ', `p_id`);

  SET @`limit` := (
    SELECT
      ST_NumGeometries(`points`)
    FROM
      `tb_test`
    WHERE
      `id` = `p_id`
  );

  PREPARE `stmt` FROM @`sql`;
  EXECUTE `stmt` USING @`limit`;
  DEALLOCATE PREPARE `stmt`;
END//

DELIMITER ;

请参见 db-fiddle .

更新

DELIMITER //

CREATE PROCEDURE `sp_test`(`p_id` BIGINT UNSIGNED)
BEGIN
  SET @`sql` := CONCAT('
    WITH RECURSIVE `sequence` AS (
      SELECT 1 `seq`
      UNION ALL
      SELECT `seq` + 1 `seq`
      FROM `sequence`
      WHERE `sequence`.`seq` < ?
    )
    SELECT
      `sequence`.`seq` `id`,
      ST_AsText(ST_GeometryN(`points`, `sequence`.`seq`)) `point`,
      ST_X(ST_GeometryN(`points`, `sequence`.`seq`)) `X`,
      ST_Y(ST_GeometryN(`points`, `sequence`.`seq`)) `Y`
    FROM `sequence`
      INNER JOIN `tb_test` ON
        `tb_test`.`id` = ', `p_id`);

  SET @`limit` := (
    SELECT
      ST_NumGeometries(`points`)
    FROM
      `tb_test`
    WHERE
      `id` = `p_id`
  );

  PREPARE `stmt` FROM @`sql`;
  EXECUTE `stmt` USING @`limit`;
  DEALLOCATE PREPARE `stmt`;
END//

DELIMITER ;

请参见 db-fiddle .

这篇关于在MySQL中获取多点空间数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！