Complex MySQL Query

Hi Guys…

I have been asked to write a MySQL query that will list locations within a certain radius of a given longitude and latitude. However, I am having a problem; the query below does not get all locations listed withn the given radius (i.e. 25km). It only lists 1 matching row. I tried changing the radius to 10000 just to see if it returned more rows, but unfortunately, it still only returned 1 row.

Any ideas what could be wrong with it?


SELECT *, 6371.04 * ACOS(COS(PI() /2 - RADIANS(90 - latitude)) 
	* COS(PI() /2 - RADIANS(90 - '57.1311')) 
    * COS(RADIANS(longitude) - RADIANS('-2.1225')) 
    + SIN(PI() /2 - RADIANS(90 - latitude)) 
    * SIN(PI() /2 - RADIANS(90 - '57.1311'))) 
AS distance FROM uk_postcode_towns 
	WHERE (6371.04 * ACOS(COS(PI() /2 - RADIANS(90 - latitude)) 
    * COS(PI() /2 - RADIANS(90 - '57.1311')) 
    * COS(RADIANS(longitude) - RADIANS('-2.1225')) 
    + SIN(PI() /2 - RADIANS(90 - latitude)) 
    * SIN(PI() /2 - RADIANS(90 - '57.1311'))) <1) 
GROUP BY postcode 
	HAVING distance < '25' 
ORDER BY distance

try it with numbers instead of strings, i.e. remove the quotes

where did you find the formula?

Hi - I tried without quotes, but getting the same result.

I found the original formula here: http://www.sitepoint.com/forums/php-34/find-closest-location-near-your-address-482470.html

Hi Guys…

Im still stuck with this, any idea what’s wrong with the query im using?

Thanks in advance.

the problem isn’t the query, it’s what your doing with the hashref… use a while statement, that should fix it…

I think it the OP hasn’t fixed the problem in 7 months, I would guess he took another approach.

THREAD CLOSED