[SQL] 집계 (X, Y)에서의 PostgreSQL 점군 좌표

집계 (X, Y)에서의 PostgreSQL 점군 좌표

나는 다음과 같은 단순화 된 구조의 PostgreSQL의 데이터베이스 테이블을 가지고 :

기본적으로이 테이블은 장치 이차원 웨이 데이터를 많이 포함한다. 지금은 출력 좌표의 수를 줄이는 쿼리를 디자인하고 싶다. 그것은 (임의의 X에 대한, Y 임계치) 근처의 좌표를 집계한다 예 :

행 1 : DEVICE1 603 1205

행 2 : DEVICE1 604 1204

임계 값이 5이면 편차가 5 이하이므로, 이들 두 개의 행이 응집한다. 일반적으로 PostgreSQL을 또는 SQL에서이 작업을 수행하는 방법에 어떤 생각?

해결법

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

   1.사용하여 자주 내장 간과하여 집계와 함께 기능 width_bucket () :

   사용하여 자주 내장 간과하여 집계와 함께 기능 width_bucket () :

   좌표가 2000 0, 말에서 실행하고 단일 지점에 5의 사각형 내에서 모든 것을 통합 할 경우에, 나는 10 (5 * 2) 등의 그리드를 배치합니다 :

   SELECT device_id
        , width_bucket(pos_x, 0, 2000, 2000/10) * 10 AS pos_x
        , width_bucket(pos_y, 0, 2000, 2000/10) * 10 AS pos_y
        , count(*) AS ct -- or any other aggregate
   FROM   tbl
   GROUP  BY 1,2,3
   ORDER  BY 1,2,3;
   

   입증 된 바와 같이 격자에 의해 당신이 GROUP을 할 수 오류를 최소화하지만, 실제 평균 좌표에 저장하기 :

   SELECT device_id
        , avg(pos_x)::int AS pos_x   -- save actual averages to minimize error
        , avg(pos_y)::int AS pos_y   -- cast if you need to
        , count(*)        AS ct      -- or any other aggregate
   FROM   tbl
   GROUP  BY
          device_id
        , width_bucket(pos_x, 0, 2000, 2000/10) * 10  -- aggregate by grid
        , width_bucket(pos_y, 0, 2000, 2000/10) * 10
   ORDER  BY 1,2,3;
   

   sqlfiddle 함께 모두 보여.

   음,이 특별한 경우는 더 간단 할 수있다 :

   ...
   GROUP  BY
          device_id
        , (pos_x / 10) * 10          -- truncates last digit of an integer
        , (pos_y / 10) * 10
   ...
   

   (10)의 데모 격자 크기가 편리 진수 시스템과 일치해서 그러나입니다. 17 무언가의 격자 크기와 같은 시도 ...

   당신은 추출물 ( '1970년 1월 1일'이후의 초 수) 유닉스 시대로 변환하여 커버 날짜와 타임 스탬프 값이 방법을 확장 할 수 있습니다 ().

   SELECT extract(epoch FROM '2012-10-01 21:06:38+02'::timestamptz);
   

   작업이 완료되면, 시간대와 타임 스탬프 결과 다시 변환 :

   SELECT timestamptz 'epoch' + 1349118398 * interval '1s';
   

   또는 단순히) (TO_TIMESTAMP :

   SELECT to_timestamp(1349118398);
   

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

   2.

   select [some aggregates] group by (pos_x/5, pos_y/5); 
   

   어디 대신 5의 당신은 당신이 필요로 얼마나 많은 집계 따라 수에는 제한이 없습니다 /

from https://stackoverflow.com/questions/12641764/aggregating-x-y-coordinate-point-clouds-in-postgresql by cc-by-sa and MIT license