MySQL BETWEEN Two Negative Numbers Returning No Results

November 8th, 2012 - Posted by Steve Marks to MySQL, Web Development.

When recently working on an application that contained a map, I wanted to get all items from a MySQL database between two latitude/longitude coordinates.

As a result my initial queries looked similar to the below:

SELECT `fieldNames`
FROM `my_table` 
WHERE 
	`latitude` BETWEEN 42.689506 AND 43.989992 
AND 
	`longitude` BETWEEN -71.922720 AND -73.122480

Looks ok right? I knew there were definitely entries in the database that fell between these coordindates, so why weren’t they coming back?

The Solution

The problem is the order in which the negative numbers have been placed. When working with negative numbers, the query requires that you put the smallest of the two numbers (the numerically larger of the negative values) first.

SELECT `fieldNames`
FROM `my_table` 
WHERE 
	`latitude` BETWEEN 42.689506 AND 43.989992 
AND 
	`longitude` BETWEEN -73.122480 AND -71.922720 

A quick swap around and the results I were expecting started to appear.

This entry was posted on Thursday, November 8th, 2012 at 6:45 am by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.
Comments...

Fear not, we won't publish this

Comments (1)
  1. rokdd says:

    thanks for posting this issue, it saved me a lot of time. funnily i am also operating with lat lng :)