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

    id, route as line 
    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?


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


    ST_Contains(route, ST_PointFromText('POINT(0 0)', 4326))
    ST_LENGTH(route) = 0

I am still trying to remove the points in lines.

2 Answers 2


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

   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.

  • Another way could be to use ST_DumpPoints for finding the indices of (0 0) points and remove those from the linestrings with ST_RemovePoint postgis.net/docs/ST_RemovePoint.html.
    – user30184
    Jun 15, 2015 at 10:20
  • @user30184. True, and that did occur to me, though I think it is slighly more complex to write. I wonder if there is any significant performance difference between rebuilding a LineString and removing from an existing one? Jun 15, 2015 at 10:27
  • @JohnBarça Thanks for the answer! What would be some other options in terms of dealing with missing points in the line string? Right now I am considering omitting them entirely.
    – BBS
    Jun 15, 2015 at 22:53
  • I don't know without knowing where the linestrings come from and what they are representing -- GPS feed along a route, for example? Using Point(0,0) for missing data struck me as similar as using 0 rather than null for a numeric field. But without knowing more, that might be an unfair judgement. Jun 16, 2015 at 6:22

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.