ST_AddPoint

adding a POINT somewhere in a LINESTRING

--> 'List of Sql-Commands'

---

Original Documentation 'Spatialite-Functions LINESTRING'

• 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 LINESTRING 441 to the start of LINESTRING 406
    • it is always a good idea to view the result before an Update is done!
      • if something is incorrect, the UPDATED Geometry could be destroyed !

SELECT name,SanitizeGeometry
(
 ST_AddPoint
 (
  -- Parm 1: the LINESTRING to use
  (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 406),
  -- Parm 2: the LINESTRING 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 after the last POINT, otherwise a 0 based Position
  0
 )
)
FROM berlin_ortsteil_segments WHERE id_segment = 406;

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

UPDATE berlin_ortsteil_segments
SET
 soldner_segment = SanitizeGeometry
 (
  ST_AddPoint
  (
    -- Parm 1: the LINESTRING to use
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 406),
   -- Parm 2: the LINESTRING 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 after the last POINT, otherwise a 0 based Position
   -1
  )
 )
WHERE id_segment = 406;

---

2014-04-01: Mark Johnson, Berlin Germany

---