views:

123

answers:

1

Hi,

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.

Any suggestions?

Thanks!

-Laxmidi

+1  A: 

You've got to transform those lats and longs 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/long 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.

duffymo
Hi duffymo,Thank you for the advice. I'll have to study the link that you provided.
Laxmidi
The area function does all its calculations in a Cartesian fashion.
Dr. Person Person II
That's why I said "convert your lat/long coordinates into equivalent surface (x,y) coordinates with the right units (e.g. miles). Then pass those into the AREA function." If you voted me down because you didn't read my answer, then shame on you. I don't know what you did to merit the title "Dr.", but your reading comprehension needs some work.
duffymo