Get part of a 3D line based on Z value

[Paul Ramsey]

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.huesken at 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
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250603/48f80fac/attachment.htm>