Get part of a 3D line based on Z value

[Richard Huesken]

Paul, Darafei,

Many, many thanks for these suggestions. They both work well, and return
identical results (after forcing to 2d). The query now returns within 1
second for 10000 geometries!
Using the M values also provides other opportunities to solve related
queries so there is plenty to investigate...

Kind regards,
Richard.

Op di 3 jun 2025 om 19:12 schreef Darafei "Komяpa" Praliaskouski <
me at komzpa.net>:

> 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/20250604/2d48b27e/attachment.htm>