Getting latitude and longitude from a postcode table (MySql) and joining to another table

Question

I have a charities table with fields: charity, postcode and a postcodes table with fields: postcode, lat, lng

I want to POST a postcode from a web page and find the nearest charities

I'm something of a mysql beginner so I'm a bit lost, but I've been trying various ideas with joins and sub queries none of which work (I either get syntax errors or 'Operand should contain 1 column' with variations on the code below) I've got

Select charity,postcode,
 ( 
   (Select lat as lat2, lng as lng2
    from postcodes
    where postcode='WN8'
    )

3959 * acos( cos( radians(lat2) ) * cos( radians( lat ) ) * 
cos( radians( lng ) - radians(lng2) ) + 
sin( radians(lat2) ) * sin( radians( lat ) ) ) 
  )
AS distance 
FROM postcodes  
JOIN   Charities on charities.postcode=postcodes.postcode
HAVING distance < 30 ORDER BY distance LIMIT 0 , 30;

I've seen lots of examples on here where lat2 and lng2 are obtained from posted values but not from a table in the db.

p.s 'where postcode='WN8' in the example is just for testing

Check this, it looks quite similar to what you need http://blog.loftdigital.com/blog/geolocation-and-sql — Zdenek Machek, Nov 21 '12 at 10:16

Kickstart · Accepted Answer · 2012-11-22T11:14:44.467

Not sure what error you are getting with the above SQL.

However try this minor tweak and let us know what errors you get

SELECT charity, postcode,
(3959 * acos( cos( radians(CustPostcode.lat) ) * cos( radians( postcodes.lat ) ) * 
cos( radians( postcodes.lng ) - radians(CustPostcode.lng) ) + 
sin( radians(CustPostcode.lat) ) * sin( radians( postcodes.lat ) ) ) 
  ) AS distance 
FROM postcodes  
INNER JOIN Charities ON charities.postcode=postcodes.postcode
CROSS JOIN  (SELECT lat, lng FROM postcodes WHERE postcode='WN8') CustPostcode
HAVING distance < 30 
ORDER BY distance 
LIMIT 0 , 30;

If you want to know the nearest 30 postcodes and the distances from each charity then something like this would do the job (not tested so excuse any typos).

SELECT charity, Charities.postcode, Postcodes.postcode, PostcodeDistance.distance
FROM Charities
CROSS JOIN Postcodes
INNER JOIN (SELECT PC1.postcode AS postcode1, PC2.postcode AS postcode2, (3959 * acos( cos( radians(PC1.lat) ) * cos( radians( PC2.lat ) ) * 
cos( radians( PC2.lng ) - radians(PC1.lng) ) + 
sin( radians(PC1.lat) ) * sin( radians( PC2.lat ) ) ) 
  ) AS distance 
FROM postcodes PC1
CROSS JOIN postcodes PC2) PostcodeDistance
ON Charities.postcode = PostcodeDistance.postcode1
AND Postcodes.postcode = PostcodeDistance.postcode2
HAVING distance < 30 
ORDER BY distance 
LIMIT 0 , 30;

This should find you the charities within 30 miles

SELECT charity, Charities.postcode, PostcodeDistance.distance
FROM Charities
INNER JOIN (
SELECT PC2.postcode AS postcode2, (3959 * acos( cos( radians(PC1.lat) ) * cos( radians( PC2.lat ) ) * 
cos( radians( PC2.lng ) - radians(PC1.lng) ) + 
sin( radians(PC1.lat) ) * sin( radians( PC2.lat ) ) ) 
) AS distance 
FROM postcodes PC1
CROSS JOIN postcodes PC2
WHERE PC1.postcode='WN8'  
) PostcodeDistance
ON Charities.postcode = PostcodeDistance.postcode2
WHERE PostcodeDistance.distance < 30 
ORDER BY PostcodeDistance.distance 
LIMIT 0 , 30;

Brilliant - that works many thanks. I've got 300K charities and 3k postcodes (both tables are MyISAM). I just tried the sql in FlyspeedSQL and it took 109 seconds - is there any way it can speeded up? — Phil Barnett, Nov 21 '12 at 13:18
Not really. Problem is the shear number of calculations you land up having to do. You are doing roughly 900 million calculations which will take time (assuming you are working out the distances between 3k postcodes and 300k charities). However as you have many charities per postcode it might be best to calculate the distances between ALL postcodes and then join that to the charities. Amended the answer with a suggestion for this — Kickstart, Nov 21 '12 at 14:26
I very much appreciate the help - I ran your amended query - I think something isn't quite right, with my test abridged charities table (just 30 records) it took 83 seconds - surely it's missing the WHERE postcode='WN8' part? — Phil Barnett, Nov 21 '12 at 14:59
The query was to get all the charities and the distances to each postcode from the charities. Can you put in English (rather than code) exactly what you are trying to get? Do you just want the 30 charities that are closest to a postcode? If so I have added another suggestion. — Kickstart, Nov 21 '12 at 15:51
I want a user of the web page to input a postcode and for a list of the 30 nearest Charities to be then displayed on the page — Phil Barnett, Nov 21 '12 at 16:37
The (marginally changed) last example above should give you what you need then. It could be speeded up by calculating the distances in advance and storing them on a table. Case of exchanging processing for memory. — Kickstart, Nov 22 '12 at 11:16

Getting latitude and longitude from a postcode table (MySql) and joining to another table

1 Answers1