2

I do not understand the error I get when I try to execute the following query:

  SELECT ST_3DDistance(location)
   FROM public.cycle_station;

  ERROR:  function st_3ddistance(geometry) does not exist
  LINE 1: select st_3DDistance(location)
           ^
  HINT:  No function matches the given name and argument types. You 
  might need to add explicit type casts.
  SQL state: 42883.

Is my query wrong? I am trying to find the largest distance between my geometries (which are 3D)

  • What happened to your previous question about the orientation of the PolyhedralSurfaceZ? I was attempting to answer it, though I haven't found a solution yet. Do you have a solution? – John Powell Apr 21 '18 at 15:48
  • @JohnPowellakaBarça - I have found the solution - I can post a pic of how it should be done if you like – hazard335 Apr 21 '18 at 18:31
  • That would be really helpful. Maybe I should ask the question and have you answer it? I'm away from my laptop, so can't do it for a few hours. – John Powell Apr 22 '18 at 6:48
3

If you look at the function signature from the ST_3DDistance docs, you will see that it requires two parameters, a from and a to, eg,

SELECT ST_3DDistance(
            ST_Force3D(ST_MakePoint(0,0,0)), 
            ST_Force3D(ST_Makepoint(1,1,1)));

which returns:

1.732,

which is the cubed root of 3, as you would expect from opposite edges of a 1-sided cube. Note, the ST_Force3D is not technically required, though I find some functions behave badly without it, so I usually include it to be certain that the z coordinate is being recognized.

Note, that ST_Distance returns the two-dimensional distance, even if 3D points are passed in, eg,

SELECT ST_Distance(
          ST_Force3D(ST_MakePoint(0,0,0)),
          ST_Force3D(ST_Makepoint(1,1,1)));

now returns 1.414, which is of course, the square root of 2.

If you are trying to get the maximum distance between all of your coordinates, you need to do a self join (which is also a cross join, ie, a Cartesian product) and then select the max. Something like:

SELECT max(ST_3DDistance(a.geom, b.geom)) 
 FROM locations a, locations b
 WHERE a.id > b.id;

The where clause (where id is whatever your primary key is called) means that you only compare the geomeries once, so it converts the run time from O (n^2) to O (n^2/2), so still polynomial, but half the time.

EDIT:
To find the actual points, corresponding to the max distance, you will need to calculate all the distances and include the id from both sides of the join and then select the maximum by ordering on distance and using LIMIT 1.

WITH distances(a_id, b_id, distance) AS (
  SELECT a.id, b.id, ST_3DMaxDistance(a.geom, b.geom)) 
    FROM locations a, locations b
    WHERE a.id > b.id
)
SELECT a_id, b_id, distance 
  FROM distances 
 ORDER BY distance DESC 
 LIMIT 1; 

And as Nicklas Aven has pointed out, using ST_3DMaxDistance might give more accurate results.

  • What about if the geometries are in the same column/table? – hazard335 Apr 21 '18 at 18:38
  • Below is the query I got from what you posted - but What i want to know is which two stations have the largest distance between them? SELECT max(ST_3DDistance(a.location, b.location)) FROM cycle_station a, cycle_station b WHERE a.station_id > b.station_id; – hazard335 Apr 21 '18 at 18:44
  • 1
    For the max distance case you can also use st_3dmaxdistance postgis.net/docs/ST_3DMaxDistance.html – Nicklas Avén Apr 21 '18 at 19:12
  • The geometries are in the same column and table. That is what the cross join on the same table, but with two different aliases does. If you have two different tables, then you would use table1 a, table2 b and remove the a.id > b.id, because across tables you wouldn't be worried about doing the same calculation twice for each pair of points, as you do with a self join. – John Powell Apr 21 '18 at 19:25

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.