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

• ypercubeᵀᴹ: Can you check that all polygons have the last point the same as the first point (ie they close the cycle)? The GROUP BY you use to produce this doesn't look very sound.
• kovac: @ypercubeTM You are right, they don't. I think it's due to limit in size allowed for group_concat() because at that step, it doesn't contain all the coordinates. I also revised the group by to a where clause. The union gives the correct result but the group_concat() does not.
• Rick James: Side note: DECIMAL(8,6)/(9,6) is precise enough to distinguish people standing next each other; having 10 decimal places is excessive.
• Evan Carroll: What version of MySQL?
• Evan Carroll: Possible duplicate of MySQL equivalent of a spatial ST_Union aggregate function?

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 

• kovac: The problem was I was using double quotes in concat instead of single quotes eh. Thank you for the effort and working through this. You are awesome :)
• ypercubeᵀᴹ: I don't think single vs double makes a difference (except perhaps your server has non-default settings). It's good to always use single quotes for string literals.

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?