8

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?

mofoyoda
  • 893
  • 7
  • 21

2 Answers2

12

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
JonasPedersen
  • 880
  • 1
  • 8
  • 19
David
  • 343
  • 2
  • 5
0

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.

Djedouas
  • 131
  • 3
  • I don't think this is correct. The original answer did have the endpoints swapped, but the fix is to interchange the ST_Translate expressions. – dr_jts Aug 06 '22 at 18:59
  • Interchange the ST_Translate expressions works as well. In my proposed version I removed the ST_Distance from the len and switched az1 and az2, that's why it works, or have I missed something? – Djedouas Aug 08 '22 at 07:40
  • 1
    Agreed, your version is equivalent, and simpler in some ways. – dr_jts Aug 09 '22 at 02:38