MySQL查询以确定数字是否大于包含负值的VARCHAR

Question

For a project, I'm storing location points in my MySQL database. The latitude and longitude data for each point is stored as a varchar. It must remain a varchar for reasons I won't get in to. For each new point, I'm trying to perform a search to see if there's already any point in my database within a .000300 latitude/longitude radius of the new point.

My PHP code is as follows: ($lat and $long are the new point's latitude and longitude)

$lat1 = $lat - 0.000300;
$lat2 = $lat + 0.000300;
$long1 = $long - 0.000300;
$long2 = $long + 0.000300;

$sql = "SELECT * FROM `DataBase`.`DataBase_data` WHERE `DataBase_data`.`Type`='Point' AND `DataBase_data`.`Lat`>'$lat1' AND `DataBase_data`.`Lat`<'$lat2' AND `DataBase_data`.`Long`>'$long1' AND `DataBase_data`.`Long`<'$long2'";
$query = mysql_query($sql);
echo mysql_error();

The code works correctly when both latitude and longitude are positive numbers. However, when one (or both) of them are negative numbers, the search doesn't work. I think it has something to do with doing a "greater than" and "less than" comparison with a varchar containing a - sign, but I'm not positive. Any ideas about how to fix the problem would be greatly appreciated.

Answer 1

This is your query

SELECT d.*
FROM `DataBase`.`DataBase_data` d
WHERE d.`Type`='Point' AND d.`Lat`> '$lat1' AND d.`Lat`<'$lat2' AND
      d.`Long`>'$long1' AND d.`Long`<'$long2';

My first suggestion is to do the comparison as numbers. You can convert to a number easily in MySQL by adding 0:

SELECT d.*
FROM `DataBase`.`DataBase_data` d
WHERE d.`Type` = 'Point' AND (d.`Lat` + 0) > $lat1 AND (d.`Lat` + 0) < $lat2 AND
      (d.`Long` + 0) > $long1 AND (d.`Long` + 0) < $long2;

Does this fix your problem?