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

[Jan Willem van der Lichte]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mobilitydb-users/attachments/20241009/b274dd17/attachment.htm>