WP Query/Meta Query BETWEEN latitude and longitude Cast

I'm going a little insane and need help. I am using WP Query to get posts from a custom post type between a min lat/max lat and between a min lng/max lng.

It works BUT some locations work with type => NUMERIC and others work with type => CHAR. It makes no sense.

Here is the relevant query bit:
// Set meta_query
$lat_lng_meta_query =
array (
'relation' => 'AND',
array(
'key' => '_ticr_location_latitude',
'value' => array($lat_min, $lat_max),
'compare' => 'BETWEEN',
//'type' => 'NUMERIC'
),
array(
'key' => '_ticr_location_longitude',
'value' => array($lng_min, $lng_max),
'compare' => 'BETWEEN',
//'type' => 'NUMERIC'
)
);

If I comment out type, it defaults to CHAR. I have been searching and can't seem to find anyone who has had this issue. thanks!

  • Milan

    Hi @Titanium Creative,

    I hope you are having an awesome day!!.

    The problem you mentioned occurs because of type mismatch. Try to typecast value of $lng_min and $lng_max to float while inputting them to value array.

    Here is what I mean...

    array(
              'key' => '_ticr_location_latitude',
              'value' => array( floatval( $lat_min ), floatval ( $lat_max ) ), //Type cast value to float.
              'compare' => 'BETWEEN',
              'type' => 'NUMERIC' //Make this to numeric.
    ),

    I think this will help you. Test this out and let me know how it goes @Titanium Creative. :slight_smile:

    Warmest Regards,
    Milan Savaliya
    Support Staff ( WPMUDEV )

  • Titanium Creative

    Thank you for your response. You would think that would work. No joy. I have tried casting these things every way I can imagine. I even inserted SQL cast to DECIMAL(13,10) in the value field. When I use NUMERIC as type, WP casts to SIGNED.

    What is truly strange about all of this is I have two clusters of locations I am testing. One in California and one in NY. If I let it cast to CHAR by default or explicitly, CA is found but not NY. ANY numeric casting I have tried yields NY but not CA. It could be coincidence (and probably is), but it seems to be regionally oriented.

    The post latlng is derived from Google Maps API and is stored in post_meta. All of the latlng are handled the same way. I even tried casting the input data (knowing it would revert to char in the database.) The origin is generated by Google Maps API but for testing, I am pasting coordinates in to the input for the ajax call.

    The essential point is that all the data is being handled the same way so, theoretically, there should be no difference. Casting should eliminate any differences. I can't figure out why some work with CHAR and others work with NUMERIC.

    I had even done a work-around where I tested for a match either by CHAR or by NUMERIC. Worked great on the dev but with a larger table the response dropped to 30 sec. So I've got to get this solved.

    ANY help or thoughts will be greatly appreciated.

  • Titanium Creative

    I have tried:
    (string)$var
    type => CHAR // explicit cast. this is the default anyway

    floatval($var)
    type => NUMERIC // creates a cast to SIGNED

    floatval($var)
    type => DECIMAL

    floatval($var)
    type => DECIMAL(13,10) // -999.9999999999

    CAST($var AS DECIMAL(13,10))
    type => DECIMAL(13,10)

    and others I have forgotten in the fog.

    The result is always the same. CA shows up with string based solutions and NY shows up with numeric based solutions.

  • Titanium Creative

    Progress. Maybe.

    I have extended the search range to 300 miles getting results that make me think it has something to do with the latlng I am getting from Google.

    CHAR search for CT will show locations in ME. Same search in ME shows no locations.
    NUMERIC search in ME will show the ME locations

    CHAR search for SC will show Charleston, SC locations. Same search for Charleston, SC will show no locations.

    Checking into that. Any thoughts are welcome.

  • Titanium Creative

    HA!! OMG!! 3 days and I finally got it by manually building the WP QUERY query set by step!

    Who knew cos turns negative?? Not me! Well. I guess I did once but it's been a while...

    I am using:
    // Get Max and Min Longitude
    $lng_deg = $max_distance/(abs(cos($map_center[0])*69));
    $lng_min = $map_center[1] - $lng_deg;
    $lng_max = $map_center[1] + $lng_deg;

    to get the min and max longitude. You will notice the "abs" function in the first line. I didn't have that in there before. The result was that the BETWEEN values would flop when cosign went negative. That set up a never true situation where the value had to be greater than the high number and lower than the low number.

    I added abs() and now it works. I suppose I will also have to check for 0 to avoid division by 0, but hey. there you go.

    Thanks, Milan, for your input! Hope this helps someone else out there who is missing the obvious.

  • Titanium Creative

    UPDATE: I found instances where NUMERIC was not working. I haven't had had time to determine the exact reason for the failure, but changing to DECIMAL fixed it.

    so the args for that bit are:

    $lat_lng_meta_query =
    array (
    'relation' => 'AND',
    array(
    'key' => 'location_latitude',
    'value' => array($lat_min, $lat_max),
    'compare' => 'BETWEEN',
    'type' => 'DECIMAL'
    ),
    array(
    'key' => 'location_longitude',
    'value' => array($lng_min, $lng_max),
    'compare' => 'BETWEEN',
    'type' => 'DECIMAL'
    )
    );

    Thanks!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.