Use ST_LineInterpolatePoint to find a point on a LineString - PolarDB

Returns a point interpolated at a given fraction along a LineString.

Syntax

geometry  ST_LineInterpolatePoint(geometry  aLinestring, float8  aFraction);
geography ST_LineInterpolatePoint(geography aLinestring, float8  aFraction);

Parameters

Parameter	Type	Description
`aLinestring`	geometry / geography	The LineString to interpolate along.
`aFraction`	float8	The position along the line as a fraction of its total length. Accepts a floating-point number in the range 0 to 1, where 0 is the start and 1 is the end.

Usage notes

The function supports 3D objects and preserves Z coordinates.
The function supports M coordinates.
For the geography type, distances are calculated based on spherical distance.
To find the fraction of a line closest to a given point, use ST_LineLocatePoint.

Examples

Get the midpoint of a line segment

Pass 0.5 as aFraction to get the point at the halfway position.

Geometry:

SELECT ST_AsText(ST_LineInterpolatePoint(geom, 0.5))
FROM (SELECT 'LINESTRING(0 0,2 2)'::geometry AS geom) AS test;

Result:

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

Geography:

SELECT ST_AsText(ST_LineInterpolatePoint(geog, 0.5))
FROM (SELECT 'LINESTRING(0 0,2 2)'::geography AS geog) AS test;

Result:

 POINT(0.99969732796684 1.00015638159834)

Note: The geography result differs from the geometry result because distances are measured along the spheroid rather than a flat plane.

Find the closest point on a line to a given point

Combine ST_LineInterpolatePoint with ST_LineLocatePoint to project a point onto the nearest position on a line.

Geometry:

SELECT ST_AsText(
  ST_LineInterpolatePoint(geom, ST_LineLocatePoint(geom, 'POINT(1 1)'::geometry))
)
FROM (SELECT 'LINESTRING(0 0,0 2)'::geometry AS geom) AS test;

Result:

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

Geography:

SELECT ST_AsText(
  ST_LineInterpolatePoint(geog, ST_LineLocatePoint(geog, 'POINT(1 1)'::geography))
)
FROM (SELECT 'LINESTRING(0 0,0 2)'::geography AS geog) AS test;

Result:

 POINT(0 1.00015229710421)

What's next

ST_LineLocatePoint — Returns the fraction of a line's total length at the point closest to a given geometry.