I have 2 tables in PostGIS:
linestablewith MultiLineString geom
surveypointtablewith Point + elevation attribute
The goal is to rebuild the lines as 3D replacing line's vertices with the closets surveypoint.
Rebuild points as PointZ and get the collection of all points
Get line's vertices as points using
Find closest surveypoint using
Check if the closestpoint is within 1m using
ST_DWithin(surveypoints.geom, linepoints.geom, 1)
Build line using only the closets surveypoints that are within 1m tolerance.
When I use
ST_ClosestPoint(p.3) it drops the z-index of the point and as a result I end up with a 2D line.
When I use
ST_3DClosestPoint it takes wrong points - i.e. if I had point A 1m away in 2D from linepoint but at the same elevation and point B at the exact same coordinates as linepoint but with 10m difference in elevation it would take point A.
DROP TABLE IF EXISTS newlines; CREATE TABLE newlines AS SELECT gid, ST_SetSRID(ST_MakeLine(geom),27700) FROM (SELECT gid, path, ST_AsText( ST_Collect( closestsurveypoint)) AS geom FROM ( SELECT linepoints.gid AS gid, linepoints.path AS path, linepoints.geom AS linepoint, (CASE ST_DWithin( ST_SetSRID(surveypoints.geom, 27700), ST_SetSRID(linepoints.geom, 27700), 1) WHEN TRUE THEN ST_AsText( ST_ClosestPoint( ST_SetSRID(surveypoints.geom, 27700), ST_SetSRID(linepoints.geom, 27700) )) END) AS closestsurveypoint FROM ( SELECT gid, color, (ST_DumpPoints(geom)).path AS path, ST_AsText((ST_DumpPoints(geom)).geom) AS geom FROM linestable) AS linepoints, ( SELECT ST_AsText( ST_Collect( ST_MakePoint( ST_X(geom), ST_Y(geom), elevation))) AS geom FROM surveypointstable) AS surveypoints ) AS newlinepoints GROUP BY gid, path ORDER BY gid, path) AS newlines GROUP BY gid