CODEWITHSUNDEEP
mysqlsqlgeospatial
stopopol
stopopol

Reputation: 506

Create mysql polygon column from bounding coordinate columns

I need to create a column in a mysql database (MySQL 5.5.60-0ubuntu0.14.04.1) that features bounding boxes created from other tables in the database containing the top, bottom, left and right bounding coordinates.

My query looks like this, but can't be executed due to syntax errors. It works fine though when I create a point and just take two coordinates.

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'

I assume that the issue has somehow todo with the way I provide the columns to the Polygon().

Upvotes: 2

Views: 894

Answers (1)

stopopol
stopopol

Reputation: 506

Thanks to the extremely helpful comments of @Jorge Campos I was able to come up with a working query:

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'

The POLYFROMTEXT function turns the complete polygon string from the result of the concat function into an actual polygon Spatial Data Type. If you omit the POLYFROMTEXT you will get a human readable WKT of each polygon.

Bear in mind that the order of points matter when constructing a WKT for a polygon (POLYGON((left bottom,right bottom,right top,left top,left bottom))), also the first point and the last point need to be identical so that the polygon is closed.

Upvotes: 2

Related Questions