위도 / 경도는 가장 가까운 위도 / 경도를 찾습니다-복잡한 SQL 또는 복잡한 계산
위도와 경도가 있고 거리에서 가장 가까운 위도와 경도가있는 데이터베이스에서 레코드를 가져 오려고합니다. 거리가 지정된 거리보다 길면 검색하지 마십시오.
테이블 구조 :
id
latitude
longitude
place name
city
country
state
zip
sealevel
필요한 것은 거리를 경도와 위도로 변환하고 경계 상자에있는 항목을 바인딩하는 항목을 기준으로 필터링 한 다음보다 정확한 거리 필터를 수행하는 것입니다. 다음은이 모든 작업을 수행하는 방법을 설명하는 훌륭한 문서입니다.
http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
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;
여기서 [starlat] 및 [startlng] 은 거리 측정을 시작하는 위치입니다.
구글의 솔루션 :
테이블 만들기
MySQL 테이블을 작성할 때 lat 및 lng 속성에 특히주의해야합니다. Google지도의 현재 확대 / 축소 기능을 사용하면 소수점 이하 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 ;
테이블 채우기
테이블을 생성 한 후 데이터로 채울 차례입니다. 아래에 제공된 샘플 데이터는 미국 전역에 흩어져있는 약 180 개의 피자에 대한 것입니다. phpMyAdmin에서 가져 오기 탭을 사용하여 CSV (쉼표로 구분 된 값)를 포함한 다양한 파일 형식을 가져올 수 있습니다. Microsoft Excel 및 Google 스프레드 시트는 모두 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');
MySQL로 위치 찾기
마커 표에서 특정 위도 / 경도의 특정 반경 거리 내에있는 위치를 찾으려면 Haversine 수식을 기반으로 SELECT 문을 사용할 수 있습니다. Haversine 공식은 일반적으로 구의 두 좌표 쌍 사이의 원거리를 계산하는 데 사용됩니다. 자세한 수학적 설명은 Wikipedia에서 제공하며 프로그래밍과 관련된 수식에 대한 자세한 설명은 Movable Type 사이트에 있습니다.
다음은 37, -122 좌표에서 25 마일 반경 내에있는 가장 가까운 20 개의 위치를 찾는 SQL 문입니다. 해당 행의 위도 / 경도 및 대상 위도 / 경도를 기반으로 거리를 계산 한 다음 거리 값이 25보다 작은 행만 요청하고 거리별로 전체 쿼리를 정렬 한 후 20 개의 결과로 제한합니다. 마일 대신 킬로미터로 검색하려면 3959를 6371로 바꾸십시오.
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 < 28
ORDER BY distance LIMIT 0, 20;
이것은 28 마일 미만의 거리에서 위도와 경도를 찾는 것입니다.
다른 하나는 28 마일에서 29 마일 사이에 거리를 찾는 것입니다.
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 < 29 and distance > 28
ORDER BY distance LIMIT 0, 20;
https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map
다음은 PHP로 구현 된 전체 솔루션입니다.
이 솔루션은 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL에 제시된 Haversine 공식을 사용합니다 .
Haversine 공식은 극 주위에서 약점을 경험합니다. 이 대답 은이 문제를 해결하기 위해 빈약 한 Great Circle Distance 공식 을 구현하는 방법을 보여 주지만 Haversine을 사용하기로 결정했습니다. 내 목적에 충분하기 때문입니다.
위도를 DECIMAL (10,8)로 저장하고 경도를 DECIMAL (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"
);
?>
위에 게시 된 "Geo-Distance-Search-with-MySQL"기사에서 제안한대로 MySQL 저장 프로 시저를 사용하여 성능을 향상시킬 수 있습니다.
~ 17,000 개의 데이터베이스가 있고 쿼리 실행 시간은 0.054 초입니다.
당신이 나처럼 게으른 경우를 대비하여 여기에 대한 해결책과 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;
쉬운 하나;)
SELECT * FROM `WAYPOINTS` W ORDER BY
ABS(ABS(W.`LATITUDE`-53.63) +
ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30;
좌표를 필요한 좌표로 바꾸십시오. 값은 double로 저장해야합니다. 이것은 작동하는 MySQL 5.x 예제가 아닙니다.
건배
당신은 haversine formula 같은 것들을 찾고 있습니다. 여기도 참조 하십시오 .
다른 것들이 있지만 이것이 가장 일반적으로 인용되는 것입니다.
보다 강력한 무언가를 찾고 있다면 데이터베이스 GIS 기능을 살펴볼 수 있습니다. 그들은 주어진 다각형 (지역, 국가, 대륙) 내에 점 (도시)이 나타나는지 알려주는 것과 같은 멋진 것들을 할 수 있습니다.
Geo-Distance-Search-with-MySQL 기사를 기반으로이 코드를 확인하십시오 .
예 : 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.
제공된 좌표에 가장 가까운 점을 표시합니다 (50km 이내). 완벽하게 작동합니다.
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>
You can read more about this solution here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
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();
}
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 KM instead of miles use .0001 instead of .000621371192
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.
Find nearest Users to my:
Distance in meters
Based in Vincenty's formula
i have User table:
+----+-----------------------+---------+--------------+---------------+
| id | email | name | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 13 | xxxxxx@xxxxxxxxxx.com | Isaac | 17.2675625 | -97.6802361 |
| 14 | xxxx@xxxxxxx.com.mx | Monse | 19.392702 | -99.172596 |
+----+-----------------------+---------+--------------+---------------+
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)
Sounds like you should just use PostGIS, SpatialLite, SQLServer2008, or Oracle Spatial. They can all answer this question for you with spatial SQL.
You should try these: http://en.wikipedia.org/wiki/Great-circle_distance http://code.google.com/apis/maps/articles/phpsqlsearch.html
In extreme cases this approach fails, but for performance, I've skipped the trigonometry and simply calculated the diagonal squared.
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
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.
'Programming' 카테고리의 다른 글
| 문자열에 숫자 만 포함되어 있는지 확인하는 가장 빠른 방법 (0) | 2020.06.01 |
|---|---|
| jQuery를 사용하여 페이지의 스크롤 위치를 감지하는 방법 (0) | 2020.06.01 |
| 자바 스크립트에서 HTML 특수 문자를 이스케이프 처리 할 수 있습니까? (0) | 2020.06.01 |
| Intellij-최신 Java 8 클래스를 사용할 수 없음-오류 :“@since 1.6 이상으로 문서화 된 API 사용 ..” (0) | 2020.06.01 |
| SQL Server 로그인이 이미 있는지 확인 (0) | 2020.06.01 |