MySQL - finding negative longitude values ​​with BETWEEN

Question

I found a lot of similar messages and even tried to figure out how to handle negative values ​​in MySQL, but to no avail.

I have a website where I use Google Maps, and as a performance improvement, I limit the markers that are drawn on the map to those that are on the borders of the map.

I would like to develop a query that will work with positive or negative latitude and longitude values.

For the database:

 latitude FLOAT( 10, 6 ) longitude FLOAT( 10, 6 ) 

Request:

 SELECT * FROM `table` WHERE `latitude` BETWEEN 47.926930 AND 47.929806 AND `longitude` BETWEEN -97.077303 AND -97.083997 

If I omit the BETWEEN clause for longitude , I get results, albeit incorrect with the longitude constraint.

I tried this:

 AND -`longitude` BETWEEN ABS( -97.077303 ) AND ABS( -97.083997 ) 

Which works, but only for negative longitude values.

Does longitude need to be checked if its negative?

Answer 1

You can also use the greatest() and least() functions, so you don't have to worry about parameters. For example:

 SELECT * FROM table WHERE latitude BETWEEN least(@lat1, @lat2) AND greatest(@lat1, @lat2) AND longitude BETWEEN least(@lon1, @lon2) AND greatest(@lon1, @lon2) 

Answer 2

between expects the format to be somefield BETWEEN lowervalue AND highervalue . Your negative longitudes have higher / lower values. It should be

 AND longitude BETWEEN -97.083997 AND -97.077303 

negative 97.08 is actually lower than negative 97.07

Answer 3

Check how negative numbers are displayed on the number line. :)

 SELECT * FROM `table` WHERE `latitude` BETWEEN 47.926930 AND 47.929806 AND `longitude` BETWEEN -97.083997 AND -97.077303