ST_SetPoint - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

ST_SetPoint

ST_SetPoint returns a linestring with updated coordinates with respect to the input linestring's position as specified by the index. The new coordinates are the coordinates of the input point.

The dimension of the returned geometry is the same as that of the geom1 value. If geom1 and geom2 have different dimensions, geom2 is projected to the dimension of geom1.

Syntax

ST_SetPoint(geom1, index, geom2)

Arguments

geom1

A value of data type GEOMETRY or an expression that evaluates to a GEOMETRY type. The subtype must be LINESTRING.

index

A value of data type INTEGER that represents the position of an index. A 0 refers to the first point of the linestring from the left, 1 refers to the second point, and so on. The index can be a negative value. A -1 refers to the first point of the linestring from the right, -2 refers to the second point of the linestring from the right, and so on.

geom2

A value of data type GEOMETRY or an expression that evaluates to a GEOMETRY type. The subtype must be POINT.

Return type

GEOMETRY

If geom2 is the empty point, then geom1 is returned.

If geom1, geom2, or index is null, then null is returned.

If geom1 is not a linestring, then an error is returned.

If index is not within a valid index range, then an error is returned.

If geom2 is not a point, then an error is returned.

If geom1 and geom2 don't have the same value for the spatial reference system identifier (SRID), then an error is returned.

Examples

The following SQL returns a new linestring where we set the second point of the input linestring with the specified point.

SELECT ST_AsText(ST_SetPoint(ST_GeomFromText('LINESTRING(1 2, 3 2, 5 2, 1 2)'), 2, ST_GeomFromText('POINT(7 9)')));
st_astext ------------- LINESTRING(1 2,3 2,7 9,1 2)

The following SQL example returns a new linestring where we set the third point from the right (the index is negative) of the linestring with the specified point.

SELECT ST_AsText(ST_SetPoint(ST_GeomFromText('LINESTRING(1 2, 3 2, 5 2, 1 2)'), -3, ST_GeomFromText('POINT(7 9)')));
st_astext ------------- LINESTRING(1 2,7 9,5 2,1 2)