Mysql use 2 functions in 1 query

I’m trying to use 2 same functions in 1 query which is this

    select *,  ST_Distance_Sphere( point(                 31.00000, 35.00000),                 point(latitude_0, longitude_0)) * .0001                 as `distance_in_km` ,            ST_Distance_Sphere( point (                 31.00000, 35.00000),                 point(dest_latitude_0, dest_longitude_0)) * .0001                 as `distance_in_km`                 from `most_places_used`                  having `distance_in_km` <= '0.001'  order by `distance_in_km` asc Limit 1 

This uses only the first function and give right result for the first latlngs only.

I need the row which is almost near to the 2 latlngs (the latlngs is just example don’t care if it’s wrong <3 ) I want it to Give me the row that equals the functions suppose to be the functions latlngs near for the 4 column latlngs

This is the column names.. as in the function ST_Distance_Sphere

(latitude_0, longitude_0, dest_latitude_0, dest_longitude_0) 

I need something similar to this query do the same need or the fix for it <3

Add Comment
1 Answer(s)

You are giving the same alias to two different columns, which makes your query ambiguous about which one you want to use to filter and order. I would recommend two different aliases and least() instead:

select * from (     select          mpu.*,           ST_Distance_Sphere(              point(31.00000, 35.00000),             point(latitude_0, longitude_0)         ) * 0.0001 as distance_in_km1,         ST_Distance_Sphere(              point (31.00000, 35.00000),             point(dest_latitude_0, dest_longitude_0)         ) * 0.0001 as distance_in_km2     from most_places_used mpu ) t where least (distance_in_km1, distance_in_km2) <= 0.001 order by least (distance_in_km1, distance_in_km2) limit 1 

Note that I used a subquery rather than a having clause: this is a more standard and readable way to proceed, for no performance penalty anyway.

If you don’t need to differentiate the two computations in the resultset, you can move least() directly to the subquery:

select * from (     select          mpu.*,           least(             ST_Distance_Sphere(                  point(31.00000, 35.00000),                 point(latitude_0, longitude_0)             ),             ST_Distance_Sphere(                  point (31.00000, 35.00000),                 point(dest_latitude_0, dest_longitude_0)             )         ) * 0.0001 as distance_in_km     from most_places_used mpu ) t where distance_in_km <= 0.001 order by distance_in_km limit 1 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.