jugoslaviaa jugoslaviaa - 4 months ago 16
SQL Question

Finding the pair of points whose distance from each other is maximal

I have a very small database which includes 6 points, with those columns id, the_geom, descr. And my aim to write a PL/pgSQL function which finds the the pair of points whose distance from each other is maximal. As an output, I would like to show the id or descr of two points and also the distance between them.

I have tried to do a function with returns table but setof text would be better solution?

Answer

You may try something like a cross join to find all combinations, then order by the difference. If your table name was foo something similar to:

SELECT set1.id, set2.id, abs(set1.the_geom - set2.the_geom) --- May want to use earth_distance extension ehre
FROM foo set1, foo set2
WHERE set1.id != set2.id 
ORDER BY 3 DESC;

And if you need earth distance to calculate the distance itself - http://www.postgresql.org/docs/9.3/static/earthdistance.html