CODEWITHSUNDEEP
mysqlsqlgeolocation
lukeseager
lukeseager

Reputation: 2615

SQL to pull locations within NE/SW bounds

I'm trying to get an SQL query to pull events that are within the bounds of a city, dynamically. I have a list of towns/cities, with Google Geocoding I can get the North East and South West bounds of each city.

I've seen code explaining how to check if a location (by lat and long) is within bounds for places like the USA, but I've read that this wouldn't work for the entire world.

I've tried the formula below but it doesn't seem to work:

AND ( 
    ($swLat < $neLat AND lat BETWEEN $swLat AND $neLat) 
        OR 
    ($neLat < $swLat AND lat BETWEEN $neLat AND $swLat)
    AND 
    ($swLng < $neLng AND lng BETWEEN $swLng AND $neLng) 
        OR 
    ($neLng < $swLng AND lng BETWEEN $neLng AND $swLng)
)

Just tried the first suggestion below, this is the output I get:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN lat_lng_post ON wp_posts.ID = lat_lng_post.post_id 
WHERE 1=1 
AND wp_posts.ID IN (10027,10095,...,10657) 
AND wp_posts.post_type = 'event' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND lat_lng_post.lat = lat 
AND lat_lng_post.lng = lng 
AND ( 
       ( 
           (52.385999 < 52.5688762) AND (lat BETWEEN 52.385999 AND 52.5688762) 
           OR 
           (52.5688762 < 52.385999) AND (lat BETWEEN 52.5688762 AND 52.385999) 
        ) AND (
           (-2.0174336 < -1.7098294) AND (lng BETWEEN -2.0174336 AND -1.7098294) 
           OR 
           (-1.7098294 < -2.0174336) AND (lng BETWEEN -1.7098294 AND -2.0174336) 
        ) 
    )
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

EDIT 2 From barryhunter's answer, I have this function which is called on the query:

function bounds_query($bounds) {

    $swLat = $_SESSION['search']['SW'][0];
    $swLng = $_SESSION['search']['SW'][1];
    $neLat = $_SESSION['search']['NE'][0];
    $neLng = $_SESSION['search']['NE'][1];

    $bounds .= ' AND (lat BETWEEN '.$swLat.' AND '.$neLat.')';
    if ($neLng < $swLng) {
      $bounds .= ' AND NOT (lng BETWEEN '.$neLng.' AND '.$swLng.')';
    } else {
      $bounds .= ' AND (lng BETWEEN '.$swLng.' AND '.$neLng.')';
    }

    return $bounds;
}

This outputs the following SQL:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN lat_lng_post 
ON wp_posts.ID = lat_lng_post.post_id 
WHERE 1=1 
AND wp_posts.ID IN (10060,10293,...,10657) 
AND wp_posts.post_type = 'event' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND lat_lng_post.lat = lat 
AND lat_lng_post.lng = lng 
AND (lat BETWEEN 52.385999 AND 52.5688762) AND (lng BETWEEN -2.0174336 AND -1.7098294)      
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

Upvotes: 0

Views: 807

Answers (1)

barryhunter
barryhunter

Reputation: 21091

The issue is when your BBOX straddles the dateline, I noted it here: http://sphinxsearch.com/forum/view.html?id=8969 in relation to sphinx, but the same applies to mysql.... 

$where = "(lat BETWEEN $swLat AND $neLat)";
if ($neLng < $swLng) {
  $where .= " AND NOT (lng BETWEEN $neLng AND $swLng)";
} else {
  $where .= " AND (lng BETWEEN $swLng AND $neLng)";
}

Using a negative filter to exclude items between the east and the west (because the east longtitde value is now on the west of the globe0 - leaving only the results on the narrow stop straddling the dateline.

Franky though you shouldn't run into the issue much, as there shouldn't be any cities straddling the dateline!

( would argue this code is cleaner, as it avoids the redundant check on latitude, and moves it to PHP rather than mysel, but you could also just fix your code by adding the NOT logic to the (lng BETWEEN $neLng AND $swLng) clause)

Upvotes: 1

Related Questions