Remove specific points from linestring postgis

Question

I am working with a data set that has been using the (0 0) point to pad any points that were missing when the linestring was created. So now the query

SELECT 
    id, route as line 
FROM 
    trips
WHERE 
    ST_Contains(route, ST_PointFromText('POINT(0 0)', 4326))

Returns some linestrings where a point or two has been set 0 0 and some where the entire linestring is 0 0's.

Is there a way get the index of ST_PointFromText('POINT(0 0)', 4326) from a linestring so I can delete it?

EDIT

Removing the lines that are all 0 0 points is trivial now that I think about it.

DELETE

FROM 
    trips
WHERE 
    ST_Contains(route, ST_PointFromText('POINT(0 0)', 4326))
AND 
    ST_LENGTH(route) = 0

I am still trying to remove the points in lines.

Answer 1

One way to do this would be to use ST_DumpPoints to get individual points and then ST_MakeLine to rebuild the LineString, with a NOT ST_EQUALS in the WHERE clause to eliminate the Point(0,0);

WITH 
   lines (ls) AS 
      (VALUES  (ST_GeomFromText('LINESTRING(-1 -1, 0 0, 1 1, 2 2)'))), 
   dumped (pts) AS 
      (SELECT (ST_DumpPoints(ls)).* FROM lines)
   SELECT ST_MakeLine(dumped.geom) 
   FROM dumped 
   WHERE NOT ST_EQUALS(geom, ST_MakePoint(0,0));

If you wrap the ST_MakeLine in ST_AsText, you will see that this returns:

LINESTRING(-1 -1,1 1,2 2)

Though you didn't ask this, there are probably better ways of dealing with incomplete LineStrings than adding a Point(0, 0), but I'm sure you know this anyway.

Answer 2

Here is another way to remove points from lines using ST_RemovePoint.

Assuming table=my_lines and geometry field=geom.

UPDATE my_lines
SET geom = ST_RemovePoint(geom, t.pt_num-1)
FROM 
(SELECT line_id, pt_num FROM
  (SELECT id line_id, (ST_DumpPoints(geom)).path[1] pt_num, (ST_DumpPoints(geom)).geom geom
    FROM my_lines) my_points
  WHERE ST_Equals(geom, ST_GeomFromText('SRID=4326;POINT(0 0)'))) t
WHERE id = t.line_id