1

I have a problem with ST_Area function in MySQL 8.0.16.

The result of the procedure call returns a different result than any library used to calculate the area of a polygon.

Example:

I have a GeoJSON with geometry like this:

{
        "type": "Polygon",
        "coordinates": [
            [
                [
                    16.91720977783202,
                    52.28469952907162
                ],
                [
                    16.910300407409657,
                    52.278608423688205
                ],
                [
                    16.919913444519032,
                    52.27753189128164
                ],
                [
                    16.91720977783202,
                    52.28469952907162
                ]
            ]
        ]
    }

The results of calulating area (in square meters) are:

  • Google Maps: 250165.80185391553
  • Turf library: 250152.24833417358
  • ST_Area(ST_GeomFromGeoJSON(@poly_json)): 202818.55768974536

First I was worried that GMaps (we got the coordinates from a map) is using some other projection, but it converts it into proper LatLng, so it shouldn't be a problem.

Do you have an idea why the result is so different between calculating area on the database and on the client?

3
  • Perhaps the lats and longs are swapped??
    – Rick James
    Feb 27, 2020 at 6:17
  • @RickJames No, I've checked this, example here is using [lng, lat] format, which is valid for GeoJSON standard.
    – arkus
    Feb 28, 2020 at 20:01
  • Perhaps one or the other is not using spherical math??
    – Rick James
    Feb 28, 2020 at 20:04

1 Answer 1

1

Please upgrade your MySQL version. In 8.0.32 the st_area returns:

mysql> set @poly = '{
'>         "type": "Polygon",
'>         "coordinates": [
'>             [
'>                 [
'>                     16.91720977783202,
'>                     52.28469952907162
'>                 ],
'>                 [
'>                     16.910300407409657,
'>                     52.278608423688205
'>                 ],
'>                 [
'>                     16.919913444519032,
'>                     52.27753189128164
'>                 ],
'>                 [
'>                     16.91720977783202,
'>                     52.28469952907162
'>                 ]
'>             ]
'>         ]
'>     }';
Query OK, 0 rows affected (0,00 sec)

mysql> select st_area(st_geomfromgeojson(@poly));
+------------------------------------+
| st_area(st_geomfromgeojson(@poly)) |
+------------------------------------+
|                  250662.8501818171 |
+------------------------------------+
1 row in set (0,00 sec)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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