0

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

  • 1
    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. – ypercubeᵀᴹ May 15 '18 at 10:30
  • @ypercubeᵀᴹ 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. – swdon May 15 '18 at 10:46
  • Side note: DECIMAL(8,6)/(9,6) is precise enough to distinguish people standing next each other; having 10 decimal places is excessive. – Rick James May 27 '18 at 15:42
  • What version of MySQL? – Evan Carroll Jun 22 '18 at 4:03
  • 2
    Possible duplicate of MySQL equivalent of a spatial ST_Union aggregate function? – Evan Carroll Jun 22 '18 at 4:08
2

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:

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 
  • 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 :) – swdon May 15 '18 at 14:39
  • 1
    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. – ypercubeᵀᴹ May 15 '18 at 14:44
1

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,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.