PHP/Larabel을 사용하여 MySQL/MariaDB에서 모든 POI를 얻는 데 더 빠른 접근 방식은 무엇입니까?

PHP/Larabel을 사용하여 MySQL/MariaDB에서 모든 POI를 얻는 데 더 빠른 접근 방식은 무엇입니까?

내가 틀렸다면 고쳐 주세요.

사용자들이 제 웹사이트에서 만든 가장 가까운 집을 얻기 위한 세 가지 방법이 있습니다.

1. 두 개의 열(위도, 경도)이 모두 부동인 테이블을 작성하려면:

여기 있습니다.

$latitude = 50;
$longitude = 60;

SELECT * FROM my_table
    WHERE (latitude  <= $latitude+10  AND latitude  >= $latitude-10)
      AND (longitude <= $longitude+10 AND longitude >= $longitude-10)

여기서 10은 예를 들어 1km를 의미합니다.

이 방법에서는 하베신 공식을 사용할 수도 있습니다.

2. 이러한 열(위도, 경도)을 POINT 유형으로 명명된 한 열에 병합하고 각 행을 하나씩 다시 검색합니다.

3. 여러 점(사용자가 작성한 주택의 좌표)을 국가의 한 구역, 즉 도시의 한 구역의 범주로 분류하고 쿼리가 $latitude와 $landitude를 사용하여 가장 가까운 주택을 볼 수 있도록 어떤 카테고리에 저장되어 있는지 확인하고 모든 행을 검색하지 말고 이 쿼리(좌표)가 속한 섹션만 검색한다.

1번 접근은 테이블의 각 행에 대한 조건 때문에 느리고 하베신 공식을 사용할 경우 다시 느릴 수 있습니다.

ST_Distance를 사용하면 계산이 많아서 다시 느려집니다.

그러나 3번 접근 방식을 사용하는 경우 모든 행을 확인하는 것보다 각 섹션에서 특정 포인트 사용자를 확인하는 것이 더 빠를 수 있습니다.각 홈의 포인트를 설정하는 방법은 알고 있습니다만, 복수의 홈 포지션을 다른 테이블의 섹션으로 작성하는 방법은 모릅니다.

새로운 버전의 MySQL 및 MariaDB Spatial Index의 BTW는 InnoDB에서 지원됩니다.

질문:

1. 1번 접근법이 정말 느립니까, 아니면 다른 ST_* 함수가 모든 행을 하나씩 체크하는 접근법과 같습니까?어떤 게 더 빨라요?

2. 2번 접근법은 단순한 조건 이외의 다른 방법으로 더 빨리 진행됩니까?float 대신 POINT 타입을 사용하고 ST_* 함수를 직접 사용하는 경우 변경사항이 있습니까?알고리즘이 다른지 알고 싶습니다.

3. 3번 접근법이 이들 3가지 접근법 중 가장 빠른 경우 테이블 내의 모든 행을 검색하지 않기 위해 어떻게 점을 분류할 수 있습니까?

4. 공간 인덱스를 사용하여 최대한 빠르게 만들려면 어떻게 해야 합니까?

5. 만약 다른 접근법이 존재하지만 제가 언급하지 않았다면, 어떻게 하면 PHP/Laravel의 MySQL/MariaDB에 좌표를 넣는 것만으로 가장 가까운 집을 얻을 수 있는지 알려주실 수 있을까요?

모두 감사합니다

거리에 대해 어떤 공식을 사용하든 크게 상관없습니다.더 중요한 것은 읽고, 처리하고, 정렬해야 하는 행의 수입니다.가장 좋은 경우 WHERE 절의 조건에 인덱스를 사용하여 처리되는 행 수를 제한할 수 있습니다.위치를 분류해 볼 수 있습니다.다만, 데이터의 성질에 의해서, 정상적으로 동작하는지에 따라 다릅니다.또, 사용하는 「카테고리」도 찾아낼 필요가 있습니다.보다 일반적인 해결책은 SPACE INDEX와 ST_Within() 함수를 사용하는 것입니다.

이제 몇 가지 테스트를 해보죠

내 DB(MySQL 5.7.18)에는 다음 표가 있습니다.

CREATE TABLE `cities` (
    `cityId` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
    `country` CHAR(2) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `city` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `accentCity` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `region` CHAR(2) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `population` INT(10) UNSIGNED NULL DEFAULT NULL,
    `latitude` DECIMAL(10,7) NOT NULL,
    `longitude` DECIMAL(10,7) NOT NULL,
    `geoPoint` POINT NOT NULL,
    PRIMARY KEY (`cityId`),
    SPATIAL INDEX `geoPoint` (`geoPoint`)
) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB

이 데이터는 Free World Citys Database에서 가져온 것이며 3173958(3.1M) 행이 포함되어 있습니다.

주의:geoPoint용장성이 있어, 와 같다.POINT(longitude, latitude).

사용자는 런던 어딘가에 있습니다.

set @lon = 0.0;
set @lat = 51.5;

그리고 가장 가까운 위치를 찾고 싶어합니다.cities테이블.

"심플한" 쿼리는 다음과 같습니다.

select c.cityId, c.accentCity, st_distance_sphere(c.geoPoint, point(@lon, @lat)) as dist
from cities c
order by dist
limit 1

그 결과는

988204 Blackwall 1085.8212159861014

실행 시간: 최대 4.970초

덜 복잡한 함수를 사용하는 경우ST_Distance()실행 시간은 약 4.580초이며, 이는 큰 차이가 없습니다.

테이블에 지리점을 저장할 필요는 없습니다.마찬가지로 사용할 수 있습니다.(point(c.longitude, c.latitude)대신c.geoPoint놀랍게도 더 빠르다(약 3.6초).ST_Distance및 최대 4.0초ST_Distance_Sphere)가 없으면 더 빠를 수 있습니다.geoPoint컬럼이 전혀 없습니다.그러나 사용자가 기다리는 것을 원하지 않으므로 더 잘 할 수 있다면 로그로 응답을 기록하십시오.

이제 SPACE INDEX를 사용하여ST_Within().

가장 가까운 위치를 포함할 폴리곤을 정의해야 합니다.간단한 방법은 ST_Buffer()를 사용하는 것입니다.ST_Buffer()는 32개의 점을 가진 거의 원*에 가까운 폴리곤을 생성합니다.

set @point = point(@lon, @lat);
set @radius = 0.1;
set @polygon = ST_Buffer(@point, @radius);

select c.cityId, c.accentCity, st_distance_sphere(c.geoPoint, point(@lon, @lat)) as dist
from cities c
where st_within(c.geoPoint, @polygon)
order by dist
limit 1

결과는 똑같다.실행 시간은 약 0.000초입니다(내 클라이언트(HeidiSQL)가 말합니다).

* 주의:@radius도 단위로 표기되므로 폴리곤은 원이라기보다는 타원에 가깝습니다.하지만 테스트에서는 항상 간단하고 느린 해결책과 같은 결과를 얻었습니다.생산 코드에 사용하기 전에 엣지 케이스를 더 조사하려고 합니다.

이제 애플리케이션/데이터에 대한 최적의 반경을 찾아야 합니다.너무 작으면 결과가 나오지 않거나 가장 가까운 지점을 놓칠 수 있습니다.너무 크면 행을 너무 많이 처리해야 할 수 있습니다.

다음은 특정 테스트 케이스에 대한 몇 가지 수치입니다.

• @syslog = 0.001: 결과 없음
• @location = 0.01: 정확히 1개소(행운의 종류) - 실행 시간 ~ 0.000초
• @syslog = 0.1: 55개소 - 실행시간 ~ 0.000초
• @syslog = 1.0: 2183 위치 - 실행 시간 ~ 0.030초

경계 상자 및 Haversine

당신의 개요에SELECT지도에 대략적인 정사각형이 그려지는 "경계 상자" 방식을 사용하고 있습니다.하지만 몇 가지 결함이 있습니다.

• 50과 60은 도 단위일 겁니다 10은 km 단위라고 하셨잖아요둘 중 하나를 변환하지 않고 혼합할 수 없습니다.
• 경도 각도가 위도 각도보다 짧다.cos()이 문제를 해결하기 위해 필요합니다.

이 값이 있으면 경계 상자에 도움이 되고 행이 크게 필터링됩니다.그러면 옵션인 Haversine 테스트가 테스트 범위를 반올림합니다.

INDEX(latitude)
INDEX(longitude)

이 어프로치에는 「중간」의 퍼포먼스가 있습니다.인덱스 중 하나가 경계 박스와 함께 사용되기 때문에 후보자는 순식간에 전 세계에서 동서(또는 북남)의 스트라이프로 제한됩니다.하지만 그것은 여전히 많은 후보일 수 있다.

대부분의 행을 필터링함으로써 Haversine 콜의 수는 나쁘지 않습니다.함수의 퍼포먼스에 대해 걱정할 필요는 없습니다.

100만 채의 주택이 있는 경우, 5채의 주택(및 몇 채의 주택이 있는 경우)이 들어 있는 최종 경계 상자에는 아마도 수천 개의 행이 만져질 것입니다.이는 두 가지 색인 중 하나만 사용되기 때문입니다.이것은 백만 개의 행을 모두 가져오고 거리 함수로 각 행을 확인하는 것보다 훨씬 더 좋습니다.

점 및 공간 색인

로의 전환POINT로의 전환이 필요합니다.SPATIAL인덱스. 이 모드에서는ST_Distance_Sphere()는 haversine 대신 사용할 수 있습니다.(주의: 이 기능은 최신 버전에만 존재합니다.)

대부분의 행을 필터링함으로써 에의 콜의 수는ST_Distance또는ST_Distance_Sphere나쁘지 않습니다.기능의 퍼포먼스에 대해서는 걱정하지 마십시오.

SPATIAL검색은 R-Tree를 사용합니다.당신의 질의에 대한 그들의 성과에 대해 좋은 예감이 들지 않습니다.

어프로치 3

다른 점 분류부터 시작하면 복잡성이 증가합니다.또한 인접 영역을 확인하여 인접 점이 있는지 확인해야 합니다.자세한 내용이 없으면 상대적인 성과를 판단할 수 없습니다.

마이 어프로치

임의로 여러 점까지 확장 가능한 복잡한 코드가 있습니다.데이터셋이 RAM에 캐시될 정도로 작기 때문에 과잉일 수 있습니다.http://mysql.rjweb.org/doc.php/latlng

100만 가구의 경우 위의 인덱스 쌍이 "충분히 양호"하므로 "내 알고리즘"에 의존할 필요가 없습니다.알고리즘은 총 행 수에 관계없이 원하는 5개를 얻기 위해 약 20개의 행만 터치합니다.

기타 주의사항

lat/lg와 lat/lg를 모두 저장하는 경우POINT테이블은 부피가 커집니다.바운딩 박스와 바운딩 박스를 혼재시키는 경우는, 이 점에 주의해 주세요.ST기능들.

언급URL : https://stackoverflow.com/questions/51429997/which-approach-is-faster-for-getting-all-pois-from-mysql-mariadb-with-php-larave