Error using ST_AddPoint for adding a new point to a polyline in PostGIS

Question

I have two PostGIS tables, one where I store points, named "nodes" and another with where I store multilinestrings, named "lines". I'm trying to append a new vertex to a line using one of the nodes executing a query similar to the following SQL sentence:

UPDATE lines SET 
the_geom = ST_AddPoint(the_geom, (select the_geom from nodes where id_node='XXXXX')  )
WHERE id_line='YYYY'

But I get:

ERROR: lwline_deserialize: attempt to deserialize a line which is really a Invalid type

Any suggestions?

Answer 1

You have version 1.5? That's the error you get if you attempt to use ST_AddPoint() with a MULTILINESTRING geometry.

For this type, you need to specify which part of the line needs to be appended to, then use a messy combination of ST_Dump()/ST_Collect() to modify that part.

For example, to append the point to the last LineString:

SELECT id_line, ST_AsEWKT(ST_Collect(geom))
FROM (
  SELECT id_line, CASE
    WHEN (dump).path[1] = ngeo THEN
      ST_AddPoint((dump).geom, (select the_geom from nodes where id_node='XXXXX'))
    ELSE
      (dump).geom
    END AS geom
  FROM (
    select id_line, ST_NumGeometries(geom) ngeo, ST_Dump(geom) as dump from lines
  ) AS foo
) AS foo
GROUP BY id_line;

where id_line is your primary key. You can modify this to an UPDATE query.