ST_{2D|2DT|3D|3DT}Intersects_IndexLeft
Checks whether two trajectories intersect on a specified axis, and instructs the query planner to use the index on the column that stores the first trajectory.
Syntax
bool ST_{2D|2DT|3D|3DT}Intersects_IndexLeft(trajectory traj1, trajectory traj2, timestamp ts, timestamp te);Parameters
| Parameter | Description |
|---|---|
traj1 | The trajectory to compare, or the original trajectory that contains the sub-trajectory to compare. |
traj2 | The trajectory to compare, or the original trajectory that contains the sub-trajectory to compare. |
ts | (Optional) The start of the time range used to extract sub-trajectories. |
te | (Optional) The end of the time range used to extract sub-trajectories. |
Description
If you call the ST_{2D|2DT|3D|3DT}Intersects(trajectory traj1, trajectory traj2, timestamp ts, timestamp te) function to compare two sub-trajectories, the function cannot determine which index to use. This ambiguity results in additional computing overhead.
Use ST_{2D|2DT|3D|3DT}Intersects_IndexLeft(...) to explicitly tell the planner to use the index on the column storing traj1. This eliminates the ambiguity and reduces computing overhead.
Example
The following example uses EXPLAIN to confirm that ST_2DIntersects_IndexLeft triggers a Bitmap Index Scan on the GiST index sqltr_test_traj_gist_2dt.
-- The traj column must have a GiST/NDF index for IndexLeft to take effect.
EXPLAIN SELECT count(*) FROM sqltr_test_trajs WHERE ST_2DIntersects_IndexLeft(traj, ST_makeTrajectory('STPOINT', 'LINESTRING(-70 -30, -72 -34, -73 -35)'::geometry, '[2000-01-01 00:01:30, 2000-01-01 00:15:00)'::tsrange,
'{"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"}]}'), ST_PGEpochToTS(0), ST_PGEpochToTs(7000));The query plan confirms the index is used:
QUERY PLAN
------------------
Aggregate (cost=4201.04..4201.05 rows=1 width=8)
-> Bitmap Heap Scan on sqltr_test_trajs (cost=98.64..4199.77 rows=505 width=0)
Recheck Cond: ('BOX2DT(-73 -35 2000-01-01 00:01:29.999994,-70 -30 2000-01-01 00:15:00)'::boxndf &#& traj)
Filter: _st_2dintersects(traj, '{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2000-01-01 00:01:30","end_time":"2000-01-01 00:15:00","spatial":"LINESTRING(-70 -30,-72 -34,-73 -35)","timeline":["2000-01-01 00:01:30","2000-01-01 00:08:15","2000-01-01 00:15:00"],"attributes":{"leafcount":3,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120.0,130.0,140.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120.0,130.0,140.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130","140"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 11:35:00","2010-01-01 12:35:00","2010-01-01 13:30:00"]}},"events":[{"2":"2010-01-02 15:00:00"},{"3":"2010-01-02 15:30:00"}]}}'::trajectory, '2000-01-01 00:00:00'::timestamp without time zone, '2000-01-01 01:56:40'::timestamp without time zone)
-> Bitmap Index Scan on sqltr_test_traj_gist_2dt (cost=0.00..98.51 rows=1515 width=0)
Index Cond: (traj &#& 'BOX2DT(-73 -35 2000-01-01 00:01:29.999994,-70 -30 2000-01-01 00:15:00)'::boxndf)The Bitmap Index Scan on sqltr_test_traj_gist_2dt confirms that the planner used the index on the traj column (the first argument).
Related functions
ST_{2D|2DT|3D|3DT}Intersects: The base intersects function without index hints.ST_makeTrajectory: Constructs a trajectory object from geometry, time range, and attribute data.