5

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.

j0k
  • 22,600
  • 28
  • 79
  • 90
Laxmidi
  • 2,650
  • 12
  • 49
  • 81
  • Are you using/did you have a look at the GIS features for MySQL? http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html – relet Jul 27 '10 at 23:03
  • Hi relet, Thanks for your message. Yep, I'm using MySQL spatial extensions. The data is stored as MySQL polygons. – Laxmidi Jul 28 '10 at 16:38
  • So you're saying AREA(my_polygon) is not working because you have [lat longs] instead of [long lats]? The native format should be [Long Lat] and I don't know if that would affect area calculations. In my case AREA(poly) worked fine. – derickito Jul 30 '14 at 18:31

1 Answers1

2

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.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Hi duffymo, Thank you for the advice. I'll have to study the link that you provided. – Laxmidi Jul 28 '10 at 16:48
  • The area function does all its calculations in a Cartesian fashion. – SO Stinks Sep 15 '10 at 02:59
  • 1
    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 Sep 15 '10 at 09:28