Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

I have latitude and longitude and I want to pull the record from the database, which has nearest latitude and longitude by the distance, if that distance gets longer than the specified one, then don't retrieve it.

Table structure:

id

latitude

longitude

place name

city

country

state

zip

sealevel

2 Answers

0 votes
by (40.4k points)

Try the below code:

SELECT latitude, longitude, SQRT(

    POW(69.1 * (latitude - [startlat]), 2) +

    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance

FROM TableName HAVING distance < 25 ORDER BY distance;

In the above code [starlat] and [startlng] is the position to start measuring the distance.

0 votes
by (140 points)

I tried below query, but faced issue, as ALIAS cannot be used in HAVING clause. So, I made this possible like below.

select * from (select latitude, longitude, SQRT(POW(69.1 * (latitude - [startlat]), 2) + POW(69.1 * (([startlng] - longitude) * COS(latitude / 57.3)), 2)) AS distance FROM TableName ORDER BY distance) as vt where vt.distance < 25;

Related questions

0 votes
1 answer
asked Jul 23, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...