Welcome to WebmasterWorld Guest from 54.221.119.45

Forum Moderators: open

Message Too Old, No Replies

Geolocation SQL query not finding exact location

     

Iridium52

11:41 pm on Apr 21, 2010 (gmt 0)

5+ Year Member



I used to be registered here but I guess my account was inactive too long so I started a new one.

I have been testing my geolocation query for some time now and I haven't found any issues with it until now.

I am trying to search for all cities within a given radius, often times I'm searching for cities surrounding a city using that city's coords, but today I tried searching around a city, but the city itself was not found.

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 around the city of Saint-Rémi, 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 these 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 town of Saint-Rémi is missing from 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 Saint-Rémi's coords slighly by changing the last digit of the lat or long by 1, both queries work. Also, if I center the query on any of the other cities above, the searched city is in the results.

Can anyone shed some light on what may be causing my queries to not display the searched city of Saint-Rémi in the queries above? I have added a sample of the table (with extra fields removed) below.

I'm using MySQL 5.0.45, thanks in advance.

-- 
-- Table structure for table `tblcity`
--

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 ;

--
-- Dumping data for table `tblcity`
--

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);
 

Featured Threads

Hot Threads This Week

Hot Threads This Month