DATA
I have 2 tables in PostGIS:
linestable
with MultiLineString geomsurveypointtable
with Point + elevation attribute
GOAL
The goal is to rebuild the lines as 3D replacing line's vertices with the closets surveypoint.
LOGIC
Rebuild points as PointZ and get the collection of all points
ST_Collect(ST_MakePoint(ST_X(geom),ST_Y(geom),elevation_att))
Get line's vertices as points using
ST_DumpPoints(geom).geom
Find closest surveypoint using
ST_ClosestPoint(surveypoints.geom, linepoints.geom)
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.
ST_MakeLine(ST_Collect(closestpoints.geom))
PROBLEM
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.
CODE
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