mysql Archives - Page 77 of 469

[php] MySQL 그레이트 서클 거리 (Haversine formula)

경도 및 위도 값을 가져 와서 MySQL 쿼리에 입력하는 작동하는 PHP 스크립트가 있습니다. 전적으로 MySQL로 만들고 싶습니다. 내 현재 PHP 코드는 다음과 같습니다.

if ($distance != "Any" && $customer_zip != "") { //get the great circle distance

    //get the origin zip code info
    $zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
    $result = mysql_query($zip_sql);
    $row = mysql_fetch_array($result);
    $origin_lat = $row['lat'];
    $origin_lon = $row['lon'];

    //get the range
    $lat_range = $distance/69.172;
    $lon_range = abs($distance/(cos($details[0]) * 69.172));
    $min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
    $max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
    $min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
    $max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
    $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
    }

누구나 이것을 완전히 MySQL로 만드는 방법을 알고 있습니까? 인터넷을 조금 살펴 보았지만 대부분의 문헌은 상당히 혼란 스럽습니다.

답변

에서 Google 코드 FAQ – PHP, MySQL은 & Google지도를 사용하여 매장 검색기 만들기 :

다음은 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 < 25
ORDER BY distance
LIMIT 0 , 20;

답변

$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));

라디안의 위도와 경도.

그래서

SELECT
  acos(
      cos(radians( $latitude0 ))
    * cos(radians( $latitude1 ))
    * cos(radians( $longitude0 ) - radians( $longitude1 ))
    + sin(radians( $latitude0 ))
    * sin(radians( $latitude1 ))
  ) AS greatCircleDistance
 FROM yourTable;

당신의 SQL 쿼리입니다

Km 또는 마일 단위로 결과를 얻으려면 결과에 지구의 평균 반경 ( 3959마일, 6371Km 또는 3440해상 마일)을 곱하십시오.

예제에서 계산하는 것은 경계 상자입니다. 당신이 당신의 좌표 데이터를 입력하면 MySQL의 컬럼을 사용 가능 공간 , 당신이 사용할 수있는 기능에 MySQL의의 빌드를 데이터를 조회 할 수 있습니다.

SELECT
  id
FROM spatialEnabledTable
WHERE
  MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))

답변

좌표 테이블에 도우미 필드를 추가하면 쿼리의 응답 시간을 향상시킬 수 있습니다.

이처럼 :

CREATE TABLE `Coordinates` (
`id` INT(10) UNSIGNED NOT NULL COMMENT 'id for the object',
`type` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'type',
`sin_lat` FLOAT NOT NULL COMMENT 'sin(lat) in radians',
`cos_cos` FLOAT NOT NULL COMMENT 'cos(lat)*cos(lon) in radians',
`cos_sin` FLOAT NOT NULL COMMENT 'cos(lat)*sin(lon) in radians',
`lat` FLOAT NOT NULL COMMENT 'latitude in degrees',
`lon` FLOAT NOT NULL COMMENT 'longitude in degrees',
INDEX `lat_lon_idx` (`lat`, `lon`)
)

TokuDB를 사용하는 경우 다음과 같이 술어 중 하나에 클러스터링 인덱스를 추가하면 성능이 훨씬 향상됩니다.

alter table Coordinates add clustering index c_lat(lat);
alter table Coordinates add clustering index c_lon(lon);

각 포인트마다 기본 위도 및 경도뿐만 아니라 라디안의 sin (lat), 라디안의 cos (lat) * cos (lon) 및 라디안의 cos (lat) * sin (lon)이 필요합니다. 그런 다음 다음과 같이 mysql 함수를 작성하십시오.

CREATE FUNCTION `geodistance`(`sin_lat1` FLOAT,
                              `cos_cos1` FLOAT, `cos_sin1` FLOAT,
                              `sin_lat2` FLOAT,
                              `cos_cos2` FLOAT, `cos_sin2` FLOAT)
    RETURNS float
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY INVOKER
   BEGIN
   RETURN acos(sin_lat1*sin_lat2 + cos_cos1*cos_cos2 + cos_sin1*cos_sin2);
   END

이것은 당신에게 거리를 제공합니다.

위도 / 경도에 인덱스를 추가해야 경계 상자가 검색 속도를 늦추지 않고 검색에 도움을 줄 수 있습니다 (인덱스가 위의 CREATE TABLE 쿼리에 이미 추가되어 있음).

INDEX `lat_lon_idx` (`lat`, `lon`)

위도 / 경도 좌표 만있는 오래된 테이블이있는 경우 다음과 같이 업데이트하도록 스크립트를 설정할 수 있습니다 (php를 사용하는 meekrodb).

$users = DB::query('SELECT id,lat,lon FROM Old_Coordinates');

foreach ($users as $user)
{
  $lat_rad = deg2rad($user['lat']);
  $lon_rad = deg2rad($user['lon']);

  DB::replace('Coordinates', array(
    'object_id' => $user['id'],
    'object_type' => 0,
    'sin_lat' => sin($lat_rad),
    'cos_cos' => cos($lat_rad)*cos($lon_rad),
    'cos_sin' => cos($lat_rad)*sin($lon_rad),
    'lat' => $user['lat'],
    'lon' => $user['lon']
  ));
}

그런 다음 실제로 필요한 경우에만 거리 계산을 수행하도록 실제 쿼리를 최적화합니다 (예 : 내외부에서 원 (타원, 타원) 경계). 이를 위해 쿼리 자체에 대한 몇 가지 메트릭을 미리 계산해야합니다.

// assuming the search center coordinates are $lat and $lon in degrees
// and radius in km is given in $distance
$lat_rad = deg2rad($lat);
$lon_rad = deg2rad($lon);
$R = 6371; // earth's radius, km
$distance_rad = $distance/$R;
$distance_rad_plus = $distance_rad * 1.06; // ovality error for outer bounding box
$dist_deg_lat = rad2deg($distance_rad_plus); //outer bounding box
$dist_deg_lon = rad2deg($distance_rad_plus/cos(deg2rad($lat)));
$dist_deg_lat_small = rad2deg($distance_rad/sqrt(2)); //inner bounding box
$dist_deg_lon_small = rad2deg($distance_rad/cos(deg2rad($lat))/sqrt(2));

이러한 준비가 주어지면 쿼리는 다음과 같습니다 (php).

$neighbors = DB::query("SELECT id, type, lat, lon,
       geodistance(sin_lat,cos_cos,cos_sin,%d,%d,%d) as distance
       FROM Coordinates WHERE
       lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d
       HAVING (lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d) OR distance <= %d",
  // center radian values: sin_lat, cos_cos, cos_sin
       sin($lat_rad),cos($lat_rad)*cos($lon_rad),cos($lat_rad)*sin($lon_rad),
  // min_lat, max_lat, min_lon, max_lon for the outside box
       $lat-$dist_deg_lat,$lat+$dist_deg_lat,
       $lon-$dist_deg_lon,$lon+$dist_deg_lon,
  // min_lat, max_lat, min_lon, max_lon for the inside box
       $lat-$dist_deg_lat_small,$lat+$dist_deg_lat_small,
       $lon-$dist_deg_lon_small,$lon+$dist_deg_lon_small,
  // distance in radians
       $distance_rad);

위 쿼리에 대한 EXPLAIN은 트리거 할 결과가 충분하지 않으면 인덱스를 사용하지 않는다고 말할 수 있습니다. 좌표 테이블에 충분한 데이터가있는 경우 인덱스가 사용됩니다. 테이블 크기와 상관없이 인덱스를 사용하도록 SELECT에 FORCE INDEX (lat_lon_idx)를 추가 할 수 있으므로 EXPLAIN을 사용하여 올바르게 작동하는지 확인할 수 있습니다.

위의 코드 샘플을 사용하면 최소한의 오류로 거리별로 객체 검색을 작동하고 확장 가능하게 구현해야합니다.

답변

나는 이것을 좀 더 자세하게 해결해야 했으므로 결과를 공유 할 것입니다. 와 테이블 이있는 zip테이블을 사용합니다 . Google지도에 의존하지 않습니다. 오히려 위도 / 경도를 포함하는 모든 테이블에 적용 할 수 있습니다.latitudelongitude

SELECT zip, primary_city,
       latitude, longitude, distance_in_mi
  FROM (
SELECT zip, primary_city, latitude, longitude,r,
       (3963.17 * ACOS(COS(RADIANS(latpoint))
                 * COS(RADIANS(latitude))
                 * COS(RADIANS(longpoint) - RADIANS(longitude))
                 + SIN(RADIANS(latpoint))
                 * SIN(RADIANS(latitude)))) AS distance_in_mi
 FROM zip
 JOIN (
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r
   ) AS p
 WHERE latitude
  BETWEEN latpoint  - (r / 69)
      AND latpoint  + (r / 69)
   AND longitude
  BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
      AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
  ) d
 WHERE distance_in_mi <= r
 ORDER BY distance_in_mi
 LIMIT 30

해당 쿼리의 중간에 다음 줄이 있습니다.

    SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r

가장 가까운 30 개의 항목을 검색합니다. zip위도 / 경도 42.81 / -70.81에서 50.0 마일 이내에 테이블에서 . 이것을 앱에 빌드하면 자신의 포인트와 검색 반경을 입력 할 수 있습니다.

마일이 아닌 킬로미터 단위로 일하려면 69로 111.045변경하고로 변경 3963.17하십시오6378.10 쿼리에서.

자세한 내용은 다음과 같습니다. 누군가에게 도움이되기를 바랍니다. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

답변

나는 같은 것을 계산할 수있는 절차를 작성했지만 해당 표에 위도와 경도를 입력해야합니다.

drop procedure if exists select_lattitude_longitude;

delimiter //

create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20))

begin

    declare origin_lat float(10,2);
    declare origin_long float(10,2);

    declare dest_lat float(10,2);
    declare dest_long float(10,2);

    if CityName1  Not In (select Name from City_lat_lon) OR CityName2  Not In (select Name from City_lat_lon) then

        select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message;

    else

        select lattitude into  origin_lat from City_lat_lon where Name=CityName1;

        select longitude into  origin_long  from City_lat_lon where Name=CityName1;

        select lattitude into  dest_lat from City_lat_lon where Name=CityName2;

        select longitude into  dest_long  from City_lat_lon where Name=CityName2;

        select origin_lat as CityName1_lattitude,
               origin_long as CityName1_longitude,
               dest_lat as CityName2_lattitude,
               dest_long as CityName2_longitude;

        SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ;

    end if;

end ;

//

delimiter ;

답변

위의 답변에 대해서는 언급 할 수 없지만 @Pavel Chuchuva의 답변에주의하십시오. 두 좌표가 동일한 경우 해당 수식은 결과를 반환하지 않습니다. 이 경우 거리가 null이므로 해당 수식과 함께 행이 그대로 반환되지 않습니다.

나는 MySQL 전문가는 아니지만 이것이 나를 위해 일하는 것 같습니다.

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 OR distance IS NULL ORDER BY distance LIMIT 0 , 20;

답변

 SELECT *, (
    6371 * acos(cos(radians(search_lat)) * cos(radians(lat) ) *
cos(radians(lng) - radians(search_lng)) + sin(radians(search_lat)) *         sin(radians(lat)))
) AS distance
FROM table
WHERE lat != search_lat AND lng != search_lng AND distance < 25
 ORDER BY distance
FETCH 10 ONLY

25km 거리