Nearby locations SQL magic
Here's an SQL statement which I use in my WordPress plugin to list nearby locations, ordered by geographic proximity... Should be easy to port, no?
Here's the SQL statement:
Here's what it does: It takes the latitude and longitude of the current WordPress article (post_lat and post_lon), works out the radial distance between this point and each other point within a given latitude range (I do the longitude calculation later in the plugin script - see here), order these by their distance, then return some of them ($limit).
I thought I'd share this with you now cause it'll probably take me a little while before I learn how to get around YACS, caching results, etc... Perhaps someone with more experience could do something with it. ::)
SELECT ID, post_status, post_date, post_title, post_lat, post_lon, abs( 3956 * acos( sin( radians( $currentlat ) ) * sin( radians( post_lat ) ) + cos( radians( $currentlat ) ) * cos( radians( post_lat ) ) * cos( radians( post_lon - $currentlon ) ) ) ) AS distance
FROM wp_posts
WHERE ID != $post->ID
AND post_lat BETWEEN $post->post_lat - $latlimit AND $currentlat + $latlimit
ORDER BY distance
LIMIT $limit
Here's what it does: It takes the latitude and longitude of the current WordPress article (post_lat and post_lon), works out the radial distance between this point and each other point within a given latitude range (I do the longitude calculation later in the plugin script - see here), order these by their distance, then return some of them ($limit).
I thought I'd share this with you now cause it'll probably take me a little while before I learn how to get around YACS, caching results, etc... Perhaps someone with more experience could do something with it. ::)