ST_LocateBetween

ST_LocateBetween takes a geometry and two M coordinates (measures) as input parameters and returns that part of the given geometry that represents the set of disconnected paths or points between the two M coordinates.

For linestrings, multilinestrings, polygons, and multipolygons, interpolation is performed to compute the result. The resulting geometry is represented in the spatial reference system of the given geometry. If the given geometry is a polygon or multipolygon, then ST_LOCATEBETWEEN is applied to the exterior and interior rings of the geometry.

If none of the parts of the given geometry are in the interval defined by the given M coordinates, then an empty geometry is returned. If the given geometry is null , then null is returned.

The resulting geometry is represented in the most appropriate spatial type. If it can be represented as a point, linestring, or polygon, then one of those types is used. Otherwise, the multipoint, multilinestring, or multipolygon type is used.

Syntax

Parameters

geometry: A value of one of the seven distinct spatial data types that represents the geometry.
startM: A value of type DOUBLE that represents the lower bound of the measure interval. If this value is null, no lower bound is applied.
endM: 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

db2gse.ST_Geometry

Example

In this example, a researcher uses the M coordinate to record data that she collects about pH values. The researcher collects the pH values of the soil at specific locations along a highway. She records the X and Y coordinates of each location and the pH value of the soil.

SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse 
CREATE TABLE sample_lines (id INTEGER, geometry ST_LineString)  
INSERT INTO sample_lines 
  VALUES (1, ST_Geometry(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 in the range 4–6, the researcher uses the following SELECT statement:

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

Results:

  ID       MEAS_BETWEEN_4_AND_6 
--------- ------------------------------    
   1      LINESTRING M (3.00000000 4.33333300 4.00000000,
          3.00000000 3.00000000 6.00000000, 
          4.00000000 4.00000000 6.00000000,
          5.00000000 5.00000000 6.00000000)