3

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.

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

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.

  • 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 '15 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? – John Powell Jun 15 '15 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 '15 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. – John Powell Jun 16 '15 at 6:22
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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