Use result of substring function to compute values for multiple columns

Question

I am trying to run to find the closest point with a long and lat. Which works fine is i have separate value stored in my db, however i have a single piped string, which i am able to split with a substring index. However when i try and combine these functions in my select query i am not getting any joy.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 1), '|', -1) as 'lat',
SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 2), '|', -1) as 'lng',
title,
( 6371 * acos( cos( radians(51.527351) ) * cos( radians( 'lat') ) * cos( radians( 'lng' ) - radians(0.765544) ) + sin( radians(51.527351) ) * sin( radians( 'lat' ) ) ) ) AS distance 
FROM locations HAVING distance < 500
ORDER BY distance
LIMIT 0 , 20

Can anyone shed some light?

Provide some data, preferrably on [SQL Fiddle](http://www.sqlfiddle.com/), and be more explicit about what *“not getting any joy”* means in terms of error behaviour. Also note [this answer](http://stackoverflow.com/a/11899326) about how you might use an approximate formula instead of the full haversine formula. — MvG, Oct 04 '12 at 12:26
I've [edited](http://stackoverflow.com/posts/12727009/revisions) your title to better [represent](http://meta.stackexchange.com/questions/146975) where your actual problem lies. — MvG, Oct 04 '12 at 14:06

score 1 · Accepted Answer · answered Oct 04 '12 at 12:29

radians( 'lat')

In this part of the query, 'lat' is a string, not a column name or similar. When passing that string to radians, it will be converted to a number, in this case to zero. So you're converting 0 to radians.

You cannot use one column as the input to the computation of the value of another column. You either have to repeat the expression, or use a subquery like this:

SELECT lat, lng, title,
  (6371 * acos(cos(radians(51.527351)) *
   cos(radians(lat)) * cos(radians(lng) - radians(0.765544)) +
   sin(radians(51.527351)) * sin(radians(lat)))
  ) AS distance
FROM
  (SELECT title,
     SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 1), '|', -1) + 0 as lat,
     SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 2), '|', -1) + 0 as lng
   FROM locations
  ) AS sub
HAVING distance < 500
ORDER BY distance
LIMIT 0 , 20

Thanks MvG I had just come to the same conclusion myself :), although i went for the repeat rather than the subquery. Which do we think will be quicker? — Jim Doodle, Oct 04 '12 at 13:48
@user1719944, hard to say which is quicker. The repeat will theoretically perform less computation, but all the computation it does is on data accessible through very fast caches. The subquery might write things to a temporary table, which will probably perform better for small result sets, but might degrade severely once that table no longer fits into caches. If in doubt, benchmark both solutions on real-life amounts of data. — MvG, Oct 04 '12 at 14:04

score 0 · Answer 2 · answered Oct 08 '12 at 14:19

Here was my solution which repeats the query. I haven;t had a chance to test of benchmark the differing solutions, but the subquery solution is more pleasant

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 1), '|', -1) as 'latitude',
       SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 2), '|', -1) as 'longitude',
(
  3959 * acos(
    cos(radians({MYLAT})) * cos(radians(SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 1), '|', -1)))
    * cos(radians(SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 2), '|', -1))) - radians({MYLONG}) + sin(radians({MYLAT})) 
    * sin( radians(SUBSTRING_INDEX(SUBSTRING_INDEX(longlat, '|', 1), '|', -1)))
  )
)
AS distance,
title

FROM locations
ORDER BY distance
LIMIT 0 , 20

Turns out i'm going to be using this equation everywhere now, so glad to get it nailed :)

I'm using the averaged 3959 as the earth's radius, however i wonder if i might be better to use some kind regional latitude for different global locations.

Use result of substring function to compute values for multiple columns

2 Answers2