How to add vertices to existing linestrings?

Question

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)

chriserik · Accepted Answer

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)

SELECT ST_AsText(
         ST_LineMerge(
           ST_Union(
             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.

R.K. · Answer

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).

Examples:

--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));

--result
        st_asewkt
        ----------
        LINESTRING(1 2, 1 3, 1 5, 1 9)

dr_jts · Answer

Do this using ST_Snap:
SELECT ST_AsText( ST_Snap( 
  'Linestring(1 2, 1 5, 1 9)',
  'Point(1 3)',
  0.1
));

st_astext          
-----------------------------
 LINESTRING(1 2,1 3,1 5,1 9)

How to add vertices to existing linestrings?

3 Answers

Add your own answers!

Ask a Question