在MySQL中获取多点空间数据 [英] Fetching Multipoint Spatial Data in MySQL
本文介绍了在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?
表结构:图片
示例数据:图片
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屋!
查看全文