If instead of putting your value on the Z, you put it on the M, you could have recourse to the https://postgis.net/docs/ST_LocateBetween.html function. Maybe that?
P > On Jun 3, 2025, at 8:50 AM, Richard Huesken <richard.hues...@gmail.com> wrote: > > Hi, > > I have a 3D line, which represents the track of a ship. > The Z value is set to a an OADate value (see e.g. > https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=net-8.0). > > In this way, I have the date time of each point in the line. > > 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. > > I have been experimenting with 3D functions and I get the result I need > (geomz_wgs_part column in the SQL). > > However: > The approach looks far fetched, I wonder if there is a better/smarter solution > 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. > The result is the 3d intersection of the original line and the box. > The 3D Intersection is rather slow. > 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... > Where to start tuning? I may provide a dump with the 10000 tracks if > necessary... > My versions are: > -- PostgreSQL 16.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu > 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit > -- 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 > > The SQL (for a single line) is: > > select i.* > , st_3dintersection(i.geomz_wgs, i.box_of_interest) geomz_wgs_part > from ( > select b.* > , st_extrude(st_force3dz(st_envelope(b.geomz_wgs), b.day_value), 0, 0, > b.day_part_value) box_of_interest > from > ( > select st_geomfromewkt('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)') geomz_wgs > , 45769.0 day_value > , 0.7916666 day_part_value > ) b > ) i > > Thanks for having a look. > Kind regards, > > Richard Huesken >
