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?

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?

• wp-query
• post-meta
• get-posts

• 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 Commented Apr 7, 2011 at 12:36

3 Answers 3

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;
}

As of 3.8 (see track) the precision can be added to the cast type like so:

$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(10,3)',
        ),
        array(
            'key' => 'places_lng',
            'value' => array($lng_min, $lng_max),
            'compare' => 'BETWEEN',
            'type' => 'DECIMAL(10,3)',
        ),
    ),
));

WordPress treats all 'value' as strings and adds single quotes to them in the final query, so sql is also forced to treat them as strings and not numbers. You can remove these quotes with a filter:

add_filter('get_meta_sql', function($data) {
  $regex = "/'(-?\d+\.\d+)'/";
  $data['where'] = preg_replace($regex, "$1", $data['where']);
  return $data;
});

But also make sure you have

'type' => 'DECIMAL(10,6)'

or similar