I have an Oracle database table which stores points of a track of moving objects.
| object_id | geometry | timestamp |
|---|---|---|
| 1 | ... | 1970年01月01日T00:01:00 |
| 1 | ... | 1970年01月01日T00:02:00 |
| 1 | ... | 1970年01月01日T00:03:00 |
| 2 | ... | 1970年01月01日T00:01:00 |
| 2 | ... | 1970年01月01日T00:02:00 |
| 2 | ... | 1970年01月01日T00:03:00 |
How can I select only the two points closest to a given timestamp for each object_id or alternatively how can I return the interpolated point between them (which would best solution).
| object_id | interpolated_point |
|---|---|
| 1 | ... |
| 2 | ... |
| 3 | ... |
1 Answer 1
That is best done using the LRS feature.
The first step is to construct a table of tracks, each containing a line geometry, built from all the points in that track.
That line needs to be extended with a M dimension (measure) coordinate, that represents the elapsed time (in decimal seconds typically) from the timestamp of the first point.
Then use the functions of the SDO_LRS package, specifically the SDO_LRS.LOCATE_PT() function, where you pass a relative time value (in decimal seconds). The result will be a point geometry that represents the estimated location of the moving object, calculated by interpolation between the existing vertices.
The LRS feature is explained here: https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/lrs-linear-referencing-system-concepts.html#GUID-9793361B-6C73-41E1-8389-85D8788ED083 and the details on the SDO_LRS package functions are here: https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/SDO_LRS-reference.html#GUID-38A5534F-0B2D-410B-8210-3DFFD4E4AE5B
Explore related questions
See similar questions with these tags.