Get part of a 3D line based on Z value

[Richard Huesken]

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:

   1. 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.
   2. 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/40a98f31/attachment.htm>