The ST_LocateBetween() function

The ST_LocateBetween() function takes an ST_Geometry object and two measure locations and returns an ST_Geometry that represents the set of disconnected paths between the two measure locations.

Syntax

ST_LocateBetween(g1 ST_Geometry, fm double precision, tm double precision)

Usage

If the source geometry dimension is 0, SE_LocateBetween() returns an ST_MultiPoint consisting of all points whose measures lie between the two source measures.

For source geometries whose dimension is greater than 0, SE_LocateBetween() returns an ST_MultiLineString if a path can be interpolated; otherwise, SE_LocateBetween() returns an ST_MultiPoint containing the point locations.

An empty point is returned whenever SE_LocateBetween() cannot interpolate a path or find a location between the measures.

SE_LocateBetween() performs an inclusive search of the geometries; therefore, the geometry measures must be greater than or equal to the from measure and less than or equal to the to measure.

In the following figure, SE_LocateBetween() returns an ST_MultiLineString that is between measures 4.3 and 6.9.
Figure 1: The SE_LocateBetween function

The multiline string starts at point 3 and continues to point 5. At point 5, two strings branch off: one continues to point 9 and the other continues to point 8.

Return type

ST_Geometry

Example

The locatebetween_test table is created with two columns: the gid INTEGER column uniquely identifies each row, while the g1 ST_MultiLineString stores the sample geometry: 
CREATE TABLE locatebetween_test (gid integer, g1 ST_Geometry);
The following INSERT statements insert two rows into the locatebetween_test table. The first row is an ST_MultiLineString and the second is an ST_MultiPoint: 
INSERT INTO locatebetween_test VALUES(
   1,
   ST_MLineFromText('multilinestring m ((10.29 19.23 5,23.82 20.29
6, 30.19 18.47 7,45.98 20.74 8),(23.82 20.29 6,30.98 23.98 7,42.92
25.98 8))',1000)
);

INSERT INTO locatebetween_test VALUES(
   2,
   ST_MPointFromText('multipoint m (10.29 19.23 5,23.82 20.29
6,30.19 18.47 7,45.98 20.74 8,23.82 20.29 6,30.98 23.98 7,42.92
25.98 8)', 1000)
);
In the query, the ST_LocateBetween function locates measures that lie between 6.5 and 7.5, inclusively. The first row returns an ST_MultiLineString containing several linestrings. The second row returns an ST_MultiPoint because the source data was ST_MultiPoint. When the source data has a dimension of 0 (point or multipoint), an exact match is required: 
SELECT gid, ST_LocateBetween(g1,6.5,7.5) Geometry
   FROM locatebetween_test;


gid       1
geometry  1000 MULTILINESTRING M ((27.005 19.38 6.5, 30.19 18.47
7, 38.085 19.6
          05 7.5),(27.4 22.135 6.5, 30.98 23.98 7, 36.95 24.98 7.5)) 

gid       2
geometry  1000 MULTIPOINT M (30.19 18.47 7, 30.98 23.98 7)