PostGIS 거리 쿼리에 대한 인덱스를 올바르게 설정하는 방법은 무엇입니까? . 대답은 다음과 같은 줄에있는

Record에서 X킬로미터 떨어진 모든 테이블 을 쿼리하고 반환 해야하는 응용 프로그램을 작성 중입니다 PointX. Records과 PointX의 위치를 결정하는 (long/lat)구글 지오 코딩 API에서 제공하는 정보를 제공합니다.

PostGIS를 처음 사용합니다. 빠른 연구 끝에이 질문을 찾았습니다 . 대답은 다음과 같은 줄에있는 것 같습니다.

SELECT *
FROM your_table
WHERE ST_Distance_Sphere(the_geom, ST_MakePoint(your_lon,your_lat)) <= radius_mi * 1609.34

문제는 : GIS에서만 시작하더라도 위의 쿼리를 볼 때 이것이 어떻게 인덱스를 사용할 수 있는지 상상할 수 없습니다. 2 개의 함수 호출이 있습니다. 나는 모든 테이블이 스캔되는 것을 상상한다 Record. 나는 틀리고 싶다 🙂

질문 : PostGIS에 위의 쿼리를 수행 할 수있는 인덱스 유형이 있습니까? 그렇지 않다면 내가 필요한 것을 수행하는 권장 접근법은 무엇입니까?

답변

geometryWGS 1984 지리 데이터 (SRID 4326)를 사용 하는 열이 있는 대형 테이블에서 우수한 측지 쿼리 성능을 얻는 데에는 두 가지 키가 있습니다.

  1. 사용 ST_DWithin가능한 공간 인덱스를 사용하여 검색하고 직교 거리가있는 지리 피쳐를 찾는 함수를 사용하십시오.
  2. 지리 캐스트에 추가 색인을 작성하여 ST_DWithin사용할 수 있습니다.

현실 세계에서 어떤 일이 일어나는지 봅시다. 먼저 백만 개의 임의의 점으로 구성된 테이블을 작성하고 채워야합니다.

DROP TABLE IF EXISTS example1
;

CREATE TABLE example1 (
    idcol   serial      NOT NULL,
    geomcol geometry        NULL,
    CONSTRAINT  example1_pk PRIMARY KEY (idcol),
    CONSTRAINT  enforce_srid CHECK (st_srid(geomcol) = 4326)
)
with (
    OIDS=FALSE
);

INSERT INTO example1(geomcol)
SELECT  ST_SetSRID(
            ST_MakePoint(
            (random()*360.0) - 180.0,
            (acos(1.0 - 2.0 * random()) * 2.0 - pi()) * 90.0 / pi()),
            4326) as geomcol
FROM  generate_series(1, 1000000) vtab;

CREATE INDEX example1_spx ON example1 USING GIST (geomcol);
-- (took about 22 sec)

ST_Distance 쿼리를 실행하면 전체 테이블 스캔이 예상됩니다.

EXPLAIN ANALYZE VERBOSE
SELECT  count(*)
FROM    example1
WHERE   ST_Distance(geomcol::geography,ST_SetSRID(ST_MakePoint(6.9333,46.8167),4326)::geography) < 30 * 1609.34
;

Aggregate  (cost=274167.33..274167.34 rows=1 width=0) (actual time=4940.531..4940.532 rows=1 loops=1)
  Output: count(*)
  ->  Seq Scan on bob.example1  (cost=0.00..273334.00 rows=333333 width=0) (actual time=592.766..4940.509 rows=11 loops=1)
        Output: idcol, geomcol
        Filter: (_st_distance((example1.geomcol)::geography, '0101000020E61000005D6DC5FEB2BB1B40545227A089684740'::geography, 0::double precision, true) < 48280.2::double precision)
        Rows Removed by Filter: 999989
Planning time: 2.137 ms
Execution time: 4940.568 ms

이제 우리가를 사용 ST_DWithin하면 여전히 전체 테이블 스캔을 얻습니다 (더 빠르지 만).

EXPLAIN ANALYZE VERBOSE
SELECT  count(*)
FROM    example1
WHERE   ST_DWithin(geomcol::geography,ST_SetSRID(ST_MakePoint(6.9333,46.8167),4326)::geography,30 * 1609.34)
;

Aggregate  (cost=405867.33..405867.34 rows=1 width=0) (actual time=908.716..908.716 rows=1 loops=1)
  Output: count(*)
  ->  Seq Scan on bob.example1  (cost=0.00..405834.00 rows=13333 width=0) (actual time=38.449..908.700 rows=7 loops=1)
        Output: idcol, geomcol
        Filter: (((example1.geomcol)::geography && '0101000020E61000005D6DC5FEB2BB1B40545227A089684740'::geography) AND ('0101000020E61000005D6DC5FEB2BB1B40545227A089684740'::geography && _st_expand((example1.geomcol)::geography, 48280.2::double precision) (...)
        Rows Removed by Filter: 999993
Planning time: 2.017 ms
Execution time: 908.763 ms

그리고 이것은 마지막 작품입니다-취재 지수 구축 (캐스트 지리) : 

CREATE INDEX example1_gpx ON example1 USING GIST (geography(geomcol));
-- (Takes an extra 13 sec)

EXPLAIN ANALYZE VERBOSE
SELECT  count(*)
FROM    example1
WHERE   ST_DWithin(geomcol::geography,ST_SetSRID(ST_MakePoint(6.9333,46.8167),4326)::geography,30 * 1609.34)
;

Aggregate  (cost=96538.95..96538.96 rows=1 width=0) (actual time=0.775..0.775 rows=1 loops=1)
  Output: count(*)
  ->  Bitmap Heap Scan on bob.example1  (cost=8671.62..96505.62 rows=13333 width=0) (actual time=0.586..0.769 rows=19 loops=1)
        Output: idcol, geomcol
        Recheck Cond: ((example1.geomcol)::geography && '0101000020E61000005D6DC5FEB2BB1B40545227A089684740'::geography)
        Filter: (('0101000020E61000005D6DC5FEB2BB1B40545227A089684740'::geography && _st_expand((example1.geomcol)::geography, 48280.2::double precision)) AND _st_dwithin((example1.geomcol)::geography, '0101000020E61000005D6DC5FEB2BB1B40545227A089684740':: (...)
        Rows Removed by Filter: 14
        Heap Blocks: exact=33
        ->  Bitmap Index Scan on example1_gpx  (cost=0.00..8668.29 rows=200000 width=0) (actual time=0.384..0.384 rows=33 loops=1)
              Index Cond: ((example1.geomcol)::geography && '0101000020E61000005D6DC5FEB2BB1B40545227A089684740'::geography)
Planning time: 2.572 ms
Execution time: 0.820 ms

마지막으로, 옵티마이 저는 공간 인덱스를 사용하고 있으며, 친구 사이의 크기는 3 배입니까?

몇 가지주의 사항 :

  • 나는 데이터베이스 괴상한 사람이므로 가정용 PC에는 데이터베이스 기본 테이블 공간을 위해 16Gb RAM, 6 개의 3.3Ghz 코어 및 256Gb SSD가 있습니다. 귀하의 마일리지가 다를 수 있습니다

  • 캐시에서 “핫”페이지와 관련하여 경기장을 평평하게하기 위해 각 쿼리 전에 생성 SQL을 다시 실행하지만 동일한 임의의 시드가 다른 실행에 사용되지 않았기 때문에 약간 다른 결과를 생성 할 수 있습니다.

그리고 메모 :

  • 동일한 면적 분포 (극쪽으로 편향되지 않음)에 아크 코사인을 사용하도록 원래 {-90, + 90} 위도 범위를 조정했습니다.

답변