Getting area (in square meter or km) of polygon in MySQL 5.6

Question

I have a table in MySQL that has polygon defined. Read MySQL 5.6 geospatial index documentation and came across ST_Area function. Tried to dig into the units which are computed as per the spatial reference system. I am not sure how to convert the output to square meters.

CREATE TABLE Area (
  `polygon`  polygon NOT NULL,
  Name VARCHAR(100),
  SPATIAL KEY `Polygon` (`Polygon`)
);

INSERT INTO Area(polygon, Name) VALUES ('POLYGON((27.55858717457406 
76.6309398865454,27.493737501738696 76.6305965637915,27.49130108815994 
76.67728845832275,27.496173861399882 76.69376795051025,27.516576256439183 
76.69720117804931,27.536366008597625 76.69273798224853,27.55858717457406 
76.6309398865454))', 'area1');

INSERT INTO Area(polygon, Name) VALUES ('POLYGON((18.311749294999043 
78.27789997153775,18.293169874823796 78.35446094565884,18.30571935075326 
78.3462211995651,18.315538274143172 78.34010576301114,18.323727074736937 
78.3350202947189,18.322240082529422 78.32786416106717,18.32071233749967 
78.32079385810391,18.31696421255629 78.3030698209335,18.312849329745635 
78.28360771232144,18.311749294999043 78.27789997153775))', 'area2');

select ST_Area(polygon) from Area; /* not sure what is the result output. */

Answer 1

MySQL < 8.0: In your case (since your coordinates are in lat/lng) the ST_Area function does not really calculate something with meaning. In order to use the MySQL ST_Area function you would need X,Y coords so you would have to convert the ones you have now.

MySQL >= 8.0: In this MySQL version the system will support the type of coordinates that you have by doing something like this (I can't test it right now but it is in the documentation):

SELECT ST_Area(ST_PolyFromText(
"POLYGON((27.55858717457406 76.6309398865454,27.493737501738696 
76.6305965637915,27.49130108815994 76.67728845832275,27.496173861399882 
76.69376795051025,27.516576256439183 76.69720117804931,27.536366008597625 
76.69273798224853,27.55858717457406 76.6309398865454))",4326);

References:

• https://stackoverflow.com/questions/5981676/mysql-geometry-area-function-returns-what-exactly-when-coords-are-long-lat https://dev.mysql.com/doc/refman/8.0/en/gis-polygon-property-functions.html#function_st-area
• https://dev.mysql.com/doc/refman/8.0/en/gis-polygon-property-functions.html#function_st-area

Answer 2

SELECT
  round(
    (
      ST_Area(
        ST_PolyFromText(
          "POLYGON((27.55858717457406 76.6309398865454,27.493737501738696 76.6305965637915,27.49130108815994 76.67728845832275,27.496173861399882 76.69376795051025,27.516576256439183 76.69720117804931,27.536366008597625 76.69273798224853,27.55858717457406 76.6309398865454))"
        )
      ) * 10000000
    ) / 0.8587,
    3
  )