<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<font face="monospace">Hi Jan,<br>
</font><font face="monospace"><br>
On 09/10/2024 13:47, Jan Willem van der Lichte via Mobilitydb-users wrote:<br>
> As far as I understand the atGeometry() returns a (point)geometry<br>
<br>
The atGeometry function actually returns a tgeompoint restricted to the spatial extent given as second argument.<br>
So, if your trips pass through a given linestring only once, atGeometry(trip, line) will be a tgeompoint with a single instant.<br>
The result of 'atGeometry(...) < atGeometry(...)', however, will not give you anything meaningful.<br>
But you can indeed get the time at which they pass the linestring using 'startTimestamp(atGeometry(trip, line))'.<br>
<br>
> The direction is created in the case in the query below<br>
> I'm not sure if this is the right way to do this (should I use time?)<br>
<br>
I believe there are multiple ways to get the information about 'direction':<br>
<br>
1. If all your trips go path through your blade in mainly two directions, then you might be able to split them by 'azimuth'.<br>
That is, find the direction of their movement at the time at which they pass the 'blade' using the azimuth(tgeompoint) [1] function.<br>
This function a tfloat representing the direction of movement of a tpoint.<br>
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').<br>
Then you can use the MATCH CASE again, but on ranges or azimuth values.<br>
<br>
2. Another idea, which you already had is to compare the time at which your trips pass through the two linestrings 'lijn1' and 'lijn2'.<br>
For this you should use the operator 'trip1 <<# trip2' </font><font face="monospace">[2]</font><font face="monospace">, which returns True only if trip1 end before trip2 starts.<br>
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 '</font><font face="monospace">trip1 <<# trip2' will thus be true if the trip passed lijn1 before
lijn2.<br>
This is thus equivalent as what you were trying, but you should be using '<<#' instead of '<'.<br>
</font><font face="monospace"><br>
> 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.<br>
<br>
Once you know the time at which a trip passes through your 'blade' ('cut-time'), you can split this trip into two as such:<br>
- before cut-point: 'atTime(trip, span(startTimestamp(trip), </font><font face="monospace">cut-time</font><font face="monospace">))'<br>
- after cut-point: 'atTime(trip, span(</font><font face="monospace">cut-time</font><font face="monospace">, endTimestamp(trip))'<br>
<br>
I hope this answers your question.<br>
Let me know if there is anything else I can help you with.<br>
<br>
Best,<br>
Maxime<br>
<br>
[1]: <a class="moz-txt-link-freetext" href="https://mobilitydb.github.io/MobilityDB/master/ch08s04.html">
https://mobilitydb.github.io/MobilityDB/master/ch08s04.html</a><br>
[2]: <a class="moz-txt-link-freetext" href="https://mobilitydb.github.io/MobilityDB/master/ch03s09.html">
https://mobilitydb.github.io/MobilityDB/master/ch03s09.html</a><br>
</font><br>
<div class="moz-cite-prefix">On 09/10/2024 13:47, Jan Willem van der Lichte via Mobilitydb-users wrote:<br>
</div>
<blockquote type="cite" cite="mid:CALNh9F_WHBL7RJZ2ura6CNgpDA7u0wYOrFSnnOgd3quORqBChg@mail.gmail.com">
<div dir="ltr">
<div><br>
</div>
<div><span style="font-family:times new roman,serif">Hi List <br>
</span></div>
<div><span style="font-family:times new roman,serif"><br>
</span></div>
<div><font size="2"><span style="font-family:times new roman,serif">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)
</span></font></div>
<div><font size="2"><span style="font-family:times new roman,serif">First of all I made a table with all trajectories</span><br>
</font></div>
<div><br>
</div>
<div>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">CREATE TABLE rr_trips_totaal(rr_id, rr_datum, rr_bestand, rr_trip, rr_traj) as<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">SELECT ritid as rr_id,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> datum as rr_datum,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> bestand as rr_bestand,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> tgeompoint_seq(array_agg(tgeompoint_inst( ST_Transform(geometry, 3857), date) ORDER BY date)),<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> trajectory(tgeompoint_seq(array_agg(tgeompoint_inst( ST_Transform(geometry, 3857), date) ORDER BY date)))::geometry
<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">FROM ruwe_rit_data<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">GROUP BY ritid, datum, bestand;<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"><span> </span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">CREATE INDEX </span> <span style="font-family:"Courier New"" lang="EN-US">
rr_trips_totaal</span> <span style="font-family:"Courier New"" lang="EN-US">_idx ON </span>
<span style="font-family:"Courier New"" lang="EN-US">rr_trips_totaal</span> <span style="font-family:"Courier New"" lang="EN-US">
USING GiST(rr_trip);</span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-family:"Aptos",sans-serif"><font size="2"><span style="font-family:"Courier New"" lang="EN-US"><br>
</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-family:"Aptos",sans-serif"><font size="2"><span style="font-family:times new roman,serif"><span lang="EN-US">First of all I try to find the direction of the trip going through my blade
<br>
</span></span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm"><font size="2"><span style="font-family:times new roman,serif" lang="EN-US">Therefor I created the table "splitsing" with two records containing linegeometries called "lijn1" and "lijn2"</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:arial,sans-serif"><span lang="EN-US"><br>
</span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-family:"Aptos",sans-serif"><font style="font-family:times new roman,serif" size="2"><span lang="EN-US">As far as I understand the atGeometry() returns a (point)geometry </span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm"><font style="font-family:times new roman,serif" size="2"><span lang="EN-US">the direction is created int het case in the query below</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<font style="font-family:times new roman,serif" size="2"><span lang="EN-US">I'm not sure if this is the right way to do this (should I use time?)<br>
</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
</p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"><br>
</span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">SELECT<span> </span>row_number() over () as id,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> a.rr_id,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> a.rr_datum,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> a.rr_bestand,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> a.rr_traj,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">case<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> when atGeometry(a.rr_trip, b.geom) < atGeometry(a.rr_trip, c.geom) then 'richting1'<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"> when atGeometry(a.rr_trip, b.geom) > atGeometry(a.rr_trip, c.geom) then 'richting2'<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">end case<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">from </span> <span style="font-family:"Courier New"" lang="EN-US">
rr_trips_totaal</span> <span style="font-family:"Courier New"" lang="EN-US">a,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">(select * from splitsing where naam='lijn1' and locatie='bij kattenrug') as b,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US">(select * from splitsing where naam='lijn2' and locatie='bij kattenrug') as c</span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"><br>
</span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif">
<span style="font-family:"Courier New"" lang="EN-US"></span><font size="2">at this<span style="font-family:times new roman,serif"> point I have a data set of rr_trips_totaal including the field "case" in which the direction is stored.</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm"><font size="2"><span style="font-family:times new roman,serif"><br>
</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm"><font size="2"><span style="font-family:times new roman,serif">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.</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm"><font size="2"><span style="font-family:times new roman,serif">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<br>
</span></font></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm"></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif" lang="EN-US"><span></span></span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif">(st_dump(st_split(a.rr_traj, b.geom))).path[1] As path,<br>
(st_dump(st_split(a.rr_traj, b.geom))).geom As deelgeom<span lang="EN-US"><span></span></span></span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif"><br>
</span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif">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.</span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif">Or does anyone know an example of such a query?</span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif">Have I overlooked a function te get a part of a trajectory?<br>
</span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif"><br>
</span></font></p>
<p class="MsoNormal" style="margin:0cm;line-height:normal"><font size="2"><span style="font-family:times new roman,serif">Any help would be nice<br>
</span></font></p>
</div>
<div></div>
<br>
<span class="gmail_signature_prefix">-- </span><br>
<div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature">
<div dir="ltr">Met groet,<br>
Jan Willem<font size="2" face="Arial"><span style="font-family:Arial;font-size:10pt"><br>
<br>
</span></font></div>
</div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
Mobilitydb-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Mobilitydb-users@lists.osgeo.org">Mobilitydb-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/mobilitydb-users">https://lists.osgeo.org/mailman/listinfo/mobilitydb-users</a>
</pre>
</blockquote>
<br>
</body>
</html>