hi matthewstevenkelly
i try your query but i dont get success till now...
please tell me any other suggestion or example ..
Main Topics
Browse All Topicshi experts,
i m working on gps project and i m stuck somewhere ... i have geonames in my database and i also have latitude and longitude but i dont know how to i get city or place name of that lat and long..
i m trying with this query
These are panipat (INDIA) co-ordinates..
Longitude ::: 76.968056
Latitude :::: 29.388889
I get longitude 76.9915264 and latitude 29.3356256 from my device and these co-ordinates just 5 km far from panipat but comes in panipat and not present in my database because every place has different lat and long as all knows..
so if i try this
select * from geonames where LONGI='76.9915264' AND LAT='29.3356256';
than it returns null because these co-ordinates are not original panipat co-ordinates.
please help me to out this .
thanks in advance..
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You, probably, have an access to a map data. If yes, you can find a nearest road. This road has a name. This is a street in a city. Maybe, you can do the same with the post code.
Another way is to use, for example, Google Map API:
http://code.google.com/api
htt
or something like that.
Could you post the table structure of geonames?
Increase speed by:
- Making Latitude and Longitude keys in the table
- Instead of '*' only put the fields you need (ie City name)
Worse case you could try to find close cities by decimal truncation
ie instead of
select * from geonames where LONGI='76.9915264' AND LAT='29.3356256';
do
select * from geonames where LONGI='76.9915' AND LAT='29.3356';
And use the top city returned.
But the results would not be as accurate.
hi matthewstevenkelly
i am getting result from this query but its execution is too slow ie 14.2 sec.
SELECT *, ((ACOS(SIN('29.3356256' * PI() / 180) * SIN(Latitude * PI() / 180) + COS('29.3356256' * PI() / 180) * COS(Latitude * PI() / 180) * COS(('76.9915264' - Longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM geonames HAVING distance<='10' ORDER BY distance ASC
and i try
select * from geonames where LONGI='76.9915' AND LAT='29.3356';
but it returns null;
I meant this sorry,
select * from geonames where LONGI LIKE '76.9915%' AND LAT LIKE '29.3356%';
Adding an index will significantly increase performance. Add an index to the LONGI and LAT:
CREATE INDEX long_idx ON geonames (LONGI(10));
CREATE INDEX lat_idx ON geonames (LAT(10));
The only caveat to indexes is they consume memory, but fields that are used heavily in WHERE statements should be indexed to improve performance.
hi matthewstevenkelly
i got how to increase performance but my problem is still alive..
i used your query
select * from geonames where LONGI LIKE '76.9915%' AND LAT LIKE '29.3356%';
after created index it fast executes but returns null;
bcoz the original lat and long for this place are 29.336111 and 76.979167.
but i got from my device LONGI='76.9915264' AND LAT='29.3356256';
so when i used select * from geonames where LONGI LIKE '76.9915%' AND LAT LIKE '29.3356%';
it returns null bcoz of different lat and long and when i changed manually select * from geonames where LONGI LIKE '76.97%' AND LAT LIKE '29.33%';
than it returns my place which i want.
tell me now where i wrong ?
thanks ..
Thanks for the compliment.
After creating the indexes, is this query faster? This will give you the most accurate results:
SELECT *, ((ACOS(SIN('29.3356256' * PI() / 180) * SIN(Latitude * PI() / 180) + COS('29.3356256' * PI() / 180) * COS(Latitude * PI() / 180) * COS(('76.9915264' - Longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM geonames HAVING distance<='10' ORDER BY distance ASC
In order for this query you would have to trim it down to one decimal place:
select * from geonames where LONGI LIKE '76.9%' AND LAT LIKE '29.3%';
1 degree is 69.2 miles so trimming it down to .1 degree would be 7 miles. Not sure your required accuracy.
Even better (if the first query in this post does not work) would be:
Select * from geonames where LONGI > '76.9415264' AND LONGI < '77.0415264' AND LAT > '29.2856256' AND LAT < '29.3856256';
Where you add and subtract 0.05 from the results from the device to get a 0.1 (7 mile) range...
Let me know how the first query executes.... it should be fast with the index's.
Business Accounts
Answer for Membership
by: matthewstevenkellyPosted on 2009-08-24 at 07:36:46ID: 25168719
What you need to do is something like this: http://www.zcentric.com/bl og/2007/03 / calculate _distance_ in_mysql_w i.html
Your device location would be the center point.
Use the lat and long from the device to find all the geonames that are within a minimum distance that you determine to allow them to be called a part of that city. You can then just use either the closest city or say no cities within 10km of your location, etc.
Select allOpen in new window