0

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. */
2
  • 1
    what is your projection for your data? [Note: MySQL 8.0 has true spatial reference support] – Mapperz Sep 4 '20 at 18:36
  • @Mapperz, I have added input in question itself. – Ankit Sep 7 '20 at 7:43
2

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:

2
  • stackoverflow.com/questions/1185408/… is this something are you suggesting? – Ankit Sep 7 '20 at 13:02
  • Take this with a grain of salt, but I think that yes something like that is what you need. I would search for ways to convert lat/lng coords to UTM or something like that. I think that this way you will get a correct result from the area calculation. The first thing I would try though, is to install a version of MySQL > 8.0 – lockonzero Sep 8 '20 at 6:40

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.