<div dir="ltr"><a href="https://postgis.net/docs/ST_LocateBetweenElevations.html">https://postgis.net/docs/ST_LocateBetweenElevations.html</a> should just do the trick.</div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Tue, Jun 3, 2025 at 7:58 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>If instead of putting your value on the Z, you put it on the M, you could have recourse to the <a href="https://postgis.net/docs/ST_LocateBetween.html" target="_blank">https://postgis.net/docs/ST_LocateBetween.html</a> function. Maybe that?<div><br></div><div>P<br id="m_7160621878743076280lineBreakAtBeginningOfMessage"><div><br><blockquote type="cite"><div>On Jun 3, 2025, at 8:50 AM, Richard Huesken <<a href="mailto:richard.huesken@gmail.com" target="_blank">richard.huesken@gmail.com</a>> wrote:</div><br><div><div dir="ltr">Hi,<div><br></div><div>I have a 3D line, which represents the track of a ship.</div><div>The Z value is set to a an OADate value (see e.g. <a href="https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=net-8.0" target="_blank">https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=net-8.0</a>). </div><div>In this way, I have the date time of each point in the line.</div><div><br></div><div>I need get the first part of the line until a certain moment in time. The result should cut the line on a location where a vertex may not yet exist.</div><div><br></div><div>I have been experimenting with 3D functions and I get the result I need (geomz_wgs_part column in the SQL). </div><div><br></div><div>However:</div><div><ol><li>The approach looks far fetched, I wonder if there is a better/smarter solution<br>Th first step is to create a 2d envelope around the track, and force the z value to the start of the day. This plane will be extruded to a box, based on the given moment of the day.<br>The result is the 3d intersection of the original line and the box.</li><li>The 3D Intersection is rather slow.<br>I'm running this on a Azure Postgres instance, which requires 90% CPU when running this for 10000 tracks. It takes about 3 minutes to complete the query... <br>Where to start tuning? I may provide a dump with the 10000 tracks if necessary...</li></ol>My versions are:</div><div style="padding:0px 0px 0px 2px"><div style="font-family:Consolas;font-size:10pt;white-space:pre-wrap"><div style="margin:0px"><span style="color:rgb(128,128,128)">-- PostgreSQL 16.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit</span></div></div></div><div><span style="color:rgb(128,128,128);font-family:Consolas;font-size:10pt;white-space:pre-wrap">-- POSTGIS="3.3.3 0" [EXTENSION] PGSQL="160" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.3.10" PROJ="9.0.1" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER</span></div><div><br></div><div>The SQL (for a single line) is:</div><div><br></div><div><div style="padding:0px 0px 0px 2px"><div style="font-family:Consolas;font-size:10pt;white-space:pre-wrap"><div style="margin:0px"><span style="color:rgb(128,0,0);font-weight:bold">select</span> i.*</div><div style="margin:0px"> , st_3dintersection(i.geomz_wgs, i.box_of_interest) geomz_wgs_part</div><div style="margin:0px"><span style="color:rgb(128,0,0);font-weight:bold">from</span> (</div><div style="margin:0px"> <span style="color:rgb(128,0,0);font-weight:bold">select</span> b.*</div><div style="margin:0px"> , st_extrude(<span style="color:rgb(0,0,128);font-weight:bold">st_force3dz</span>(<span style="color:rgb(0,0,128);font-weight:bold">st_envelope</span>(b.geomz_wgs), b.day_value), <span style="color:rgb(0,0,255)">0</span>, <span style="color:rgb(0,0,255)">0</span>, b.day_part_value) box_of_interest</div><div style="margin:0px"> <span style="color:rgb(128,0,0);font-weight:bold">from</span></div><div style="margin:0px"> (</div><div style="margin:0px"> <span style="color:rgb(128,0,0);font-weight:bold">select</span> <span style="color:rgb(0,0,128);font-weight:bold">st_geomfromewkt</span>(<span style="color:rgb(0,128,0)">'srid=4326;LINESTRING Z(4.304028511047363 53.749210357666016 45769.7500462963, 4.361361503601074 53.776851654052734 45769.76358796296, 4.435863494873047 53.81759262084961 45769.781863425924, 4.471341609954834 53.83902359008789 45769.79107638889, 4.475761890411377 53.84187698364258 45769.792280092595, 4.532068252563477 53.878421783447266 45769.807337962964, 4.570201873779297 53.90427017211914 45769.81774305556, 4.575718402862549 53.90929412841797 45769.81945601852, 4.586120128631592 53.92267990112305 45769.82388888889, 4.5958099365234375 53.93675994873047 45769.82859953704, 4.602046489715576 53.95013427734375 45769.83280092593, 4.604625225067139 53.95497512817383 45769.834178240744, 4.642298221588135 54.026405334472656 45769.85655092593, 4.654839992523193 54.0516357421875 45769.86440972222, 4.6703200340271 54.085777282714844 45769.87482638889, 4.6709418296813965 54.0881233215332 45769.875393518516, 4.688111782073975 54.15925216674805 45769.896585648145, 4.699371814727783 54.20192337036133 45769.90912037037)'</span>) geomz_wgs</div><div style="margin:0px"> , <span style="color:rgb(0,0,255)">45769.0</span> day_value</div><div style="margin:0px"> , <span style="color:rgb(0,0,255)">0.7916666</span> day_part_value</div><div style="margin:0px"> ) b</div><div style="margin:0px">) i</div></div></div></div><div> </div><div>Thanks for having a look.</div><div>Kind regards,</div><div><br></div><div>Richard Huesken</div><div><br></div></div>
</div></blockquote></div><br></div></div></blockquote></div>