데이터베이스:위치 데이터를 쿼리하는 최상의 성능 방법은 무엇입니까?

데이터베이스:위치 데이터를 쿼리하는 최상의 성능 방법은 무엇입니까?

MySQL 데이터베이스를 가지고 있습니다.저는 집을 데이터베이스에 저장하고 데이터베이스에 대해 문자 그대로 1개의 쿼리만 수행합니다. 하지만 이 쿼리를 매우 빠르게 수행해야 합니다. 즉, 지구 위도와 경도의 사각형 상자 안에 있는 모든 집을 반환하는 것입니다.

SELECT * FROM homes 
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

지리 위치 상자 내의 모든 홈을 표시하는 쿼리를 가장 빠르게 수행할 수 있도록 지리 데이터를 저장하는 가장 좋은 방법은 무엇입니까?

기본적으로:

• 이 쿼리를 가장 빨리 수행하기 위해 최상의 SQL 문을 사용하고 있습니까?
• 박스형 지리 위치 경계 내에서 주택의 결과를 가장 빠르게 쿼리할 수 있는 다른 방법이 있습니까? 데이터베이스를 사용하지 않을 수도 있습니다.

도움이 될 경우 아래에 데이터베이스 테이블 스키마를 포함합니다.

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `address` varchar(128) collate utf8_unicode_ci NOT NULL,
  `city` varchar(64) collate utf8_unicode_ci NOT NULL,
  `state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `zip` mediumint(8) unsigned NOT NULL,
  `price` mediumint(8) unsigned NOT NULL,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  PRIMARY KEY  (`home_id`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
) ENGINE=InnoDB  ;

갱신하다

저는 공간이 지구의 곡률에 영향을 미칠 것이라는 것을 이해하지만, 저는 가장 빨리 지리 데이터를 반환하는 것에 관심이 있습니다. 이러한 공간 데이터베이스 패키지가 어떻게든 더 빨리 데이터를 반환하지 않는 한, 공간 확장을 추천하지 마십시오.감사해요.

업데이트 2

아래의 어느 누구도 질문에 진정으로 대답하지 않았습니다.저는 제가 받을 수 있는 어떤 도움도 정말 기대하고 있습니다.잘 부탁드립니다.

여기에 MySQL 지리 위치 성능에 대한 좋은 논문이 있습니다.

편집 이것은 고정 반경을 사용하는 것이 확실합니다.또한 거리를 계산하는 알고리즘이 가장 진보된 것인지 100% 확신할 수 없습니다(즉, 지구를 통해 "드릴"할 것입니다).

중요한 것은 적절한 거리 검색을 위해 행 수에 대한 볼 파크 제한을 제공하는 알고리즘이 저렴하다는 것입니다.

---

이 알고리즘은 소스 포인트 주변의 정사각형에서 후보를 가져온 다음 거리(마일)를 계산하여 사전 필터링합니다.

이 값을 미리 계산하거나 소스에서 제안하는 대로 저장 프로시저를 사용합니다.

# Pseudo code
# user_lon and user_lat are the source longitude and latitude
# radius is the radius where you want to search
lon_distance = radius / abs(cos(radians(user_lat))*69);
min_lon = user_lon - lon_distance;
max_lon = user_lon + lon_distance;
min_lat = user_lat - (radius / 69);
max_lat = user_lat + (radius / 69);

SELECT dest.*,
  3956 * 2 * ASIN(
    SQRT(
      POWER(
        SIN(
          (user_lat - dest.lat) * pi() / 180 / 2
        ), 2
      ) + COS(
        user_lat * pi() / 180
      ) * COS(
        dest.lat * pi() / 180
      ) * POWER(
        SIN(
          (user_lon - dest.lon) * pi() / 180 / 2
        ), 2
      )
    )
  ) as distance
FROM dest
WHERE 
  dest.lon between min_lon and max_lon AND
  dest.lat between min_lat and max_lat
HAVING distance < radius
ORDER BY distance
LIMIT 10

저도 같은 문제가 있었고, 3부 블로그 게시물을 작성했습니다.이것은 지구 지수보다 더 빨랐습니다.

소개, 벤치마크, SQL

성능 향상이 꼭 필요한 경우 데이터에 대한 경계 상자를 정의하고 삽입 시 사전 계산 경계 상자를 개체에 매핑하여 나중에 쿼리에 사용할 수 있습니다.

결과 세트가 상당히 작은 경우에도 정확한 결과를 제공하는 동시에 애플리케이션 로직에서 정확도를 수정할 수 있습니다(데이터베이스보다 수평으로 확장하기 쉽습니다).

접근 방식에 대한 훌륭한 문서가 포함된 Bret Slatkin의 geobox.py 을 살펴보십시오.

저는 여전히 Postgre를 확인하는 것을 추천합니다.SQL 및 게시가까운 미래에 더 복잡한 쿼리를 수행하려는 경우 MySQL과 비교하여 GIS.

이며 B-는 B-를 합니다.BETWEEN검색어를 입력합니다.이는 최적화 도구가 인덱스를 사용하여 "상자" 내의 홈을 찾을 수 있음을 의미합니다.그러나 항상 인덱스를 사용한다는 의미는 아닙니다."히트"가 너무 많이 포함된 범위를 지정하면 인덱스가 사용되지 않습니다.

현재의 접근 방식을 고수하면서 한 가지 변경 사항을 수행해야 합니다. 지오래트와 지오래트를 개별적으로 인덱싱하는 것보다 복합 인덱스를 사용해야 합니다.

KEY `geolat_geolng` (`geolat`, `geolng`),

현재 쿼리는 두 개의 인덱스 중 하나만 사용합니다.

매우 좋은 대안은 지리공간 색인이 있는 MongoDB입니다.

여기 제가 성공적으로 사용한 속임수가 있습니다. 라운드-오프 지역을 만드는 것입니다.즉, 36.12345, -120.54321에 위치한 경우 반 마일(대략) 그리드 상자 내의 다른 위치와 그룹화하려면 해당 영역을 36.12x-120.54라고 부르면 반올림 영역이 동일한 다른 모든 위치가 동일한 상자에 들어갑니다.

분명히, 그것은 깨끗한 반경을 제공하지 않습니다. 즉, 당신이 보고 있는 위치가 다른 가장자리보다 한 쪽 가장자리에 가깝다면 말입니다.그러나 이러한 설정을 사용하면 기본 위치의 상자를 둘러싼 8개의 상자를 쉽게 계산할 수 있습니다.위트:

[36.13x-120.55][36.13x-120.54][36.13x-120.53]
[36.12x-120.55][36.12x-120.54][36.12x-120.53]
[36.11x-120.55][36.11x-120.54][36.11x-120.53]

일치하는 반올림 레이블이 있는 모든 위치를 가져온 다음 데이터베이스에서 거리 계산을 수행하여 사용할 위치를 결정할 수 있습니다.

MySQL 5.7 mysql은 성능을 향상시키는 ST_Distance_Sphere() 및 ST_Contains()와 같은 지오인덱스를 사용할 수 있기 때문입니다.

이거 꽤 빨라 보여요.제 유일한 관심사는 지표를 사용하여 위도 3마일 이내의 모든 값을 얻은 다음 경도 3마일 이내의 값을 필터링하는 것입니다.기본 시스템의 작동 방식을 이해하면 테이블당 하나의 인덱스만 사용할 수 있으므로 lat 또는 long 인덱스는 가치가 없습니다.

데이터 양이 많은 경우 속도를 높여 매 1x1마일 제곱마다 고유한 논리 ID를 제공한 다음 SELECT에 대해 점 주변의 모든 정사각형에 대해 영역="23234/34234" OR 영역="23235/34234" OR ...을 추가로 제한한 다음 데이터베이스가 lat 및 long 대신 해당 인덱스를 사용하도록 강제할 수 있습니다.그러면 훨씬 적은 평방 마일의 데이터만 필터링하게 됩니다.

집이요? 아마 만 채도 못 가질 거예요.STRtree와 같은 메모리 내 인덱스를 사용하면 됩니다.

특정 지오로케이션에 홈이 있는 경우 기본 키가 ('geolat', 'geolng')이고 home_id가 들어 있는 열이 있는 별도의 테이블 'GeoLocations'를 만드는 것을 고려할 수 있습니다.이를 통해 최적화 도구는 home_ids 목록에 대해 디스크에서 정렬될 지리적 위치 범위를 검색할 수 있습니다.그런 다음 'home' 테이블로 조인을 수행하여 home_ids에 대한 정보를 찾을 수 있습니다.

CREATE TABLE IF NOT EXISTS `GeoLocations` (
`geolat` decimal(10,6) NOT NULL,
`geolng` decimal(10,6) NOT NULL,
`home_id` int(10) NULL
PRIMARY KEY  (`geolat`,`geolng`)
);

SELECT GL.home_id
FROM GeoLocations GL
INNER JOIN Homes H
 ON GL.home_id = H.home_id
WHERE GL.geolat between X and Y
 and GL.geolng between X and Y

언급URL : https://stackoverflow.com/questions/1813460/database-best-performance-way-to-query-geo-location-data