mysql - 从边界坐标列创建 mysql 多边形列

Question

我需要在 mysql 数据库 (MySQL 5.5.60-0ubuntu0.14.04.1) 中创建一个列，该列具有从数据库中包含顶部、底部、左侧和右侧边界坐标的其他表创建的边界框。

我的查询看起来像这样，但由于语法错误无法执行。虽然当我创建一个点并只取两个坐标时它工作正常。

SELECT 
name.`field_site_sitelong_value` AS name,
basetable.`uuid`,
basetable.`nid`,
msl.`field_elevation_average_value`,
Polygon(
    coordinates.`field_geo_bounding_box_left` coordinates.`field_geo_bounding_box_bottom`,
    coordinates.`field_geo_bounding_box_right` coordinates.`field_geo_bounding_box_bottom`,
    coordinates.`field_geo_bounding_box_right` coordinates.`field_geo_bounding_box_top`,
    coordinates.`field_geo_bounding_box_left` coordinates.`field_geo_bounding_box_top`,
    coordinates.`field_geo_bounding_box_left` coordinates.`field_geo_bounding_box_bottom`
) AS geom

FROM `node` basetable
INNER JOIN `field_data_field_geo_bounding_box` coordinates
ON coordinates.`entity_id` = basetable.`nid`

INNER JOIN `field_data_field_site_sitelong` name
ON name.`entity_id` = basetable.`nid`

LEFT JOIN `field_data_field_elevation_average` msl
ON msl.`entity_id` = basetable.`nid`

WHERE basetable.`status` = 1 AND `field_geo_bounding_box_geo_type` = 'polygon'

我认为这个问题与我向 Polygon() 提供列的方式有关。

Answer 1

感谢 @Jorge Campos 非常有帮助的评论我能够提出一个有效的查询:

SELECT 
name.`field_site_sitelong_value` AS name,
basetable.`uuid`,
basetable.`nid`,
msl.`field_elevation_average_value`,

POLYFROMTEXT(concat(
        'Polygon((', 
            coordinates.`field_geo_bounding_box_left`   , ' ', coordinates.`field_geo_bounding_box_bottom`  , ', ',     
            coordinates.`field_geo_bounding_box_right`  , ' ', coordinates.`field_geo_bounding_box_bottom`  , ', ', 
            coordinates.`field_geo_bounding_box_right`  , ' ', coordinates.`field_geo_bounding_box_top`     , ', ',
            coordinates.`field_geo_bounding_box_left`   , ' ', coordinates.`field_geo_bounding_box_top`     , ', ', 
            coordinates.`field_geo_bounding_box_left`   , ' ', coordinates.`field_geo_bounding_box_bottom`  , 
        '))'
    ))
 AS GEOM

FROM `node` basetable
INNER JOIN `field_data_field_geo_bounding_box` coordinates
ON coordinates.`entity_id` = basetable.`nid`

INNER JOIN `field_data_field_site_sitelong` name
ON name.`entity_id` = basetable.`nid`

LEFT JOIN `field_data_field_elevation_average` msl
ON msl.`entity_id` = basetable.`nid`

WHERE basetable.`status` = 1 
  AND `field_geo_bounding_box_geo_type` = 'polygon'

POLYFROMTEXT 函数将来自 concat 函数结果的完整多边形字符串转换为实际的多边形空间数据类型。如果你省略 POLYFROMTEXT 你会得到一个人类可读的 WKT每个多边形。

请记住，在为多边形构造 WKT 时，点的顺序很重要 (POLYGON((left bottom,right bottom,right top,left top,left bottom)))，也是第一个点和最后一个点必须相同，这样多边形才闭合。