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:




place name






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

Browse Categories