I'm using following sql code to find out 'ALL' poi closest to the set coordinates, but I would want to find out specific poi instead of all of them. When I try to use the where clause I get an error and it doesn't work and this is where I'm currently stuck, since I only use one table for all the coordinates off all poi's.
SET @orig_lat=55.4058;
SET @orig_lon=13.7907;
SET @dist=10;
SELECT
*,
3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat -abs(latitude)) * pi()/180 / 2), 2)
+ COS(@orig_lat * pi()/180 ) * COS(abs(latitude) * pi()/180)
* POWER(SIN((@orig_lon - longitude) * pi()/180 / 2), 2) )) as distance
FROM geo_kulplex.sweden_bobo
HAVING distance < @dist
ORDER BY distance limit 10;
The problem is that you can not reference an aliased column (distance
in this case) in a select
or where
clause. For example, you can't do this:
select a, b, a + b as NewCol, NewCol + 1 as AnotherCol from table
where NewCol = 2
This will fail in both: the select
statement when trying to process NewCol + 1
and also in the where
statement when trying to process NewCol = 2
.
There are two ways to solve this:
1) Replace the reference by the calculated value itself. Example:
select a, b, a + b as NewCol, a + b + 1 as AnotherCol from table
where a + b = 2
2) Use an outer select
statement:
select a, b, NewCol, NewCol + 1 as AnotherCol from (
select a, b, a + b as NewCol from table
) as S
where NewCol = 2
Now, given your HUGE and not very human-friendly calculated column :) I think you should go for the last option to improve readibility:
SET @orig_lat=55.4058;
SET @orig_lon=13.7907;
SET @dist=10;
SELECT * FROM (
SELECT
*,
3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat -abs(latitude)) * pi()/180 / 2), 2)
+ COS(@orig_lat * pi()/180 ) * COS(abs(latitude) * pi()/180)
* POWER(SIN((@orig_lon - longitude) * pi()/180 / 2), 2) )) as distance
FROM geo_kulplex.sweden_bobo
) AS S
WHERE distance < @dist
ORDER BY distance limit 10;
Edit: As @Kaii mentioned below this will result in a full table scan. Depending on the amount of data you will be processing you might want to avoid that and go for the first option, which should perform faster.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With