ApsaraDB RDS:ST_JaccardSimilarity

構文

record ST_JaccardSimilarity(trajectory tr1, trajectory tr2, double tol_dist,
                            text unit default '{}', interval tol_time default NULL,
                            timestamp ts default '-infinity', timestamp te default 'infinity');

この関数は、以下のフィールドを含むレコードを返します：

パラメーター

仕組み

2 つの集合に対する古典的なジャッカード係数は、共通部分のサイズを和集合のサイズで割った値です。軌跡の場合、これは空間的 (およびオプションで時間的) なマッチングに拡張されます。すなわち、tr2 内に tol_dist メートル以内 (また、tol_time が設定されている場合は、指定された時間枠内) の点が存在する場合に、tr1 内の軌跡上の点は tr2 と交差すると見なされます。

マッチング関係が対称的ではないこと、つまり tr1 が tr2 上のある点の近くを 1 回通過する場合と tr2 が同じ点の近くを 2 回通過する場合とでカウントが異なることから、この関数は単一の値ではなく 2 つの境界を返します。

• jaccard_lower uses the smaller intersection count (min(inter1, inter2)), giving a conservative estimate of overlap. Use this when a confirmed minimum level of similarity is required.

• jaccard_upper uses the larger intersection count (max(inter1, inter2)), giving an optimistic estimate of overlap. Use this to capture the broadest possible match between the two trajectories.

どちらの値も、0 (オーバーラップなし) から 1 (完全なオーバーラップ) の範囲です。

Example

The following example compares two trajectories within a three-day time window, using a 100-meter distance tolerance and a 20-second time tolerance.

WITH traj AS (
  SELECT
    ST_makeTrajectory('STPOINT'::leaftype,
      'SRID=4326;LINESTRING(114.49211 37.97921,114.49211 37.97921,114.49211 37.97921,114.49211 37.97921)'::geometry,
      ARRAY[
        to_timestamp(1590287775) AT TIME ZONE 'UTC',
        to_timestamp(1590287778) AT TIME ZONE 'UTC',
        to_timestamp(1590302169) AT TIME ZONE 'UTC',
        to_timestamp(1590302171) AT TIME ZONE 'UTC'
      ], '{}') a,
    ST_makeTrajectory('STPOINT'::leaftype,
      'SRID=4326;LINESTRING(114.49211 37.97921,114.49211 37.97921,114.49211 37.97921,114.49211 37.97921,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49145 37.97781,114.49211 37.97921,114.49211 37.97921,114.49211 37.97921,114.49211 37.97921,114.49211 37.97921,114.49211 37.97921)'::geometry,
      ARRAY[
        to_timestamp(1590287765) AT TIME ZONE 'UTC',
        to_timestamp(1590287771) AT TIME ZONE 'UTC',
        to_timestamp(1590287778) AT TIME ZONE 'UTC',
        to_timestamp(1590287780) AT TIME ZONE 'UTC',
        to_timestamp(1590295992) AT TIME ZONE 'UTC',
        to_timestamp(1590295997) AT TIME ZONE 'UTC',
        to_timestamp(1590296013) AT TIME ZONE 'UTC',
        to_timestamp(1590296018) AT TIME ZONE 'UTC',
        to_timestamp(1590296025) AT TIME ZONE 'UTC',
        to_timestamp(1590296032) AT TIME ZONE 'UTC',
        to_timestamp(1590296055) AT TIME ZONE 'UTC',
        to_timestamp(1590296073) AT TIME ZONE 'UTC',
        to_timestamp(1590296081) AT TIME ZONE 'UTC',
        to_timestamp(1590296081) AT TIME ZONE 'UTC',
        to_timestamp(1590302169) AT TIME ZONE 'UTC',
        to_timestamp(1590302174) AT TIME ZONE 'UTC',
        to_timestamp(1590302176) AT TIME ZONE 'UTC',
        to_timestamp(1590302176) AT TIME ZONE 'UTC',
        to_timestamp(1590302172) AT TIME ZONE 'UTC',
        to_timestamp(1590302176) AT TIME ZONE 'UTC'
      ], '{}') b
)
SELECT ST_JaccardSimilarity(a, b, 100, '{"unit":"M"}', '20 second',
  '2020-05-23'::timestamptz AT TIME ZONE 'UTC',
  '2020-05-26'::timestamptz AT TIME ZONE 'UTC')
FROM traj;

Output:

       st_jaccardsimilarity
-----------------------------------
 (4,20,4,10,0.2,0.714285714285714)
(1 row)

The result maps to the return fields as follows: