外包框运算

在查询时，可以利用轨迹或几何类型的外包矩形框来辅助查询。例如下图就是一条在x,y,t空间中的运动的轨迹和它对应的外包矩形框。

在Ganos Trajectory模块中，使用BoxNDF类型表示外包框。对于轨迹、几何类型，可以求得其外包框，也可以提取其在特定时间段的子外包框。

• 提取几何类型的外包框，也可以在外包框上附加时间范围（2000-01-01 00:00:10至02:13:20）。

  WITH geom AS(
      SELECT ('POLYGON((12.7243236691148 4.35238368367118,12.9102992732078 1.49748113937676,12.5926592946053 1.67643963359296' ||
              ',12.0197574747333 3.19258554889152,12.7243236691148 4.35238368367118))')::geometry a )
  SELECT ST_MakeBox(a),ST_MakeBox(a,'2000-01-01 00:00:10'::timestamp, '2000-01-01 02:13:20'::timestamp) from geom;

• 提取轨迹类型的外包框，也可以提取轨迹在限定时间内的子轨迹的外包框。

  With traj AS (
      Select ST_makeTrajectory(
          'STPOINT', 
          'LINESTRING(0 0, 50 50, 100 100)'::geometry,
          tsrange('2000-01-01 00:00:00'::timestamp, '2000-01-01 00:01:40'::timestamp),
          '{"leafcount":3,
              "attributes":{
                  "velocity": {"type": "integer","length": 2,"nullable" : true,"value": [120,130,140]}, 
                  "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, 
                  "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, 
                  "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]},
                  "active": {"type": "timestamp","nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}
              }, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]
            }') a )
  SELECT ST_MakeBox(a), ST_MakeBox(a,'1999-12-31 23:00:00'::timestamp, '2000-01-01 00:00:30'::timestamp) from traj;

在较为复杂的应用场景中，对轨迹的处理时常分为两步：

1. 根据轨迹的外包框信息过滤掉大部分的无关数据。

2. 对剩余的数据进行精细处理。

可以假设和查询轨迹相似的轨迹必然与查询轨迹的外包框相交，或是距离在一定范围内，然后只计算符合条件的轨迹和查询之间的相似度，得到一个近似的结果。

• 外包框相交：

  -- 将轨迹SRID统一设置为4326
  UPDATE trajectory_table SET traj = ST_SetSRID(traj,4326) WHERE ST_SRID(traj)!=4326;
  
  With query AS (
    SELECT '{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2020-04-11 17:42:30","end_time":"2020-04-11 17:45:00","spatial":"SRID=4326;LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)","timeline":["2020-04-11 17:42:30","2020-04-11 17:43:30","2020-04-11 17:45:00"],"attributes":{"leafcount":3,"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}}'::trajectory q)
  SELECT ST_lcsDistance(traj, q, 500), trajectory_table.* FROM trajectory_table, query WHERE traj && q ORDER BY ST_lcsDistance(traj, q, 500);

• 外包框空间距离小于500米，时间距离小于1小时：

  -- 将轨迹SRID统一设置为4326
  UPDATE trajectory_table SET traj = ST_SetSRID(traj,4326) WHERE ST_SRID(traj)!=4326;
  
  With query AS (
    SELECT '{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2020-04-11 17:42:30","end_time":"2020-04-11 17:45:00","spatial":"SRID=4326;LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)","timeline":["2020-04-11 17:42:30","2020-04-11 17:43:30","2020-04-11 17:45:00"],"attributes":{"leafcount":3,"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}}'::trajectory q)
  SELECT ST_lcsDistance(traj, q, 500), trajectory_table.* FROM trajectory_table, query WHERE traj && ST_ExpandTemporal(ST_ExpandSpatial(ST_MakeBox(q), 500/110000), 3600) ORDER BY ST_lcsDistance(traj, q, 500);

  说明

  • 由于对轨迹调用ST_Buffer函数时无法使用traj列的索引，因此必须扩展q，将q的外包框扩大相应距离。

  • 由于轨迹使用经纬度坐标，需要先将500米转为经纬度范围，一经纬度大约110km。一小时为3600秒。

常用外包框相交算子：