how to get city or place name through latitude and longitude

hi matthewstevenkelly
i try your query but i dont get success till now...

please tell me any other suggestion or example ..

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/apis/maps/

http://code.google.com/apis/maps/documentation/

or something like that.

hi matthewstevenkelly

thanks for your reply.. your query working fine but its very slow to execute..

i m using 5.1.36 MySql version please tell me how can i  fast execute my query this time it is taking 14.246 seconds.

so you can see its too slow to execute..

thanks in advance..

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;

my table structure is

CREATE TABLE geonames (`RC` VARCHAR(255), `UFI` VARCHAR(255), `UNI` VARCHAR(255), `LAT` VARCHAR(255), `LONGI` VARCHAR(255), `DMS_LAT` VARCHAR(255), `DMS_LONG` VARCHAR(255), `MGRS` VARCHAR(255), `JOG` VARCHAR(255), `FC` VARCHAR(255), `DSG` VARCHAR(255), `PC` VARCHAR(255), `CC1` VARCHAR(255), `ADM1` VARCHAR(255), `ADM2` VARCHAR(255), `POP` VARCHAR(255), `ELEV` VARCHAR(255), `CC2` VARCHAR(255), `NT` VARCHAR(255), `LC` VARCHAR(255), `SHORT_FORM` VARCHAR(255), `GENERIC` VARCHAR(255), `SORT_NAME` VARCHAR(255), `FULL_NAME` VARCHAR(255), `FULL_NAME_ND` VARCHAR(255), `MODIFY_DATE` VARCHAR(255));
                                              

1:

above are row values with header name

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 ..

and apart questioning i visit your personal website .. it is very good to see.

hi matthewstevenkelly
 
Sorry pal i was on holidays therefore i was not able to reply. Today i m trying your query as soon as i got successes than i will inform you.

thanks