I have a table that has columns
zone(name of the zone which is a string),
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
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?
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))
SQL fiddle with sample data: http://www.sqlfiddle.com/#!9/5094e5/5