MySQL calculating distance (simple solution)

I have the next query for getting addresses within a given distance and given postal code. Distance is calculated, based upon longitude and latitude data.

In this example i have replaced the user-input for just values (lat=52.64, long=6.88 en desired distance=10km)

the query:

SELECT *,
ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2) + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
FROM lp_relations_addresses distance
WHERE distance < 10
 ORDER BY `distance`  DESC

gives unknown column distance as error message. when leaving out the where clausule i get every record of the table including their calculated开发者_StackOverflow社区 distance. In this case i have to fetch the whole table.

How do i get only the desired records to fetch??

Thanks in advance for any comment!

---

You can't reference an alias in the select clause from another part of the sql statement. You need to put the whole expression in your where clause:

WHERE
    ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2)
        + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) < 10

A cleaner solution would be to use a sub-query to generate the calculated data:

  SELECT *, distance
    FROM (
       SELECT *,
           ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2)
               + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
           FROM lp_relations_addresses
       ) d
   WHERE d.distance < 10
ORDER BY d.distance DESC

Demo: http://www.sqlize.com/q96p2mCwnJ

---

As mellamokb notes, you can't reference column aliases in the WHERE clause. You can, however, do it in a HAVING clause:

SELECT *,
  ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2) +
         POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
FROM lp_relations_addresses
HAVING distance < 10
ORDER BY distance DESC

Ps. If you have lots of addresses, you might want to consider optimizing the query by ruling out some of them early. For example, with suitable indexes, the following version might be considerably faster:

SELECT *,
  ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2) +
         POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
FROM lp_relations_addresses
WHERE latitude > '52.64' - 10 / (69.1/1.61)
  AND latitude < '52.64' + 10 / (69.1/1.61)
  AND longitude > '6.88' - 10 / (53/1.61)
  AND longitude < '6.88' + 10 / (53/1.61)
HAVING distance < 10
ORDER BY distance DESC

---

You are aliasing the calculation as 'distance', but you are also aliasing table 'lp_relations_addresses' as 'distance'. Try giving them a different name like this:

SELECT *,
ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2) + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
FROM lp_relations_addresses addr
WHERE distance < 10
ORDER BY `distance`  DESC