# Extract a LineString from LineString by two Points

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.

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

• Thank you. That helped me to find a solution. I wrote a userdefined function for the extract. I will post it after my interlock of 8 hours for a new user has passed by. The solution is bigger than the space for a comment. Oct 16 '13 at 12:18

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.

``````ST_Line_Substring(geometry, double precision, double precision)
``````ST_Line_Locate_Point(geometry, geometry)