MySQL Forums

Re: Using MySQL Spatial Extensions to make a Google Maps interface.
Posted by: Jay Pipes
Date: September 06, 2005 03:19PM

There is one major problem with using the current spatial extensions in MySQL for doing any kind of GIS work, particularly with GoogleMaps type functionality. The MySQL spatial extensions currently only provide implementations for a Euclidian world view, which is a planar system. The calculations you will receive from the spatial extension functions (many of which have not yet been implemented) will produce wildly inaccurate results once the distance between two points crosses a certain threshold and this variance will increase the further from the equator the points reside.

Currently, until the spatial extensions provide support for a spherical (Hadrian, I believe?) model, you should use the great circle distance formula and related nautical formulae in order to get accurate calculations based on speherical latitude and longitude.

The great circle distance formula states that the distance (d) between two points (x1,y1) and (x2,y2), where the x values are latitude and the y values are longitude, on a sphere of radius r can be determined by this calculation:

d = acos ( sin(x1) * sin(x2) + cos(x1) * cos(x2) * cos(y2- y1) ) * r

The formula assumes that the latitude and longitude values are in radians.

Assuming two user variables containing the latitude/longitude pairs of two US ZCTA areas (here, I used zip code '10001' and '21236' (approx New York City and Baltimore, Maryland), you could find the distance between the two points using:

mysql> SELECT ACOS(SIN(@lat_A) * SIN(@lat_B)
-> + COS(@lat_A) * COS(@lat_B)
-> * COS(@long_B - @long_A)) * 3956 AS distance;
+-----------------+
| distance |
+-----------------+
| 161.70380719616 |
+-----------------+
1 row in set (0.00 sec)

The constant 3956 is the radius of the Earth in miles.

If you used the spatial extensions, this calculation would be off by around 1.2-2.0 miles. Similarly, if you want to find zip codes which fall within a specified radius, you could transpose the equation into the WHERE expression, here to find zips in a test database within 5 miles of the ZCTA '21236':

mysql> SELECT
-> x2.zcta
-> FROM ZCTA x1, ZCTA x2
-> WHERE x1.zcta = '21236'
+-------+
| zcta |
+-------+
| 21057 |
| 21128 |
| 21162 |
| 21206 |
| 21214 |
| 21234 |
| 21236 |
| 21237 |
+-------+
8 rows in set (0.13 sec)

Again, if using the spatial extensions, the calculations are skewed.

Blatant plug: you can read more advanced discussions of GIS-related SQL in Chapter 9 of Pro MySQL. Hope this info helps,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Subject
Views
Written By
Posted
23919
August 23, 2005 01:19PM
7763
August 24, 2005 01:10AM
12508
August 24, 2005 04:33AM
7672
September 06, 2005 02:51PM
Re: Using MySQL Spatial Extensions to make a Google Maps interface.
23116
September 06, 2005 03:19PM
8376
January 18, 2006 11:51AM
7280
November 02, 2007 09:07PM
6927
May 16, 2009 07:35AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.