1

I am trying find the nearest lang and lat from the specified lat and lang i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the nearest cities of muscat with limit 3 along with muscat

CREATE TABLE IF NOT EXISTS tablename(_id SERIAL NOT NULL UNIQUE PRIMARY KEY,city VARCHAR, Lat NUMERIC, Lang NUMERIC);

select * from (
SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lang ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance 
FROM tablename
) al
where lat >  23.61  OR lang >  58.54
ORDER BY distance
LIMIT 1;

here is my table (Assumed data not exact lat and lang)data

| id | City      |  lat      | lang    |
----------------------------------------
|  1 | muscat    | 23.61     | 58.54   |
|  2 | sur       | 22.566    |59.52    |
|  3 | Muhafazat |23.585     |58.40    |
|  4 | ZZZ       | 5.8       | 7       |
|  5 | AAA       | 9.22      | 5       |
|  6 | Barka     |23.613     |58.592   |

Expected out put:

    | id | City      |  lat      | lang    |
    ----------------------------------------
    |  1 | muscat    | 23.61     | 58.54   |
    |  2 | sur       | 22.566    |59.52    |
    |  3 | Muhafazat |23.585     |58.40    |
    |  6 | Barka     |23.613     |58.592   |
0

yes i got the output with this query, and thank you to all who tried to help me:)

select * from (SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lang ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance FROM table name) al where lat >= 22.566 OR lat >= 59.52 AND lat <= 22.566 OR lat <= 59.52 ORDER BY distance LIMIT 3;
  • Not sure what the WHERE clause is trying to do but it's useless. – ypercubeᵀᴹ Jun 5 '18 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.