PolarDB:Calculate bounding boxes

When you run a query, you can use the bounding box of a trajectory or a geometry to facilitate the query. The following figure shows the trajectory and bounding box of an object that moves on the x-axis, y-axis, and t-axis.

In the trajectory module of GanosBase, the BoxNDF data type is used to indicate the bounding box. You can calculate the bounding box of a trajectory or a geometry. You can also extract the bounding box of the trajectory or geometry within a specific period of time.

• If you extract the bounding box of a geometry, you can also attach a time range to the bounding box, such as 2000-01-01 00:00:10 to 2000-01-01 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;

• You can extract the bounding box of a trajectory. You can also extract the bounding box of the trajectory within a specific period of time.

  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;

In some complex scenarios, the following steps are required to process a trajectory:

1. Filter out irrelevant data based on the bounding box of the trajectory.

2. Process the remaining data at fine granularities.

Assume that the trajectory that is similar to the input trajectory must intersect the bounding box of the input trajectory or locates within a certain distance. You need to only calculate the similarity between the trajectory that meets the preceding requirements and the input trajectory to obtain the result.

• The trajectory that is similar to the input trajectory intersects the bounding box of the input trajectory.

  -- Set the spatial reference identifier (SRID) of the trajectories to 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);

• The spatial distance from the trajectory that is similar to the input trajectory to the bounding box of the input trajectory is less than 500 meters, and the time distance is less than 1 hour.

  -- Set the SRID of the trajectories to 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);

  Note

  • If you call the ST_Buffer function to process the trajectory, the index for the traj column cannot be used. You must expand q to extend the bounding box of q by the corresponding distance.

  • Trajectories use longitudes and latitudes. You must express 500 meters based on the longitudes and latitudes. At the equator, a degree of longitude is the same as a degree of latitude, which is about 110 kilometers. One hour is 3,600 seconds.

Common intersection operators for bounding boxes: