How Do I Calculate the Area of a Polygon in a MySQL Database When the Polygon's Points are Lat Longs?

Question

How do I calculate the area of a polygon stored in a MySql database? The polygons' points are lat longs. So, degrees and minutes seem to be causing a problem.

I've tried:

SELECT AREA( my_polygon ) 
FROM  `my_table` 
WHERE name =  'Newport'

Because, the points are lat longs, I get weird results.

(I'm not able to switch to Postgre). Is there a way to do this in MySQL? I'd like to get the results in sq. meters or sq. km or sq. miles-- any of these would be fine.

Answer 1

You've got to transform those lats and lons into a more appropriate coordinate system.

Since the earth is a sphere, you're talking about calculating an area in spherical coordinates.

The docs say that the MySQL "AREA" function takes a polygon as its input. I would say that if you want area as something like square miles you should convert your lat/lon coordinates into equivalent surface (x, y) coordinates with the right units (e.g., miles). Then pass those into the AREA function.

This link suggests that someone else has had this problem and solved it.