Find nearest latitude/longitude with an SQL query

user_75799 0
Viewed 261852

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 specified one, then don't retrieve it.

Table structure:

id
latitude
longitude
place name
city
country
state
zip
sealevel
geospatial coordinates mysql sql
18 Answers

The original answers to the question are good, but newer versions of mysql (MySQL 5.7.6 on) support geo queries, so you can now use built in functionality rather than doing complex queries.

You can now do something like:

select *, ST_Distance_Sphere( point ('input_longitude', 'input_latitude'), 
                              point(longitude, latitude)) * .000621371192 
          as `distance_in_miles` 
  from `TableName`
having `distance_in_miles` <= 'input_max_distance'
 order by `distance_in_miles` asc

The results are returned in meters. So if you want in KM simply use .001 instead of .000621371192 (which is for miles).

MySql docs are here

user_6450610

You're looking for things like the haversine formula. See here as well.

There's other ones but this is the most commonly cited.

If you're looking for something even more robust, you might want to look at your databases GIS capabilities. They're capable of some cool things like telling you whether a point (City) appears within a given polygon (Region, Country, Continent).

user_2023920

It sounds like you want to do a nearest neighbour search with some bound on the distance. SQL does not support anything like this as far as I am aware and you would need to use an alternative data structure such as an R-tree or kd-tree.

cdv_38360

MS SQL Edition here:

        DECLARE @SLAT AS FLOAT
        DECLARE @SLON AS FLOAT

        SET @SLAT = 38.150785
        SET @SLON = 27.360249

        SELECT TOP 10 [LATITUDE], [LONGITUDE], SQRT(
            POWER(69.1 * ([LATITUDE] - @SLAT), 2) +
            POWER(69.1 * (@SLON - [LONGITUDE]) * COS([LATITUDE] / 57.3), 2)) AS distance
        FROM [TABLE] ORDER BY 3
user_63446080

Sounds like you should just use PostGIS, SpatialLite, SQLServer2008, or Oracle Spatial. They can all answer this question for you with spatial SQL.

user_2666090 
 +----+-----------------------+---------+--------------+---------------+
| id | email                 | name    | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 7  | test@gmail.com        | rembo   | 23.0249256   |  72.5269697   |
| 25 | test1@gmail.com.      | Rajnis  | 23.0233221    | 72.5342112   |
+----+-----------------------+---------+--------------+---------------+

$lat = 23.02350629;

$long = 72.53230239;

DB:: SELECT (" SELECT * FROM ( SELECT , ( ( ( acos( sin(( ". $ lat ." * pi() / 180)) * sin(( lat * pi() / 180)) + cos(( ". $ lat ." pi() / 180 )) * cos(( lat * pi() / 180)) * cos((( ". $ long ." - LONG) * pi() / 180))) ) * 180 / pi() ) * 60 * 1.1515 * 1.609344 ) as distance FROM users ) users WHERE distance <= 2");

user_118288700

Mysql query for search coordinates with distance limit and where condition

 SELECT id, ( 3959 * acos( cos( radians('28.5850154') ) * cos( radians(latitude) ) * cos( radians( longitude ) - radians('77.07207489999999') ) + sin( radians('28.5850154') ) * sin( radians( latitude ) ) ) ) AS distance FROM `vendors` HAVING distance < 5;
user_103250000

This problem is not very hard at all, but it gets more complicated if you need to optimize it.

What I mean is, do you have 100 locations in your database or 100 million? It makes a big difference.

If the number of locations is small, get them out of SQL and into code by just doing -> 

Select * from Location

Once you get them into code, calculate the distance between each lat/lon and your original with the Haversine formula and sort it.

user_2699960
Related