latitude/longitude find nearest latitude/longitude - complex sql or complex calculation

Question

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

Answer 1

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.

Answer 2

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;