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   |
4
  • Can you post your table and data as a script so we can run it too? And what is the output from your query? Commented Jun 5, 2018 at 12:10
  • Am not getting any data , it retrieved 0 rows even though i have 2-3 relevant values Commented Jun 5, 2018 at 12:19
  • 3
    With PostGIS this is quite easy: boundlessgeo.com/2011/09/… Commented Jun 5, 2018 at 12:24
  • 1
    Why don't you use postgresql.org/docs/current/static/earthdistance.html Commented Jun 5, 2018 at 12:24

1 Answer 1

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;
1
  • Not sure what the WHERE clause is trying to do but it's useless. Commented Jun 5, 2018 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.