[wp-hackers] Sorting queries by values in custom array?

[Nathaniel Taintor]

Hi all,

I'm working on a project that's basically a craigslist clone. Visitors
choose their city on first visiting the site, which is stored in a cookie.
On browsing the site, they see posts from their own city first, then from
other cities, sorted by proximity.

I have 'City' set up as a custom post type, with custom fields for latitude
and longitude. This post type is mirrored in a custom taxonomy, kind of the
same way Mike S. was talking about recently on the list. My problem is in
figuring out an efficient way of generating the queries to show posts. As it
is now, my "find nearby cities" looks like this:

function sort_nearest_cities() {
    global $wpdb;
    $thiscity = $wpdb->get_var("SELECT id FROM wp_posts WHERE
post_title='".BCD_CITY."'"); // BCD_CITY is the value of the cookie
    $thislat = get_post_meta($thiscity,'lat',true);
    $thislon = get_post_meta($thiscity,'lon',true);
    $othercities = $wpdb->get_results("SELECT `ID`,`post_title` FROM
`wp_posts` WHERE `post_type`='city'",ARRAY_A);
    $othercities_sorted = array();
    foreach ($othercities as $othercity) {
        if ($othercity['post_title'] != BCD_CITY) {
            $otherlat=get_post_meta($othercity['ID'],'lat',true);
            $otherlon=get_post_meta($othercity['ID'],'lon',true);
            $othercities_sorted[$othercity['post_title']] = (int) sqrt(
pow((69.1*($otherlat-$thislat)),2) +
pow((69.1*($otherlon-$thislon)*cos($thislat/57.3)),2) ) ;
        }
    }
    asort ($othercities_sorted);
    return array_keys($othercities_sorted);
}

and then I loop through each of the results returned with a get_posts query
for each. It works, but its a stupid brute-force approach that won't scale
well at all.

What I'd like to do, given an array of nearby cities sorted, is somehow pass
that to the get_posts query as a sort order field. But I'm drawing a
complete blank. Is there a smart way of doing this? Maybe hooking into
posts_where or posts_join?

Sorry if this is outside the scope of this list.

Thanks,
Than