ST_AddPoint

adding a point somewhere in a LINESTRING

Original Documentation [[spatialite 4.1.0 LINESTRING|http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html#p7]] ST_AddPoint( line LineString , point Point [ , position Integer ] ) : Linestring

Notes:

• position Integer

  • 0 : Add a POINT at the beginning of the LINESTRING (before the first POINT)
  • 1 : Add a POINT between the first and second POINT of the LINESTRING
  • ...
  • -1 : Add a POINT at the end of the LINESTRING (after the last POINT)

• Display the result [adding the POINT 43 of LINSTRING 441 to the start of LINESTRING 406]

  • always a good idea to view the result before an Update is done!

SELECT name,SanitizeGeometry
(
 ST_AddPoint
 (
  -- Parm 1: the line to set
  (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 406),
  -- Parm 2: the line to take the point from
  --- ST_PointN(base 1) - for the first POINT use '1'
  (SELECT ST_PointN(soldner_segment,43) FROM berlin_ortsteil_segments WHERE id_segment = 441),
  -- Parm 3: Position to set the Point (0 based)
  -- 0= add as first point ; -1 add as last point, otherwise a 0 based Position
  0
 )
)
FROM berlin_ortsteil_segments WHERE id_segment = 406;

• Update the Record [adding the first POINT of LINSTRING 441 to the end of LINESTRING 406]

UPDATE berlin_ortsteil_segments
SET
 soldner_segment = SanitizeGeometry
 (
  ST_AddPoint
  (
    -- Parm 1: the line to set
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 406),
   -- Parm 2: the line to take the point from
   --- ST_PointN(base 1) - for the first POINT use '1'
   (SELECT ST_PointN(soldner_segment,1) FROM berlin_ortsteil_segments WHERE id_segment = 441),
   -- Parm 3: Position to set the Point (0 based)
   -- 0= add as first point ; -1 add as last point, otherwise a 0 based Position
   -1
  )
 )
WHERE id_segment = 406;