Assign Z value to start & end of PostGIS line

Question

I have a line table in my PostGIS database and a table with invert levels for the start and end of each line.

My goal is to create create 3D lines in my table, from my invert data. I know I can use st_makeline to recreate the line with a 3D point at the start and end but I was hoping to be able to run a update just on the Z value for the start and end.

Can this be done or is it easier to just recreate the line.

Answer 1

-You can update a point in a linestring with ST_SetPoint(geometry linestring, integer zerobasedposition, geometry point) and ST_Translate().
-To get the first and last point use st_endpoint() and st_startpoint().
-To get the "index" of the last point use ST_NumPoints(the_geom)-1.
-The first point "index" is 0.

Somethng like that for the first point:

  UPDATE table
  SET the_geom=
st_setpoint(the_geom,0,st_translate(st_firstpoint(the_geom),0,0,Z))  
   FROM ....