Use ST_LineInterpolatePoints to interpolate points on a LineString - ApsaraDB RDS

Interpolates one or more points along a LineString at positions specified as a fraction of the total line length.

Syntax

geometry  ST_LineInterpolatePoints(geometry  aLinestring, float8  aFraction, boolean  repeat);
geography ST_LineInterpolatePoints(geography aLinestring, float8  aFraction, boolean  repeat);

Parameters

Parameter	Type	Description
`aLinestring`	geometry / geography	The LineString to interpolate along.
`aFraction`	float8	The fraction at which the point is interpolated along the line. Valid values: 0 to 1.
`repeat`	boolean	Specifies whether to place a point at every multiple of `aFraction` along the line. Default: `true`.

Usage notes

When repeat is true, the function places a point at every location that is a multiple of aFraction along the line.
When repeat is false, at most one point is returned.
If the result contains zero or one point, the function returns a point object. If the result contains more than one point, the function returns a MultiPoint object.
Supports 3D geometries. Z coordinates are preserved in the output.
Supports M coordinates.
For the geography variant, distances are calculated using spherical distance.

Examples

Interpolate points every 20% along a geometry line

With repeat set to true (the default), the function places a point at 20%, 40%, 60%, 80%, and 100% of the line length.

SELECT ST_AsText(ST_LineInterpolatePoints('LINESTRING(0 0,0 5)', 0.20));

Output:

         st_astext
---------------------------------
 MULTIPOINT(0 1,0 2,0 3,0 4,0 5)
(1 row)

For the geography variant, distances are measured along the sphere, so the coordinates differ slightly from the planar result.

SELECT ST_AsText(ST_LineInterpolatePoints('LINESTRING(0 0,0 5)'::geography, 0.20));

Output:

                                                  st_astext
-----------------------------------------------------------------------------------------------
 MULTIPOINT((0 1.00002443285827),(0 2.00004274948544),(0 3.00004884128919),(0 4.00003661494431),(0 5))

Interpolate a single point with `repeat` set to `false`

With repeat set to false, the function returns only the first matching point.

SELECT ST_AsText(ST_LineInterpolatePoints('LINESTRING(0 0,0 5)', 0.20, false));

Output:

 st_astext
------------
 POINT(0 1)
(1 row)

SELECT ST_AsText(ST_LineInterpolatePoints('LINESTRING(0 0,0 5)'::geography, 0.20, false));

Output:

          st_astext
---------------------------
 POINT(0 1.00002443285827)
(1 row)