Geolocation SQL query cannot find exact location

Question

I have been testing my geolocation request for some time, and so far I have not found any problems with it.

I try to search for all cities within a given radius, often when I search for cities surrounding a city using these city coordinates, but recently I tried to search the city and found that the city itself was not returned.

I have these cities as an excerpt in my database:

city latitude longitude Saint-Mathieu 45.316708 -73.516253 Saint-Édouard 45.233374 -73.516254 Saint-Michel 45.233374 -73.566256 Saint-Rémi 45.266708 -73.616257 

But when I run my query on the city of Saint-Remy, with the following query ...

 SELECT tblcity.city, tblcity.latitude, tblcity.longitude, truncate((degrees(acos( sin(radians(tblcity.latitude)) * sin(radians(45.266708)) + cos(radians(tblcity.latitude)) * cos(radians(45.266708)) * cos(radians(tblcity.longitude - -73.616257) ) ) ) * 69.09*1.6),1) as distance FROM tblcity HAVING distance < 10 ORDER BY distance desc 

I get the following results:

 city latitude longitude distance Saint-Mathieu 45.316708 -73.516253 9.5 Saint-Édouard 45.233374 -73.516254 8.6 Saint-Michel 45.233374 -73.566256 5.3 

The city of San Remy is not in the search.

So, I tried a modified query, hoping to get a better result:

 SELECT tblcity.city, tblcity.latitude, tblcity.longitude, truncate(( 6371 * acos( cos( radians( 45.266708 ) ) * cos( radians( tblcity.latitude ) ) * cos( radians( tblcity.longitude ) - radians( -73.616257 ) ) + sin( radians( 45.266708 ) ) * sin( radians( tblcity.latitude ) ) ) ),1) AS distance FROM tblcity HAVING distance < 10 ORDER BY distance desc 

But I get the same result ...

However, if I modify the slides of Saint-Remy by changing the last digit of lat or long to 1, both queries will return Saint-Rémi. Also, if I focus the query on any of the other cities above, the city you are looking for is returned in the results.

Can someone shed some light on what might cause my inquiries above so as not to display the city of Saint-Remy that I’m looking for? I added a sample table (with additional fields) below.

I am using MySQL 5.0.45, thanks in advance.

 CREATE TABLE `tblcity` ( `IDCity` int(1) NOT NULL auto_increment, `City` varchar(155) NOT NULL default '', `Latitude` decimal(9,6) NOT NULL default '0.000000', `Longitude` decimal(9,6) NOT NULL default '0.000000', PRIMARY KEY (`IDCity`) ) ENGINE=MyISAM AUTO_INCREMENT=52743 DEFAULT CHARSET=latin1 AUTO_INCREMENT=52743; INSERT INTO `tblcity` (`city`, `latitude`, `longitude`) VALUES ('Saint-Mathieu', 45.316708, -73.516253), ('Saint-Édouard', 45.233374, -73.516254), ('Saint-Michel', 45.233374, -73.566256), ('Saint-Rémi', 45.266708, -73.616257); 

Answer 1

In your first request, I believe that you have inverted longitudes in subtraction. Spherical law of cosines:

 d = acos(sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(long2−long1))*R 

If lat1 is replaced by tblcity.latitude, long1 should be replaced by tblcity.longitude. I think you accidentally replaced long2 in your request. Is this work improving?

 SELECT tblcity.city, tblcity.latitude, tblcity.longitude, truncate((degrees(acos( sin(radians(tblcity.latitude)) * sin(radians(45.266708)) + cos(radians(tblcity.latitude)) * cos(radians(45.266708)) * cos(radians(-73.616257 - tblcity.longitude) ) ) ) * 69.09*1.6),1) as distance FROM tblcity HAVING distance < 10 ORDER BY distance desc 

I haven't looked at your second request yet, but hope this helps.

Answer 2

You use the formula "spherical law of cosines", which allows a rounding error at small distances (for example, zero!) - see this discussion . The long-running expression that feeds into acos () evaluates to just over 1.0, which is beyond the scope.

Here's the problem illustrated by using Python to do the calculations:

 >>> from math import sin, cos, acos, radians >>> lat = radians(45.266708) >>> long_expression = sin(lat) * sin(lat) + cos(lat) * cos(lat) * cos(0.0) >>> repr(long_expression) '1.0000000000000002' >>> acos(long_expression) Traceback (most recent call last): File "<stdin>", line 1, in <module> ValueError: math domain error >>> 

MySQL seems to replace NULL instead of throwing an exception. I have little knowledge of MySQL, but you can overcome this by doing something like ifnull(acos(long_expression), 0.0) or coalesce(acos(long_expression), 0.0) .

Alternatively, you can use the haversine formula, which shifts the rounding problem from your door step to the opposite side of the earth.

Update: I tested this formula in Python to calculate the distance between points and one point for each of the 37,582 unique (lat, lon) 2 -tuples in the US zip file.

Of them:

• 31591 (84.1%) produced zero distance
• 4244 (11.3%) produced a distance of 9.5 cm.
• 831 (2.2%) produced a distance of 13.4 cm.
• 916 (2.4%) produced a value of "cos" 1.0000000000000002, which would throw an exception in acos () if it were not and was not detected.

It seems that explicit testing for lat1 = lat2 and lon1 = lon2 and avoiding the formula in this case (just use zero) may be a good idea - this will give a consistent answer and avoid bewilderment.