Search code examples
mysqljoingeocodingpostal-code

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

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

Solution

  • 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;