ST_LocateBetween takes a linestring, point, or multipoint column, a start m-value, an end m-value, and an optional offset column, and returns an array of geometries. The input geometry must have m-values. The output represents all parts of the input geometry within the inclusive range between the start and end m-values.

Locations along line segments are interpolated. Neither query boundary needs to occur at an existing vertex or within the input's m-value range; any overlapping portion is returned. Matching points are grouped into a multipoint, and matching line portions are grouped into a linestring in the output array. For a valid, nonempty input geometry with m-values, the function returns an empty array if there are no matches. Providing a start m-value greater than the end m-value will not change the order or direction of the output geometry. A positive offset value will return geometries shifted to the left of the input line, while a negative offset value will return geometries shifted to the right of the input line. For point and multipoint input geometries, specifying a non-zero offset value will return null.

Here is a table describing the different locate functions and their primary differences.

One query m-valueTwo query m-values (start/end)
Returns geometry columnST_LocatePointAlongST_LocateLineBetween
Returns array columnST_LocateAlongST_LocateBetween
FunctionSyntax
Pythonlocate_between(geometry, start_measure, end_measure, offset = 0)
SQLST_LocateBetween(geometry, start_measure, end_measure, offset = 0)
ScalalocateBetween(geometry, startMeasure, endMeasure, offset)

For more details, go to the GeoAnalytics Engine API reference for locate_between.

Examples

PythonPythonSQLScala
Use dark colors for code blocksCopy
1
2
3
4
5
6
7
8
9
10

from geoanalytics.sql import functions as ST

LINE_M_WKT = [('LINESTRING M (10 10 10, 10 50 20, 50 50 30, 50 10 40)',)]

df = spark.createDataFrame(LINE_M_WKT, ["wkt"]) \
    .withColumn("geometry", ST.line_from_text("wkt")) \
    .withColumn("locate_between", ST.locate_between("geometry", 15, 25, -5))

df.select("locate_between").show(truncate = False)
Result
Use dark colors for code blocksCopy
1
2
3
4
5
+------------------------------------------------------------+
|locate_between                                              |
+------------------------------------------------------------+
|[{"hasM":true,"paths":[[[15,30,15],[15,45,20],[30,45,25]]]}]|
+------------------------------------------------------------+

Here is a visual representation of the code snippet above.

Version table

ReleaseNotes

2.2.0

Python, SQL, and Scala functions introduced

Your browser is no longer supported. Please upgrade your browser for the best experience. See our browser deprecation post for more details.