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.
Bernard from nearby-an-airport Associate, 6734 posts |
Eoin, this sounds interesting. At the moment YACS support to attach one or several locations to any article or user profile (look at mine). Also, you can configure YACS to indicate the location of the server (look at the "geo.position" meta tag in the source of the home page). Maybe I should make this evolve to something more simple, with only one location per page. The server location would be used by default. With such an approach the implementation of your plug-in would become straightforward... Does it make sense to have several locations per page? If the answer is NO i will suppress the locations module, and streamline the code... Thank you for the suggestion |
| Eoin 23 posts |
Keeping locations separate might be useful I guess if there was a way to query "what's at this location" and get a list of links, articles, and whatever other containers you've made, which are situated there or close-by. I dunno..?
|
| Bernard from nearby-an-airport Associate, 6734 posts |
" what's at this location " Very bright idea!!! I will add a function to the module that handle locations to implement this. Actually, I will probably use your code to implement a list_nearby() function.
|
Rate this page
Posted by Eoin on Sep. 11 2004, commented by Bernard on Sep. 11 2004, (popular)
