Get part of a 3D line based on Z value

Tue Jun 3 10:11:51 PDT 2025

https://postgis.net/docs/ST_LocateBetweenElevations.html should just do the
trick.

On Tue, Jun 3, 2025 at 7:58 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> 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:
>
>    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/81cb18b7/attachment.htm>