代码之家 › 专栏 › 技术社区 › stopopol

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

geospatial sql mysql

stopopol · 技术社区 · 6 年前

我需要在MySQL数据库(MySQL5.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()提供列的方式有关。

1 回复 | 直到 6 年前

Jorge Campos 6 年前

感谢 @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))) ,同样,第一个点和最后一个点需要相同,以便多边形闭合。