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.

Each post has a lat/lng value attached to it via postmeta. I'm trying to grab all posts within a bounding lat/lng value. Here's the get_posts query:

$posts = get_posts(array(
    'posts_per_page' => 100,
    'post_type' => 'place',
    'post_status' => 'publish',
    'meta_query' => array(
        array(
            'key' => 'places_lat',
            'value' => array($lat_min, $lat_max),
            'compare' => 'BETWEEN',
            //'type' => 'DECIMAL',
        ),
        array(
            'key' => 'places_lng',
            'value' => array($lng_min, $lng_max),
            'compare' => 'BETWEEN',
            //'type' => 'DECIMAL',
        ),
    ),
));

Since postmeta values are stored as strings, I figured I should be casting to DECIMAL, but it just seems to trim the decimal value from the string due to the lack of DECIMAL arguments/precision parameters.

I did notice the query treats the floats within the value array as strings, which could also be another point of failure. Running the compiled query without the quotes around each floating value works as expected.

I'll be using get_permalink() on each post. I can run a custom query outside of get_posts (via $wpdb->get_results()) to properly grab the posts within the bounding box, then loop through the posts and get_permalink, but it ends up firing an additional database query per post to build the permalink - not an ideal solution!

Any ideas?

share|improve this question
2  
A sidenote: I would not do a location query on the postmeta table, it will be hard to benefit from indexes this way. I once wrote an example that copies post geodata in a separate table with an efficient index, and does the geo queries on that table. –  Jan Fabry Apr 7 '11 at 12:36

1 Answer 1

up vote 2 down vote accepted

You can filter generated SQL and add precision parameters that you need.

Enable filters for get_posts() by adding following to query:

'suppress_filters' => false,

And:

add_filter('posts_where','cast_decimal_precision');

function cast_decimal_precision( $where ) {

    return str_replace('DECIMAL','DECIMAL(10,3)',$where);
}

Update

With Jan's suggestion:

add_filter('get_meta_sql','cast_decimal_precision');

function cast_decimal_precision( $array ) {

    $array['where'] = str_replace('DECIMAL','DECIMAL(10,3)',$array['where']);

    return $array;
}
share|improve this answer
1  
You can even use a more specific filter: get_meta_sql, since the taxonomy query part is created in _get_meta_sql(). This is also unaffected by suppress_filters, so you don't have to disable that. –  Jan Fabry Apr 7 '11 at 17:45
    
Thanks Rarst, and thanks again Jan! I think the best option in this case is moving the lat/lng values into their own table for better indexing. –  Kevin Apr 7 '11 at 23:16
    
tried both solutions and it looks like it compares them as strings with single quotes (wp 3.1.1), any ideas on that? trying raw sql did work though. –  Amit Apr 12 '11 at 23:05
1  
@Amit nothing I can really guess here... Such is usually long debug mess with query-related code. –  Rarst Apr 12 '11 at 23:29

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.