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:

First I tried to create polygons like:

SELECT t0.zone, POLYGON(GROUP_CONCAT(t0.coordinate ORDER BY t0.logical_order SEPARATOR ',')) FROM
(
    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 
GROUP BY t0.zone 

However, this gives me the error:

Error Code: 1367. Illegal non geometric 'group_concat(`t0`.`coordinate` order by `t0`.`logical_order` ASC separator ',')' value found during parsing

I also 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))