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?