我有一个表,其中包含zone
列(字符串区域名称),longitude
(十进制),latitude
(十进制),logical order
(整数)。
logical_order
指示组合坐标以创建面的顺序。例如,此表中每个区域将具有不同longitude
、latitude
的多行。对于每个区域行,logical_order
将从 1(用于起点)开始,每行增加一个。例如,如果一个区域有 3 个点,则logical_order
将从 1 到 3 运行。3 处的坐标与 1 处的坐标不同。换句话说,坐标不会闭合面。
我正在尝试通过对每个区域的坐标进行分组并使用空间包来创建POLYGON
,如下所示:
我尝试了以下查询:
SELECT t0.zone, ST_GeometryFromText(CONCAT("'POLYGON((", GROUP_CONCAT(t0.coordinate ORDER BY t0.logical_order SEPARATOR ','), "))'")) FROM
(
(SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, logical_order FROM zones)
UNION ALL
(SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, 1000 AS logical_order FROM zones WHERE logical_order = 1)
) t0
GROUP BY t0.zone
但是,这给了我错误:
Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext.
如何修复此错误?我走对了路吗?
编辑:
如果我在第二个查询中尝试不使用ST_GeometryFromText()
,我会得到一个字符串,如下所示:
POLYGON(77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800)
添加两个括号后,如下所示仍然会给出相同的错误:
POLYGON((77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800))
编辑2:
我还为每个区域手动检查了多边形闭合,这很好。我也增加了对group_concat()
字符数的限制。但错误仍然存在。
编辑3:
SQL 摆弄示例数据:http://www.sqlfiddle.com/#!9/5094e5/5
这由 ypercubeTM 在 db exchange 中回答。您可以在此处参考实际答案和讨论。
有几个问题:
- 字符数的默认值太低
group_concat_max_len
切断某些坐标 - 在最外层
CONCAT()
中使用的双引号会导致问题。需要使用单引号 - 我使用
GROUP BY
关闭多边形的方式是值得怀疑的。
为了将来的用户的利益,我将在这里复制该答案:
该错误可能是因为"循环"未闭合,即多边形中的最后一个点与第一个点不匹配。
这可能来自两个问题:
group_concat_max_len
设置太低(默认值为 1024)。
解决方案:在服务器或会话级别增加它。
- 容易出错的
GROUP BY
用于添加附加点。
您使用的GROUP BY
容易给出错误的结果,因为它可能并不总是选择您想要(WHERE logical_order = 1
) 读取坐标的行:
(
SELECT zone,
CONCAT(longitude, ' ', latitude) AS coordinate,
logical_order
FROM zones
UNION
-- This is to close the polygon by adding the first coordinate also
-- as the final coordinate of the zone.
SELECT zone,
CONCAT(longitude, ' ', latitude) AS coordinate,
COUNT(zone) + 1 AS logical_order
FROM zones
GROUP BY zone
) t0
我会这样写:
(
SELECT zone,
CONCAT(longitude, ' ', latitude) AS coordinate,
logical_order
FROM zones
UNION ALL
-- This is to close the polygon by adding the first coordinate also
-- as the final coordinate of the zone.
SELECT zone,
CONCAT(longitude, ' ', latitude),
1000000 -- unlikely to have a million points polygon
FROM zones
WHERE logical_order = 1
) t0
最终工作查询:
SELECT t0.zone, ST_GeometryFromText(CONCAT('POLYGON((', GROUP_CONCAT(t0.coordinate ORDER BY t0.logical_order SEPARATOR ','), '))')) FROM
(
(SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, logical_order FROM zones)
UNION
(SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, 10000 AS logical_order FROM zones WHERE logical_order = 1)
) t0
GROUP BY t0.zone;
需要适当设置group_concat_max_len
。