Re: Using MySQL Spatial Extensions to make a Google Maps interface.
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'
-> AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
-> + COS(x1.lat_radians) * COS(x2.lat_radians)
-> * COS(x2.long_radians - x1.long_radians)) * 3956 <= 5;
+-------+
| 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