ST_LocateBetween or ST_MeasureBetween function

The ST_LocateBetween or ST_MeasureBetween function take a geometry and two M coordinates (measures) as input parameters and returns that part of the specified geometry that represents the set of disconnected paths or points between the two M coordinates.

For curves, multicurves, surfaces, and multisurfaces, interpolation is performed to compute the result. The resulting geometry is represented in the spatial reference system of the specified geometry.

If the specified geometry is a surface or multisurface, then ST_MeasureBetween or ST_LocateBetween will be applied to the exterior and interior rings of the geometry. If none of the parts of the specified geometry are in the interval defined by the specified M coordinates, then an empty geometry is returned. If the specified geometry is null , then null is returned.

If the resulting geometry is not empty, a multipoint or multilinestring type is returned.

Syntax

Parameters

geometry: A value of type ST_Geometry or one of its subtypes that represents the geometry in which those parts with measure values between startMeasure to endMeasure are to be found.
startMeasure: A value of type DOUBLE that represents the lower bound of the measure interval. If this value is null, no lower bound is applied.
endMeasure: A value of type DOUBLE that represents the upper bound of the measure interval. If this value is null, no upper bound is applied.

Return type

ST_Geometry

Example

In the following examples, the lines of results have been reformatted for readability. The spacing in your results will vary according to your online display.

The M coordinate (measure) of a geometry is defined by the user. It is very versatile because it can represent anything that you want to measure; for example, distance along a highway, temperature, pressure, or pH measurements.

This example illustrates the use of the M coordinate to record collected data of pH measurements. A researcher collects the pH of the soil along a highway at specific places. Following his standard operating procedures, he writes down the values that he needs at every place at which he takes a soil sample: the X and Y coordinates of that place and the pH that he measures.



CREATE TABLE sample_lines (id INTEGER, geometry ST_LineString)

INSERT INTO sample_lines
  VALUES (1, ST_LineString ('linestring m (2 2 3, 3 5 3,
                                           3 3 6, 4 4 6,
                                           5 5 6, 6 6 8)', 1 ) )

To find the path where the acidity of the soil varies between 4 and 6, the researcher would use this SELECT statement:


SELECT id, CAST( ST_AsText( ST_MeasureBetween( 4, 6) )
  AS VARCHAR(150) ) MEAS_BETWEEN_4_AND_6
  FROM sample_lines

Results:


ID         MEAS_BETWEEN_4_AND_6
---------- --------------------------------------------------------
         1 MULTILINESTRING M ((3.000000 4.333333 4, 
                               3.000000 3.000000 6, 
                               4.000000 4.000000 6, 
                               5.000000 5.000000 6))

Alternatively, the ST_LocateBetween function can be used for the same purpose:


SELECT id, CAST( ST_AsText( ST_LocateBetween( 4, 6) )
  AS VARCHAR(150) ) LOC_BETWEEN_4_AND_6
  FROM sample_lines

Results:


ID         LOC_BETWEEN_4_AND_6
---------- --------------------------------------------------------
         1 MULTILINESTRING M ((3.000000 4.333333 4, 
                               3.000000 3.000000 6, 
                               4.000000 4.000000 6, 
                               5.000000 5.000000 6))