Geo Search using Haversine formula, distinct not working

[eluser]mabright[/eluser]
I am using the below script which works fine to query all regions in a specified radius of my input latitude and longitude. However my database has duplicate city/state pairs due to different zip codes and the query returns duplicate cities. I tried to use distinct but since the query has to be ordered by distance, I could not get this to work. Any suggestions.

Note: Table contains columns city, region(state), country, zip, latitude and longitude.

Code:

SELECT `city`, `region`, 3956 * 2 * ASIN(SQRT(POWER(SIN((
37.4404 - abs(`latitude`)) * pi()/180 / 2),2) + COS(37.4404 * pi()/180 ) * COS(abs(`latitude`) *
pi()/180) * POWER(SIN((-121.8705 - `longitude`) * pi()/180 / 2), 2) )) as distance
FROM `locationdb`
having distance < 20
ORDER BY distance limit 10

Here are my current results. I have logic in my PHP to loop through the result and remove duplicates but that is a sloppy way of removing the duplicates and since the cities are not in order, the logic does not always work.

--------------------------------------------
city region distance Ascending
--------------------------------------------
Milpitas CA 0
San Jose CA 4.05605867292861
San Jose CA 4.27379165682789
San Jose CA 4.72803292240825
San Jose CA 5.10657017304142
Fremont CA 5.37310902047679
Santa Clara CA 5.86235168930009
San Jose CA 6.31906928616225
Alviso CA 6.57871062331007
Fremont CA 6.7615683089475
---------------------------------------------

[eluser]alexedwards[/eluser]
Ah, fun!

So ultimately, you want a list containing the nearest 10 cities from a given point, right?

I'd take an average of the distances between your point and each entry for a city in your file (i'm guessing zip code) to tell you the average distance between your point and that city. So assuming your table is called 'cities' something like this should work:

Code:

SELECT city, region, AVG([distance calc logic]) AS distance
FROM cities
GROUP BY city, region
ORDER BY distance
LIMIT 10

So, if you want to keep it Pythagorean, perhaps:

Code:

SELECT city, region,
AVG(SQRT(POW(your_lat-latitude,2)+POW(your_long-longitude,2))) AS distance
FROM cities
GROUP BY city, region
ORDER BY distance
LIMIT 10

or with your original code:

Code:

SELECT city, region,
AVG(3956 * 2 * ASIN(SQRT(POWER(SIN((37.4404 - abs(`latitude`)) * pi()/180 / 2),2) + COS(37.4404 * pi()/180 ) * COS(abs(`latitude`) * pi()/180) * POWER(SIN((-121.8705 - `longitude`) * pi()/180 / 2), 2) ))) AS distance
FROM cities
GROUP BY city, region
ORDER BY distance
LIMIT 10

Hope that helps

[eluser]mabright[/eluser]
Alex,

Thanks for the input. I now have the script working as desired. I prefer to have the cities within 20 miles but I guess the first 10 entries works out to be the same in most scenarios.