homepage Welcome to WebmasterWorld Guest from 54.161.247.22
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Geolocation SQL query not finding exact location
Iridium52




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

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

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved