Complex MySQL Query

Databases
1

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
2

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

where did you find the formula?

3

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

4

Hi Guys…

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

Thanks in advance.

5

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

6

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

THREAD CLOSED