PostGIS Distance between vertex

Question

I want to filter lines which represent ship movement in a manner, that linestrings where the distance between two vertexes is greater than 1000 km are removed. I have a piece of code, however I am getting the error message that the function ST_Distance does not exist.

> ERROR:  function st_distance(geometry_dump, geometry_dump) does not exist
LINE 1: SELECT ST_Distance(ST_DumpPoints(a.line), ST_DumpPoints(b.li...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

Here is my code:

SELECT ST_Distance(ST_DumpPoints(a.line), ST_DumpPoints(b.line)) dist
FROM ais_2017_09_move a, ais_2017_09_move b 
WHERE mmsi = 566035000
AND dist < 1000;

Answer 1

Its not that ST_Distance doesn't exist, its that PostGIS can't find the correct ST_Distance function for the arguments you gave it. Check the error message:

HINT:  No function matches the given name and argument types.

So what arguments are you giving it? Two outputs from ST_DumpPoints.

The documentation https://postgis.net/docs/ST_DumpPoints.html says that it returns geometry_dump rows, and it seems ST_Distance doesn't know how to compute distances across those.

To do what I think you are trying to do it looks like you'll need to dump the points and then compute the distances grouped by the first element of the path part of the returned dump. See the doc for an example. I don't have handy access to a PostGIS server at the moment to try.

Also, as written, even you are calling ST_Distance on the same two things (with a different alias) and ST_Distance works row-by-row meaning you'll end up with a column of zeroes. To compute a distance matrix you have to join two geometry columns together. See Distance Matrix PostGis for example.