How to extend a straight line in postgis?

Question

I have a postgis table of simple linestrings, each one is a straight line with just two points. I want to select every linestring, but extended from both sides by 1 meter. So i have lines, that are longer by 2 meters. How can this be done?

Answer 1

I seems very similar to post "postgis, extrapolate a line". If I avoid repetition of cited post, I think you just need to extrapolate beyond your extreme points. In a query you get something like this should work:

SELECT ST_MakeLine(ST_TRANSLATE(a, sin(az1) * len, cos(az1) * 
len),ST_TRANSLATE(b,sin(az2) * len, cos(az2) * len))

  FROM (
    SELECT a, b, ST_Azimuth(a,b) AS az1, ST_Azimuth(b, a) AS az2, ST_Distance(a,b) + 1 AS len
      FROM (
        SELECT ST_StartPoint(the_geom) AS a, ST_EndPoint(the_geom) AS b
          FROM ST_MakeLine(ST_MakePoint(1,2), ST_MakePoint(3,4)) AS the_geom
    ) AS sub
) AS sub2

Answer 2

Be careful: this method swaps points a and b in the end.

Use

SELECT ST_MakeLine(ST_TRANSLATE(a, sin(az2) * len, cos(az2) * 
len),ST_TRANSLATE(b,sin(az1) * len, cos(az1) * len))

  FROM (
    SELECT a, b, ST_Azimuth(a,b) AS az1, ST_Azimuth(b, a) AS az2, 1 AS len
      FROM (
        SELECT ST_StartPoint(the_geom) AS a, ST_EndPoint(the_geom) AS b
          FROM ST_MakeLine(ST_MakePoint(1,2), ST_MakePoint(3,4)) AS the_geom
    ) AS sub
) AS sub2

to have a and b not swapped.