If I have

Linestring(1 2, 1 5, 1 9)

and a

Point(1 3)

Is there any function that can merge linestring and point preserving the order so output would be:

Linestring(1 2, 1 3, 1 5, 1 9)
  • So you'll just be adding vertices to existing lines? – R.K. Nov 8 '12 at 14:22
  • yes, add vertice to existing linestring, resulting in a linestring. – BorisT Nov 8 '12 at 15:18
  • Why do you need to do it btw? – R.K. Nov 8 '12 at 15:22

If the LineString is simply to be subdivided at a position closest to the given Point, you could do what you want with this (splits LineString at closest Point to given Point and remerges the two segements afterwards)

             ST_Line_Substring(line, 0, ST_Line_Locate_Point(line, point)),
             ST_Line_Substring(line, ST_Line_Locate_Point(line, point), 1)
FROM  ST_GeomFromText('Linestring(1 2, 1 5, 1 9)') as line, 
      ST_GeomFromText('Point(1 3)') as point;

However, if your Point is not supposed to be projected on the LineString, this will not work.


PostGIS has ST_AddPoint that should allow you to do this though you'd have to specify where to add the point.

ST_AddPoint — Adds a point to a LineString before point (0-based index).


--guarantee all linestrings in a table are closed
        --by adding the start point of each linestring to the end of the line string
        --only for those that are not closed
        UPDATE sometable
        SET the_geom = ST_AddPoint(the_geom, ST_StartPoint(the_geom))
        FROM sometable
        WHERE ST_IsClosed(the_geom) = false;

        --Adding point to a 2-d line
        SELECT ST_AsEWKT(ST_AddPoint(ST_GeomFromEWKT('LINESTRING(1 2, 1 5, 1 9)'), ST_MakePoint(1, 3), 1));

        LINESTRING(1 2, 1 3, 1 5, 1 9)
  • yeah, i know for this function but i do not know where should i put my new point. – BorisT Nov 8 '12 at 13:55

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.