<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 lang="EN-US" style="font-family:"Courier New"">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 lang="EN-US" style="font-family:"Courier New"">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 lang="EN-US" style="font-family:"Courier New""> datum as rr_datum,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""> bestand as rr_bestand,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""> 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 lang="EN-US" style="font-family:"Courier New""> 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 lang="EN-US" style="font-family:"Courier New"">FROM ruwe_rit_data<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New"">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 lang="EN-US" style="font-family:"Courier New""><span> </span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New"">CREATE INDEX </span>
<span lang="EN-US" style="font-family:"Courier New"">rr_trips_totaal</span>
<span lang="EN-US" style="font-family:"Courier New"">_idx ON </span>
<span lang="EN-US" style="font-family:"Courier New"">rr_trips_totaal</span>
<span lang="EN-US" style="font-family:"Courier New""> USING
GiST(rr_trip);</span></p><p class="gmail-MsoNoSpacing" style="margin:0cm;font-family:"Aptos",sans-serif"><font size="2"><span lang="EN-US" style="font-family:"Courier New""><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 lang="EN-US" style="font-family:times new roman,serif">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 size="2" style="font-family:times new roman,serif"><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 size="2" style="font-family:times new roman,serif"><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 size="2" style="font-family:times new roman,serif"><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 lang="EN-US" style="font-family:"Courier New""><br></span></p><p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New"">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 lang="EN-US" style="font-family:"Courier New""> a.rr_id,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""> a.rr_datum,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""> a.rr_bestand,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""> a.rr_traj,<span></span></span></p><p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New"">case<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""> 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 lang="EN-US" style="font-family:"Courier New""> 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 lang="EN-US" style="font-family:"Courier New"">end case<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New"">from </span>
<span lang="EN-US" style="font-family:"Courier New"">rr_trips_totaal</span>
<span lang="EN-US" style="font-family:"Courier New""> a,<span></span></span></p>
<p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New"">(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 lang="EN-US" style="font-family:"Courier New"">(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 lang="EN-US" style="font-family:"Courier New""><br></span></p><p class="gmail-MsoNoSpacing" style="margin:0cm;font-size:11pt;font-family:"Aptos",sans-serif"><span lang="EN-US" style="font-family:"Courier New""></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 lang="EN-US" style="font-family:times new roman,serif"><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 face="Arial" size="2"><span style="font-family:Arial;font-size:10pt"><br><br></span></font></div></div></div>