Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'll just like to add that I have a current working solution in place and works well but I'm looking for ideas of being able to do it better or cleaner if possible.

We have hundreds of properties in a custom post type with metakey/metavalue holding the properties longitude and latitude co-ordinates.

We allow a visitor to type in a location and a radius to search for those properties in that area. Kind of like a store locator. Here is how I'm doing it at the moment:

// Work out square radius
if(!empty($_SESSION['s_property_radius'])) {$dist = $_SESSION['s_property_radius'];}else{$dist = 50;}
$orig_lat = $_SESSION['s_property_address_lat'];
$orig_lon = $_SESSION['s_property_address_lng'];
$lon1 = $orig_lon - $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lon2 = $orig_lon + $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lat1 = $orig_lat - ( $dist / 69 );
$lat2 = $orig_lat + ( $dist / 69 );

// Compile a map search query to get all property ID's.
$mapsearchquery = "
            SELECT `t`.`ID`
            , 3956 * 2 * ASIN( SQRT( POWER( SIN( ( ".$orig_lat." - CAST(`t`.`property_address_lat` AS DECIMAL(9,6)) ) * pi() / 180 / 2), 2 ) + COS( ".$orig_lat." * pi() / 180) * COS( CAST(`t`.`property_address_lat` AS DECIMAL(9,6)) * pi() / 180 ) * POWER( SIN( ( ".$orig_lon." - CAST(`t`.`property_address_lng` AS DECIMAL(9,6)) ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance` 
            FROM (

            SELECT `$wpdb->posts`.`ID`
            , MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = 'chb_homes_for_sale_address_longitude' THEN `$wpdb->postmeta`.`meta_value` END ) AS `property_address_lng`
            , MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = 'chb_homes_for_sale_address_latitude' THEN `$wpdb->postmeta`.`meta_value` END ) AS `property_address_lat`
            FROM `$wpdb->posts` 
            LEFT JOIN `$wpdb->postmeta` ON ( `$wpdb->posts`.`ID` = `$wpdb->postmeta`.`post_id` ) 
            WHERE `$wpdb->posts`.`post_status` = 'publish' 
            AND `$wpdb->posts`.`post_type` = 'homes-for-sale' 
            GROUP BY `$wpdb->posts`.`ID` 
            HAVING CAST(`property_address_lng` AS DECIMAL(9,6)) BETWEEN '".$lon1."' AND '".$lon2."' AND CAST(`property_address_lat` AS DECIMAL(9,6)) BETWEEN '".$lat1."' AND '".$lat2."'

            ) AS `t`
            HAVING `distance` < ".$dist."
        ";

// Just get the ID's
$mapsearchresults = $wpdb->get_col($mapsearchquery);

This returns an array of all post id's that are within the users search parameters. I then past that array to WP_Query to only show posts that have the above ID's using post__in. Also I can do other search criteria on the WP_Query afterwards.

In the above query I do a square radius check and I know this could be easily done in WP_Query using meta_query. But where I get stuck is running the circle radius check. Is there a way I can wrap this in WP_Query?

Basically what I'm after is trying to do this all in one rather than doing two lots of queries.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

I would duplicate the coordinates of the posts in a separate table (post_id, lat, lon), with an index on (lat, lon). With all the joins and the casts I doubt the database can use an efficient index with your query.

I once wrote an answer to a similar question using this approach.

share|improve this answer
    
Yeah I saw this. However my code will be somewhat different as my setup in the backend is a little more complicated. But I like the idea of saving the lat lon in another table. For me I think I'll have to add an extra column that stores the post type as I have multiple post types that might have lat lon attached. –  Brady May 5 '11 at 12:48
    
Jan how would I correctly index my separate table? Also taking into account I'll add an additional column for post type. thanks –  Brady May 5 '11 at 12:49
    
@Brady: Well, if you query for posts that have post type "x" and are between certain boundaries for lat and lon, an index on (lat, lon, post_type) should be enough. If you read the database much more than you write to it, I don't think you can have too many indexes :-) MySQL's EXPLAIN will be your friend here, but database optimization can also be a good question for the regular Stack Overflow site. –  Jan Fabry May 5 '11 at 12:55
    
@Jan: OK I'll put a post up on stack overflow to best optimise this table. But back to my question... By using a different table to do the GEO Data search all that I'm changing is $mapsearchquery to a more efficient way. Is it at all possible to incorporate $mapsearchquery into WP_Query or the way I'm doing it now using post__in the best way? –  Brady May 5 '11 at 13:14
    
@Brady: We'll always have to get the post IDs from the secondary table first, and then use them to get the full post information. You can do this in one query (WHERE post_id IN (SELECT post_id FROM secondary_table WHERE [geo_stuff]), or with a join), or first query them and then pass them to the query (WHERE post_id IN (1, 2, 4, 6)), and you can do the latter in an integrated way or just in two steps using post_in. If this was for a plugin that you distribute to others I would integrate it for simplicity, otherwise... I don't think I would spend the extra effort to "clean it up". –  Jan Fabry May 5 '11 at 13:19
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.