line_locate_point(Line, Point)

Calculates where on a line (line) a point (point) lies or interpolates it onto the line, then returns the fraction of distance from the start point as a measurement.

The line locate point function returns all points that are closest to the line section specified in the query. This function assumes a 2D world, so points on the other side of the antimeridian from the line may not be returned. If no other geometry is specified (such as a bounding box or a geometry shape), this function queries the whole world.

In order for optimizations to be applied, the section of the line you want to query must be at one end of the line.

Parameters

Parameter	Type	Description
line	lineString	A line defined using the WKT notation; see Points within or intersects with a geometry shape
Point	point	A location defined using the WKT notation; see Points within or intersects with a geometry shape

Returns

double

Example

SELECT * from geospock.default.largetable large 
WHERE line_locate_point(ST_LineFromText('LINESTRING(-50 33, -40 39, -40 40, -41 40)'), ST_Point(large.longitude, large.latitude)) < 0.1 

Usage

For example, the following query finds the points in the bounding box that are closer to the first 10% of the line than the remaining 90%:

SELECT * from geospock.default.largetable large 
WHERE line_locate_point(ST_LineFromText('LINESTRING(-50 33, -40 39, -40 40, -41 40)'), ST_Point(large.longitude, large.latitude)) < 0.1 
AND large.latitude BETWEEN 33.50 AND 33.60 AND large.longitude BETWEEN 135.10 AND 135.20

In order for optimizations to be applied, the section of the line you want to query must be at one end of the line.