[SQL] 위도 경도에 따라 수행 반경 검색을위한 SQL 쿼리

위도 경도에 따라 수행 반경 검색을위한 SQL 쿼리

우리는 각 행 위도 길이의 데이터가 레스토랑 테이블이있다.

우리는 수행 검색이 제공된 반경 예를 들어, 내의 모든 레스토랑을 찾을 수있는 쿼리를 작성해야 1마일, 5마일 등

우리는이 목적을 위해 다음과 같은 쿼리를 가지고 :

***Parameters***

Longitude: -74.008680
Latitude: 40.711676
Radius: 1 mile

***Query***

SELECT *
FROM restaurant
WHERE (
POW( ( 69.1 * ( Longitude - -74.008680 ) * cos( 40.711676 / 57.3 ) ) , 2 ) + POW( ( 69.1 * ( Latitude - 40.711676 ) ) , 2 )
) < ( 1 *1 );

표는 23K 행에 대해이있다. 결과 집합의 크기는 예컨대 시간에 이상한 5.4 마일 검색, 그것은 다시 880 개 행을 제공하고 5.5 마일, 그것은 다시 21K 행을 제공합니다.

실제 분포가 결과 집합에 따라되지 않도록 -이 표는 뉴욕에 레스토랑 데이터를 포함합니다.

질문 :이 쿼리 거기 아무것도 잘못인가?

해결법

1. ==============================

   1.수학의 관여하기 때문에 제 생각에는 WHERE 절 느린 될 것입니다, 그리고 절은 쿼리 속도를 인덱스를 사용하여 데이터베이스를 방지 할 수있는 기능의 사용 - 그래서, 효과에, 당신은 모든 식당을 검사합니다 데이터베이스, 그리고, 모든 행에 쿼리를 할 때마다 큰-원 수학을 수행합니다.

   수학의 관여하기 때문에 제 생각에는 WHERE 절 느린 될 것입니다, 그리고 절은 쿼리 속도를 인덱스를 사용하여 데이터베이스를 방지 할 수있는 기능의 사용 - 그래서, 효과에, 당신은 모든 식당을 검사합니다 데이터베이스, 그리고, 모든 행에 쿼리를 할 때마다 큰-원 수학을 수행합니다.

   개인적으로 나는면 당신이 찾고있는 범위에 등호 (만 crudly 피타고라스을 사용하여 계산해야합니다) 사각형의 좌상 및 BottomRight 좌표를 계산합니다 다음의 작은 부분 집합에 더 복잡한 WHERE 절 테스트를 수행 그 위도 / 경도 광장 내에 기록.

   데이터베이스 쿼리의 위도 및 긴에 인덱스

   WHERE     MyLat >= @MinLat AND MyLat <= @MaxLat
         AND MyLong >= @MinLong AND MyLong <= @MaxLong
   

   매우 효율적이어야한다

   (주 난 단지 MS SQL의, 특히 MySQL의에 대한 지식이없는하시기 바랍니다)

2. ==============================

   2.당신은 빠른 검색을 만들기 위해 테이블에 공간 인덱스를 만들 수 있습니다.

   당신은 빠른 검색을 만들기 위해 테이블에 공간 인덱스를 만들 수 있습니다.

   이렇게하려면 테이블에 POINT 열을 추가 :

   ALTER TABLE restaurant ADD coords POINT NOT NULL;
   
   CREATE SPATIAL INDEX sx_restaurant_coords ON restaurant (coords);
   
   SELECT  *
   FROM    restaurant
   WHERE   MBRContains(coords, LineString(Point(583734 - 1609, 4507223 - 1609), Point(583734 + 1609, 4507223 + 1609))
           AND GLength(LineString(Point(583734, 4507223), coords)) <= 1609
   

   당신은 하나의 영역 내에서 UTM 좌표로 좌표를 저장해야합니다.

3. ==============================

   3.함수를 사용하여, 예를 들어, 하나의 내가 여기에 게시.

   함수를 사용하여, 예를 들어, 하나의 내가 여기에 게시.

   그런 다음, 쿼리 당신의 레스토랑, 예를 들어, 5 마일 반경 내에서 모든 것을 얻을 수 있습니다

   select * from restaurants 
     where dbo.udf_Haversine(latitude, longitude, @lat, @long) < 5
   

   우편 번호 데이터와이 수행 벌금.

4. ==============================

   4.데이터는 SQL 서버 데이터베이스에있는 경우, 당신은이를 사용할 수 있습니다 :

   데이터는 SQL 서버 데이터베이스에있는 경우, 당신은이를 사용할 수 있습니다 :

   CREATE PROC up_FindZipCodesWithinRadius
   
       @ZipCode char(5) ,
       @GivenMileRadius int
   AS
   SET NOCOUNT ON
   
   DECLARE @lat1 float, 
       @long1 float
   
   SELECT  @lat1= latitude,
       @long1 = longitude 
   FROM ZipSource
   WHERE zipcode = @ZipCode
   
   SELECT ZipCode ,DistanceInMiles
   FROM
   (
       SELECT  ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + 
               (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Long1/57.2958)))), 2)) / 
               ((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * 
               Cos((longitude/57.2958) - (@Long1/57.2958)))))) as DistanceInMiles
   FROM ZipSource
   ) a
   WHERE a.DistanceInMiles <= @GivenMileRadius
   --AND ZipCode <> @ZipCode
   ORDER BY DistanceInMiles
   
   GO
   
   EXEC up_FindZipCodesWithinRadius '35085',20
   GO
   
   DROP PROC up_FindZipCodesWithinRadius
   

from https://stackoverflow.com/questions/1727137/sql-query-for-performing-radius-search-based-on-latitude-longitude by cc-by-sa and MIT license