[Mobilitydb-users] get a geom of a part of a trajectory

[SCHOEMANS Maxime]

Hi Jan,

On 09/10/2024 13:47, Jan Willem van der Lichte via Mobilitydb-users wrote:
> As far as I understand the atGeometry() returns a (point)geometry

The atGeometry function actually returns a tgeompoint restricted to the spatial extent given as second argument.
So, if your trips pass through a given linestring only once, atGeometry(trip, line) will be a tgeompoint with a single instant.
The result of 'atGeometry(...) < atGeometry(...)', however, will not give you anything meaningful.
But you can indeed get the time at which they pass the linestring using 'startTimestamp(atGeometry(trip, line))'.

> The direction is created in the case in the query below
> I'm not sure if this is the right way to do this (should I use time?)

I believe there are multiple ways to get the information about 'direction':

1. If all your trips go path through your blade in mainly two directions, then you might be able to split them by 'azimuth'.
That is, find the direction of their movement at the time at which they pass the 'blade' using the azimuth(tgeompoint) [1] function.
This function a tfloat representing the direction of movement of a tpoint.
By combining azimuth and valueAtTimestamp, you can then get the direction of movement at a specific time (time at which the trip passes through your 'blade').
Then you can use the MATCH CASE again, but on ranges or azimuth values.

2. Another idea, which you already had is to compare the time at which your trips pass through the two linestrings 'lijn1' and 'lijn2'.
For this you should use the operator 'trip1 <<# trip2' [2], which returns True only if trip1 end before trip2 starts.
If you use the results of atGeometry(trip, lijn1) as trip1 and atGeometry(trip, lijn2) as trip2, then both trip1 and trip2 should contain only a single instant and 'trip1 <<# trip2' will thus be true if the trip passed lijn1 before lijn2.
This is thus equivalent as what you were trying, but you should be using '<<#' instead of '<'.

> Is it possible to get the geometry from startpoint to 'cut-point' (or start-time- to 'cut-time') and from 'cut-point/time' to end-point/time.

Once you know the time at which a trip passes through your 'blade' ('cut-time'), you can split this trip into two as such:
 - before cut-point: 'atTime(trip, span(startTimestamp(trip), cut-time))'
 - after cut-point:  'atTime(trip, span(cut-time, endTimestamp(trip))'

I hope this answers your question.
Let me know if there is anything else I can help you with.

Best,
Maxime

[1]: https://mobilitydb.github.io/MobilityDB/master/ch08s04.html
[2]: https://mobilitydb.github.io/MobilityDB/master/ch03s09.html

On 09/10/2024 13:47, Jan Willem van der Lichte via Mobilitydb-users wrote:

Hi List

I have a question about splitting a trajectory into 2 parts by a "blade" linestring, and store both parts of that trajectory as a geometry in seperate fields (left_geometry and right_geometry)
First of all I made a table with all trajectories


CREATE TABLE rr_trips_totaal(rr_id, rr_datum, rr_bestand, rr_trip, rr_traj) as

SELECT ritid as rr_id,

  datum as rr_datum,

  bestand as rr_bestand,

  tgeompoint_seq(array_agg(tgeompoint_inst( ST_Transform(geometry, 3857), date) ORDER BY date)),

  trajectory(tgeompoint_seq(array_agg(tgeompoint_inst( ST_Transform(geometry, 3857), date) ORDER BY date)))::geometry

FROM ruwe_rit_data

GROUP BY ritid, datum, bestand;



CREATE INDEX  rr_trips_totaal _idx ON  rr_trips_totaal USING GiST(rr_trip);


First of all I try to find the direction of the trip going  through my blade

Therefor I created the table "splitsing" with two records containing linegeometries called "lijn1" and "lijn2"


As far as I understand the atGeometry() returns a (point)geometry

the direction is created int het case in the query below

I'm not sure if this is the right way to do this (should I use time?)


SELECT  row_number() over () as id,

   a.rr_id,

   a.rr_datum,

   a.rr_bestand,

   a.rr_traj,

case

   when atGeometry(a.rr_trip, b.geom) < atGeometry(a.rr_trip, c.geom) then 'richting1'

   when atGeometry(a.rr_trip, b.geom) > atGeometry(a.rr_trip, c.geom) then 'richting2'

end case

from  rr_trips_totaal a,

(select * from splitsing where naam='lijn1' and locatie='bij kattenrug') as b,

(select * from splitsing where naam='lijn2' and locatie='bij kattenrug') as c


at this point I have a data set of  rr_trips_totaal including the field "case" in which the direction is stored.


Now I want to cut the trip into two parts to the geometry fields "trip_before_split" and "trip_after_split" and here i'm getting confused.

I tried something with the  st_dump(st_split()), but in some case there were a lot more than 2 path part, so that didn't work
(st_dump(st_split(a.rr_traj, b.geom))).path[1] As path,
(st_dump(st_split(a.rr_traj, b.geom))).geom As deelgeom

Is it possible to get the geometry from startpoint to 'cut-point' (or start-time- to 'cut-time') and from 'cut-point/time' to end-point/time.
Or does anyone know an example of such a query?
Have I overlooked a function te get a part of a trajectory?

Any help would be nice

--
Met groet,
Jan Willem




_______________________________________________
Mobilitydb-users mailing list
Mobilitydb-users at lists.osgeo.org<mailto:Mobilitydb-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/mobilitydb-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mobilitydb-users/attachments/20241009/808a75a8/attachment-0001.htm>