Use GROUP_CONCAT() to generate polygons in mysql

Question

I have a table that has columns zone(name of the zone which is a string), longitude (decimal), latitude (decimal), logical order (integer).

logical_order indicates the order in which the coordinates can be combined to create the polygon. For example, each zone will have multiple rows in this table with different longitude, latitude. For each zone row, the logical_order will start at 1 (for starting point) and increase by one per row. For example, if a zone has 3 points, logical_order will run from 1 through 3. Coordinates at 3 are not the same as at 1. In other words, the coordinates do not close the polygon.

I'm trying to create POLYGON by grouping coordinates of each zone and using spatial package as below:

I tried the below query:

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, COUNT(zone) + 1 AS logical_order FROM zones GROUP BY zone
) t0 
GROUP BY t0.zone

However, this gives me the error:

Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext.

How can I fix this error? Am I approaching the right way?

EDIT:

If I try without the ST_GeometryFromText() in the second query, I get a string like:

POLYGON(77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800)

After adding two parenthesis like below still give the same error:

POLYGON((77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800))

EDIT:

SQL fiddle with sample data: http://www.sqlfiddle.com/#!9/5094e5/5

Answer 1

The error is likely cause because the "cycle" isn't closed, i.e. last point in the polygon does not match the first one.

This can be from two issues:

• the group_concat_max_len setting is too low (default is 1024).

Solution: increase it, either in the server or session level.

• the error-prone GROUP BY used to add the additional point.

The GROUP BY you use is prone to give wrong results, as it may not always select the row you want (WHERE logical_order = 1) to read the coordinates:

(
    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 

I'd write it like this:

(
    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 

Answer 2

What you want is a Spatial Aggregate. Unfortunately, MySQL doesn't yet support them, consider upgrading to PostgreSQL and PostGIS (which is in every way better).

That said, there is a better workaround. You can find that here,

• MySQL equivalent of a spatial ST_Union aggregate function?