找到最近的纬度/经度-复杂的sql或复杂的计算

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

17 个解决方案

#1

What you need is to translate the distance into degrees of longitude and latitude, filter based on those to bound the entries that are roughly in the bounding box, then do a more precise distance filter. Here is great paper that explains how to do all this:

您需要的是将距离转换为经度和纬度，根据它们进行筛选，以绑定大致在边界框中的条目，然后进行更精确的距离筛选。这里有一篇很棒的论文，解释了如何做到这一切:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

#2

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;

where [starlat] and [startlng] is the position where to start measuring the distance.

[starlat]和[startlng]是开始测量距离的位置。

#3

Google's solution:

Creating the Table

When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. To keep the storage space required for your table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees. Your table should also have an id attribute to serve as the primary key.

创建MySQL表时，需要特别注意lat和lng属性。使用当前谷歌映射的缩放功能，您应该只需要小数点后6位的精度。要将表所需的存储空间保持在最小，可以指定lat和lng属性是大小为(10,6)的浮点数。这将使字段在十进制之后存储6位数字，在十进制之前存储最多4位数字，例如-123.456789度。您的表还应该具有一个id属性作为主键。

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;

Populating the Table

After creating the table, it's time to populate it with data. The sample data provided below is for about 180 pizzarias scattered across the United States. In phpMyAdmin, you can use the IMPORT tab to import various file formats, including CSV (comma-separated values). Microsoft Excel and Google Spreadsheets both export to CSV format, so you can easily transfer data from spreadsheets to MySQL tables through exporting/importing CSV files.

创建表之后，就该用数据填充它了。下面提供的样本数据是分布在美国各地的大约180个披萨饼。在phpMyAdmin中，可以使用IMPORT选项卡来导入各种文件格式，包括CSV(逗号分隔的值)。Microsoft Excel和谷歌电子表格都导出为CSV格式，因此您可以通过导出/导入CSV文件轻松地将数据从电子表格传输到MySQL表。

INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici\'s East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp\'s Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba\'s Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano\'s Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646');

Finding Locations with MySQL

To find locations in your markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula. The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere. An in-depth mathemetical explanation is given by Wikipedia and a good discussion of the formula as it relates to programming is on Movable Type's site.

要在标记表中找到在给定纬度/经度的一定半径范围内的位置，可以使用基于Haversine公式的SELECT语句。一般用于计算球面上两对坐标之间的大圆距离。*给出了一个深入的数学解释，并且在Movable Type的网站上对这个公式进行了很好的讨论。

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

下面是SQL语句，它将找到距离37，- 122坐标25英里范围内最近的20个位置。它根据行的纬度/经度和目标纬度/经度计算距离，然后只要求距离值小于25的行，按距离对整个查询进行排序，并将结果限制为20个。用千米代替英里搜索，用6371代替3959。

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;

https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map

https://developers.google.com/maps/articles/phpsqlsearch_v3创建地图

#4

Here is my full solution implemented in PHP.

下面是我在PHP中实现的完整解决方案。

This solution uses the Haversine formula as presented in http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL.

这个解决方案使用了http://www.scribd.com/doc/2569355/ geodistance - search - mysql中的Haversine公式。

It should be noted that the Haversine formula experiences weaknesses around the poles. This answer shows how to implement the vincenty Great Circle Distance formula to get around this, however I chose to just use Haversine because it's good enough for my purposes.

应该指出的是，Haversine公式在极点周围存在弱点。这个答案展示了如何实现vincenty大圆距离公式来解决这个问题，但是我选择使用Haversine，因为它对我的目的来说已经足够好了。

I'm storing latitude as DECIMAL(10,8) and longitude as DECIMAL(11,8). Hopefully this helps!

我将纬度存储为十进制(10,8)和经度为十进制(11,8)。希望这可以帮助!

showClosest.php

<?PHP
/**
 * Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon
 * Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius.
 */
include("./assets/db/db.php"); // Include database connection function
$db = new database(); // Initiate a new MySQL connection
$tableName = "db.table";
$origLat = 42.1365;
$origLon = -71.7559;
$dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search
$query = "SELECT name, latitude, longitude, 3956 * 2 * 
          ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2)
          +COS($origLat*pi()/180 )*COS(latitude*pi()/180)
          *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) 
          as distance FROM $tableName WHERE 
          longitude between ($origLon-$dist/cos(radians($origLat))*69) 
          and ($origLon+$dist/cos(radians($origLat))*69) 
          and latitude between ($origLat-($dist/69)) 
          and ($origLat+($dist/69)) 
          having distance < $dist ORDER BY distance limit 100"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
    echo $row['name']." > ".$row['distance']."<BR>";
}
mysql_close($db);
?>

./assets/db/db.php

<?PHP
/**
 * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php
 *
 * @example $db = new database(); // Initiate a new database connection
 * @example mysql_close($db); // close the connection
 */
class database{
    protected $databaseLink;
    function __construct(){
        include "dbSettings.php";
        $this->database = $dbInfo['host'];
        $this->mysql_user = $dbInfo['user'];
        $this->mysql_pass = $dbInfo['pass'];
        $this->openConnection();
        return $this->get_link();
    }
    function openConnection(){
    $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass);
    }

    function get_link(){
    return $this->databaseLink;
    }
}
?>

./assets/db/dbSettings.php

<?php
$dbInfo = array(
    'host'      => "localhost",
    'user'      => "root",
    'pass'      => "password"
);
?>

It may be possible to increase performance by using a MySQL stored procedure as suggested by the "Geo-Distance-Search-with-MySQL" article posted above.

根据上面的“地理距离-搜索-MySQL”的文章，可以通过使用MySQL存储过程来提高性能。

I have a database of ~17,000 places and the query execution time is 0.054 seconds.

我有一个1.7万个位置的数据库，查询执行时间为0.054秒。

#5

Just in case you are lazy like me, here's a solution amalgamated from this and other answers on SO.

如果你像我一样懒惰，这里有一个从这个和其他答案合并的解决方案。

set @orig_lat=37.46; 
set @orig_long=-122.25; 
set @bounding_distance=1;

SELECT
*
,((ACOS(SIN(@orig_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(@orig_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((@orig_long - `long`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` 
FROM `cities` 
WHERE
(
  `lat` BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance)
  AND `long` BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance)
)
ORDER BY `distance` ASC
limit 25;

#6

Easy one ;)

简单。)

SELECT * FROM `WAYPOINTS` W ORDER BY
ABS(ABS(W.`LATITUDE`-53.63) +
ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30;

Just replace the coordinates with your required ones. The values have to be stored as double. This ist a working MySQL 5.x example.

把坐标换成你需要的坐标。值必须以双精度存储。这是一个正常的MySQL 5。x的例子。

Cheers

干杯

#7

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

你要找的是像haversine公式。在这里看到的。

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).

如果您正在寻找更健壮的东西，您可能需要查看数据库GIS功能。它们可以做一些很酷的事情，比如告诉你一个点(城市)是否出现在一个给定的多边形(地区、国家、大陆)中。

#8

Check this code based on the article Geo-Distance-Search-with-MySQL:

根据“地理距离-搜索- mysql”这篇文章来检查这段代码:

Example: find the 10 nearest hotels to my current location in a 10 miles radius:

例如:找到离我现在位置最近的10家酒店，半径为10英里:

#Please notice that (lat,lng) values mustn't be negatives to perform all calculations

set @my_lat=34.6087674878572; 
set @my_lng=58.3783670308302;
set @dist=10; #10 miles radius

SELECT dest.id, dest.lat, dest.lng,  3956 * 2 * ASIN(SQRT(POWER(SIN((@my_lat -abs(dest.lat)) * pi()/180 / 2),2) + COS(@my_lat * pi()/180 ) * COS(abs(dest.lat) *  pi()/180) * POWER(SIN((@my_lng - abs(dest.lng)) *  pi()/180 / 2), 2))
) as distance
FROM hotel as dest
having distance < @dist
ORDER BY distance limit 10;

#Also notice that distance are expressed in terms of radius.

#9

Try this, it show the nearest points to provided coordinates (within 50 km). It works perfectly:

试试这个，它会显示距离所提供坐标最近的点(在50公里以内)。它完美地:

SELECT m.name,
    m.lat, m.lon,
    p.distance_unit
             * DEGREES(ACOS(COS(RADIANS(p.latpoint))
             * COS(RADIANS(m.lat))
             * COS(RADIANS(p.longpoint) - RADIANS(m.lon))
             + SIN(RADIANS(p.latpoint))
             * SIN(RADIANS(m.lat)))) AS distance_in_km
FROM <table_name> AS m
JOIN (
      SELECT <userLat> AS latpoint, <userLon> AS longpoint,
             50.0 AS radius, 111.045 AS distance_unit
     ) AS p ON 1=1
WHERE m.lat
BETWEEN p.latpoint  - (p.radius / p.distance_unit)
    AND p.latpoint  + (p.radius / p.distance_unit)
    AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
    AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km

Just change <table_name>. <userLat> and <userLon>

只改变< table_name >。< userLat >和< userLon >

You can read more about this solution here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

您可以在这里阅读关于这个解决方案的更多信息:http://www.plumislandmedia.net/mysql/haversine-mysql-near -loc/

#10

simpledb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + "(id INTEGER PRIMARY KEY   AUTOINCREMENT,lat double,lng double,address varchar)");
            simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2891001','70.780154','craftbox');");
            simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2901396','70.7782428','kotecha');");//22.2904718 //70.7783906
            simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2863155','70.772108','kkv Hall');");
            simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.275993','70.778076','nana mava');");
            simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2667148','70.7609386','Govani boys hostal');");


    double curentlat=22.2667258;  //22.2677258
    double curentlong=70.76096826;//70.76096826

    double curentlat1=curentlat+0.0010000;
    double curentlat2=curentlat-0.0010000;

    double curentlong1=curentlong+0.0010000;
    double curentlong2=curentlong-0.0010000;

    try{

        Cursor c=simpledb.rawQuery("select * from '"+tablename+"' where (lat BETWEEN '"+curentlat2+"' and '"+curentlat1+"') or (lng BETWEEN         '"+curentlong2+"' and '"+curentlong1+"')",null);

        Log.d("SQL ", c.toString());
        if(c.getCount()>0)
        {
            while (c.moveToNext())
            {
                double d=c.getDouble(1);
                double d1=c.getDouble(2);

            }
        }
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }

#11

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.

听起来你想做一个最近的邻居搜索，在距离上有一定的界限。就我所知，SQL不支持任何类似的操作，您需要使用另一种数据结构，比如R-tree或kd-tree。

#12

Find nearest Users to my:

找到离我最近的用户:

Distance in meters

米的距离

Based in Vincenty's formula

位于Vincenty的公式

i have User table:

我有用户表:

+----+-----------------------+---------+--------------+---------------+
| id | email                 | name    | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 13 | [e-mail] | Isaac   | 17.2675625   | -97.6802361   |
| 14 | [e-mail]   | Monse   | 19.392702    | -99.172596    |
+----+-----------------------+---------+--------------+---------------+

sql:

sql:

-- my location:  lat   19.391124   -99.165660
SELECT 
(ATAN(
    SQRT(
        POW(COS(RADIANS(users.location_lat)) * SIN(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) +
        POW(COS(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) - 
       SIN(RADIANS(19.391124)) * cos(RADIANS(users.location_lat)) * cos(RADIANS(users.location_long) - RADIANS(-99.165660)), 2)
    )
    ,
    SIN(RADIANS(19.391124)) * 
    SIN(RADIANS(users.location_lat)) + 
    COS(RADIANS(19.391124)) * 
    COS(RADIANS(users.location_lat)) * 
    COS(RADIANS(users.location_long) - RADIANS(-99.165660))
 ) * 6371000) as distance,
users.id
FROM users
ORDER BY distance ASC

radius of the earth : 6371000 ( in meters)

地球半径:6371000(单位:米)

#13

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

听起来您应该使用PostGIS、SpatialLite、SQLServer2008或Oracle Spatial。它们都可以用空间SQL为您回答这个问题。

#14

You should try these: http://en.wikipedia.org/wiki/Great-circle_distance http://code.google.com/apis/maps/articles/phpsqlsearch.html

您应该试试这些:http://en.wikipedia.org/wiki/greatcircle_distance http://code.google.com/apis/maps/articles/phpsqlsearch.html

#15

In extreme cases this approach fails, but for performance, I've skipped the trigonometry and simply calculated the diagonal squared.

在极端情况下，这种方法失败了，但是对于性能，我跳过了三角学，简单地计算了对角线的平方。

#16

MS SQL Edition here:

MS SQL版:

        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

#17

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.

我的意思是，你的数据库中有100个地点还是1亿个?这有很大的不同。

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

如果位置的数量很少，只需执行—>，就可以将它们从SQL中取出并放入代码中

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.

一旦你把它们写进代码，用Haversine公式计算出每个lat/lon和原来的距离，并对其进行排序。