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 < m...@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 <pram...@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.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: >> >> 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 >> >> >>
