Description

Replace point N of linestring with given point. Index is 0-based.Negative index are counted backwards, so that -1 is last point. This is especially useful in triggers when trying to maintain relationship of joints when one vertex moves.

Availability: 1.1.0

Updated 2.3.0 : negative indexing

This function supports 3d and will not drop the z-index.

Examples

Change first point in line string from -1 3 to -1 1.

SELECT ST_SetPoint('LINESTRING(-1 2,-1 3)', 0, 'POINT(-1 1)');

LINESTRING(-1 1,-1 3)

010200000002000000000000000000F0BF000000000000F03F000000000000F0BF0000000000000840

Figure

Change last point in a line string (let's play with 3D linestring this time)

SELECT ST_SetPoint(foo.geom, ST_NumPoints(foo.geom) - 1, 'POINT(-1 1 3)')
FROM (SELECT 'LINESTRING(-1 2 3,-1 3 4,5 6 7)'::geometry As geom) As foo;

LINESTRING(-1 2 3,-1 3 4,-1 1 3)

010200008003000000000000000000F0BF00000000000000400000000000000840000000000000F0BF00000000000008400000000000001040000000000000F0BF000000000000F03F0000000000000840

Figure

SELECT ST_SetPoint(g, -3, p)
FROM ST_GeomFromText('LINESTRING(0 0,1 1,2 2,3 3,4 4)') AS g
    CROSS JOIN ST_PointN(g, 1) as p;

LINESTRING(0 0,1 1,0 0,3 3,4 4)

01020000000500000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000000000000000000000000000000000000840000000000000084000000000000010400000000000001040

Figure

See Also

ST_AddPoint, ST_NPoints, ST_NumPoints, ST_PointN, ST_RemovePoint