Extract a LineString from LineString by two Points

Question

I am using PostGIS2.0 and PostgreSQL9.3. I have LineString in Spatial-Ref 4326 like

geom:= ST_GEOMETRYFROMTEXT('LINESTRING(60.7014631515719 56.8441322356241,60.7023117507097 56.8445673405349,60.702948200063 56.8447993944193,60.703902874093 56.8448574076656,60.706236521722 56.8447993944193,60.7094187684889 56.8449444273664,60.7121236782406 56.8450894597515',4326);

And 2 Points in Spatial-Ref 4326 like

point1:= ST_GEOMETRYFROMTEXT('POINT(60.703902874093 56.8448574076656)', 4326);
point2:= ST_GEOMETRYFROMTEXT('POINT(60.7023117507097 56.8445673405349)', 4326);

Both points have been extracted from given LineString. So it is guaranteed, that both points are inside the given LineString. No neared points will be used.

It is not guaranteed, that the points are in the same order like in given LineString (see example: point1 is point #4 and point2 is point #2 of the LineString). They can be ascending or descending.

Is there any function or operator to extract a LineString from a given LineString by the points?

I want to do someting like

sub_geom:= ST_???(geom, point1, point2);

to get a LineString geometry that contains all Points from the given geometry from point1 to point2 (points are included)?

Expected result:

sub_geom == ST_GEOMETRYFROMTEXT('LINESTRING(60.7023117507097 56.8445673405349,60.702948200063 56.8447993944193,60.703902874093 56.8448574076656',4326) 

The order of the points of the result geometry does not matter. It can be ascending or descending. The result is only used for visualisation.

It would be nice, if you can show me a solution for my problem.

Answer 1

I don't check it but something like this code should made the trick:

WITH geoms AS (

SELECT 
    ST_GEOMETRYFROMTEXT('LINESTRING(60.7014631515719 56.8441322356241,60.7023117507097 56.8445673405349,60.702948200063 56.8447993944193,60.703902874093 56.8448574076656,60.706236521722 56.8447993944193,60.7094187684889 56.8449444273664,60.7121236782406 56.8450894597515)',4326) AS geom,
    ST_GEOMETRYFROMTEXT('POINT(60.703902874093 56.8448574076656)', 4326) AS point1,
    ST_GEOMETRYFROMTEXT('POINT(60.7023117507097 56.8445673405349)', 4326) AS point2
)

SELECT ST_Line_Substring(geom, ST_Line_Locate_Point(geom, point2), ST_Line_Locate_Point(geom, point1)) FROM geoms;

Take into account that the second argument in ST_Line_Substring should be always smaller that third argument, so you should check it first

Answer 2

Thank you for your answer.

Your hint helped me to find a solution. I created a userdefined function for extracting:

CREATE OR REPLACE FUNCTION ExtractLineStringByPoints(geom geometry, point1 geometry, point2 geometry) 
RETURNS geometry AS
$BODY$
DECLARE
  d1 double precision;
  d2 double precision;
  b1 boolean;
  b2 boolean;
  p geometry;
  numpoints integer;
  i integer;
  srid1 integer;
  srid2 integer;
  srid3 integer;
BEGIN
  srid1 = ST_SRID(geom);
  srid2 = ST_SRID(point1);
  srid3 = ST_SRID(point2);
  IF srid1 & srid2 & srid3 = srid1 THEN -- all SRID have to be the same. ST_LineSubstring() may get along with different SRIDs. But i want same SRID to prevent using miscalculated points!
    b1:=false;
    b2:=false;
    numpoints:= ST_NumPoints(geom);
    FOR i IN 1..numpoints BY 1 LOOP
      p:= ST_PointN(geom, i);
      IF p = point1 THEN
        b1:=true;
      END IF;
      IF p = point2 THEN
        b2:=true;
      END IF;
      IF b1 AND b2 THEN
        EXIT; --exit loop, if both points have been found inside linestring
      END IF;
    END LOOP;
    IF b1 AND b2 THEN -- both points have to been found in linestring
      d1:=ST_LineLocatePoint(geom, point1);
      d2:=ST_LineLocatePoint(geom, point2);
      IF d1 < d2 THEN
        RETURN ST_LineSubstring(geom, d1, d2);
      ELSE
        RETURN ST_LineSubstring(geom, d2, d1);
      END IF;
    ELSE
      RAISE EXCEPTION  'At least one POINT not part of LINESTRING!';
    END IF;
  ELSE
    RAISE EXCEPTION 'LINESTRING and Points must have same SRID!';
  END IF;
END;
$BODY$
LANGUAGE PLPGSQL;

This function checks all geometries (LineString and both Points) have same SRID and both Points are contained in LineString. If not, an exception is thrown.

If Point1 is nearer to end of LineString than Point2, both Points are swapped in usage.

The result is a LineString extracted of the given LineString with both points for start and end.

One advice:

ST_Line_Substring(geometry, double precision, double precision)

and

ST_Line_Locate_Point(geometry, geometry)

are marked as deprecated. Please use ST_LineSubstring() and ST_LineLocatePoint() instead.