Find Nearest Latitude/Longitude With an SQL Query

How to find nearest location using latitude and longitude from SQL database?

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

sql query to get first closest locations by latitude and longitude provided as feilds in table

Your code in your question isn't doing anything like calculating distance between two points. It would be a good idea to do a little research on how to calculate distance between two points - that's where you'll need to start. You should be able to adapt my answer to your scenario, but it would be a good idea to understand the math involved if you'll be working with coordinates.

There's lots of info on Stack Overflow about calculating the distance between two points, even specifically to PostgreSQL. Here's an example adapted from another Stack Overflow artical (PostgreSQL calculate distance between two points without using PostGIS):

SELECT
    id,
    longitude,
    latitude,
    (select SQRT(POW(69.1 * (latitude::float -  10::float), 2) + 
        POW(69.1 * (longitude::float - 15::float) * COS(latitude::float / 57.3), 2)
    )) AS Distance
FROM destination
ORDER BY Distance

I have hardcoded values of 10 for the latitude of the origin point, and 15 for the longitude. Those will need to be replaced by the lat/long of the point you're trying to compare to.

This will return all of the rows in your destination table, including a new column that is the distance (in miles) of the row from your origin point. It will be ordered by that distance as well, closest to farthest.

Find Nearest Place By Using My SQL Record Of LATITUDE And LONGITUDE

Use below query:

SELECT
    Place_id,
    PlaceName,
    (
        3959 
        * acos(
            cos( radians(37) ) 
            * cos( radians( Place_LATITUDE ) ) 
            * cos( radians( Place_LONGITUDE ) - radians(-122) ) 
            + sin( radians(37) ) 
            * sin( radians( Place_LATITUDE ) ) 
        ) 
    ) AS distance 
FROM tbl_places 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20;

NOTE - Here latitude = 37 & longitude = -122

Also, check here for more reference.

How to find nearest location by latitude and longitude?

try this

     SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(( $lat - LatOnTable) *  pi()/180 / 2), 2) +COS( $lat * pi()/180) * COS(LatOnTable * pi()/180) * POWER(SIN(( $long - LongOnTable) * pi()/180 / 2), 2) ))) as distance  
from yourTable  
having  distance <= 10 
order by distance

substitute LatOnTable with the latitude table column name , and longOnTable with you longitude column name in your table .

Get the nearest longitude and latitude from MSSQL database table?

I wrote a blog a couple years ago that explains how this can be done without using the spatial data types. Since it appears as though you have a table of longitude/latitude values, this blog will likely help a lot.

SQL Server Zipcode Latitude Longitude Proximity Search

Same page saved from Archive.org

find the nearest location by latitude and longitude in postgresql

select * from (
SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance 
FROM station_location
) al
where distance < 5
ORDER BY distance
LIMIT 20;

Making query to find nearest multiple(Lat,Long) from the single(Lat,Long)

You can use SQL Server's geography functions for this.

DECLARE @InputLatitude FLOAT = 1.64
DECLARE @InputLongitude FLOAT = 4.25

DECLARE @GPS GEOGRAPHY = GEOGRAPHY::Point(@InputLatitude, @InputLongitude, 4326)

SELECT TOP 1
    P.*,
    Distance = @GPS.STDistance(GEOGRAPHY::Point(P.Lat, P.Long, 4326))
FROM
    dbo.Place AS P
ORDER BY
    @GPS.STDistance(GEOGRAPHY::Point(P.Lat, P.Long, 4326)) ASC

You should consider adding a GEOGRAPHY column on your table with the GPS points already converted and adding a SPATIAL INDEX to speed up queries.

---

Related Topics