How I can insert point to LINESTRING in PostGIS? For example, I have line like LINESTRING(0 0, 10 0) and point which lies on line segment: POINT(5, 0). I need to add this point to line. Expected result is LINESTRING(0 0, 5 0, 10 0). It looks like ST_AddPoint(geometry linestring, geometry point, integer position); can helps me but I don't know how I can determine position value in case if line will not so simple.

  • SELECT ST_AddPoint('LINESTRING(0 0, 10 0)'::geometry,'POINT(5 0)'::geometry,1) will work. It's not clear what you mean by "when the line is not so simple," or how your solution below addresses the problem. – amball Nov 9 '17 at 4:46

I've found such solution:

WITH geom AS
  ( SELECT 'LINESTRING(0 0, 10 0)'::geometry l,
           'POINT(5 0)'::geometry p),
     series AS
  ( SELECT generate_series(1, ST_NPoints(l)-1) n
   FROM geom)
SELECT ST_AsText(ST_AddPoint(geom.l, geom.p, series.n))
FROM geom,
WHERE ST_Contains(ST_MakeLine(ST_PointN(l, n), ST_PointN(l, n+1)), geom.p);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.