ST_Area() for latitude and longitude

Question

I have a polygon that contains multiple coordinates in MariaDB:

POLYGON((50.4668 -6.90665,51.73607 0.14657,55.3032 -4.52724,50.4668 -6.90665))

I want to calculate area of this polygon with MariaDB like this:

SELECT ST_Area(ST_GeomFromText('POLYGON((50.4668 -6.90665,51.73607 0.14657,55.3032 -4.52724,50.4668 -6.90665))'))

The result of the above query is 15.546039738650009 which is wrong. I calculated the area of the polygon with this tool and it gives me 119116192747.1.

What should I do to make mariaDB understand this polygon contains latitude and longitudes to calculate area correctly?

Did you try to set SRID? https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d6cbc8c8f6c707002a89fd430dc78baa — Paul Spiegel, Feb 28 '19 at 19:23
@PaulSpiegel Yes but I don't know how to do it in MariaDB. see: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=d6cbc8c8f6c707002a89fd430dc78baa — Sky, Mar 01 '19 at 10:37
I'm afraid MariaDB is far behind MySQL here. I don't even know how to get a distance in meters. — Paul Spiegel, Mar 01 '19 at 17:08

score 2 · Answer 1 · answered Mar 02 '19 at 11:18

MariaDB stores SRIDs, and complains when trying to work on geometries with mixed SRIDs, but it does not actually perform any spatial reference / projection operations. So the world is flat, no matter what SRID you use.

MySQL did the same up to MySQL 5.7, but starting with MySQL 8.0 has true spatial reference support, at least sort of.

E.g. it now correctly returns the distance between two degrees of latitude correctly as ~111km instead of just "1":

> SELECT st_distance(st_geomfromtext('POINT(50.0 10)', 4326), st_geomfromtext('POINT(51.0 10)', 4326)):
        111239.69315297529

Spatial reference support for ST_AREA() is only supported starting with 8.0.13 though. Before that you'll get when using an SRID different to 0 (the cartesian plane):

> SELECT ST_Area(ST_GeomFromText('POLYGON((50.4668 -6.90665,51.73607 0.14657,55.3032 -4.52724,50.4668 -6.90665))', 4326));
ERROR 3618 (22S00): st_area(POLYGON) has not been implemented for geographic spatial reference systems.

With MySQL >= 8.0.13 you'll get the expected result though:

> SELECT ST_Area(ST_GeomFromText('POLYGON((50.4668 -6.90665,51.73607 0.14657,55.3032 -4.52724,50.4668 -6.90665))', 4326));
    119085646585.42969

score 1 · Answer 2 · answered Mar 03 '19 at 05:25

According to MariaDB documents it does not provide this feature:

In MariaDB, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry.

However, in Mysql 8.0.13 this feature is provided you can calculate polygon area with ST_Area():

Incompatible Change: Previously, ST_Area() supported only geometry arguments having a Cartesian spatial reference system (SRS) and produced an error when invoked with geometry arguments that specified a geographic SRS. ST_Area() now supports geometry arguments having a geographic SRS and returns the geodetic area in square meters.

So If you change your database to MYSQL > 8.0.13 you can calculate polygon area using this query:

SELECT ST_Area(ST_PolyFromText('POLYGON((50.4668 -6.90665,51.73607 0.14657,55.3032 -4.52724,50.4668 -6.90665))', 4326));

ST_Area() for latitude and longitude

2 Answers2