[postgis-users] [Longest distance between two from list of points?]

[Regina Obe]

I would do a cross join

SELECT a.id As a_id, b.id aS b_id
-- pick a number you know is not the furthest this is just to save some processing, you can skip this and just do a CROSS JOIN but will lose some speed
FROM your_points AS a INNER JOIN your_points AS b ON NOT ST_DWithin(a,b,some_biggish_number)   
ORDER BY ST_Distance(a,b) DESC LIMIT 1



-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paul Ramsey
Sent: Wednesday, August 14, 2019 11:18 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] [Longest distance between two from list of points?]

Depends on how exact you need the answer. If you re looking for the extent of the data set, using

SELECT ST_Extent(geom) FROM mytable

will pop put a nice bounds and you can easily pull the width/height of that to get something like the size. 
The actual two points that are the furthest, that s harder for sure, I almost think you would need a recursive CTE for that, but with only 10K points, it might not perform too too badly. Basically pick a random point, and then find the furthest point from that one (ORDER BY ST_Distance() DESC LIMIT 1), then use that point as the seed for the next iteration, and repeat until you end up with the same pairing in subsequent iterations.

Maybe someone on the list will feel like writing out an example for the intellectual glory  :)

P.

> On Aug 14, 2019, at 7:15 AM, Sergiusz Pawlowicz <sergiusz at pawlowicz.name> wrote:
> 
> I have about ten thousands of points stored in a postgis database.
> 
> I need to find the longest distance between two of the furthest of 
> them from each other, something like bounding box, but let's say 
> "bounding line".
> 
> Any suggestions?
> 
> I browse the archives but there was no clearly opensource answer, the 
> only one was suggesting some FME-like commercial products.
> 
> Thanks,
> Serge
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users