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

Paul Ramsey pramsey at cleverelephant.ca
Wed Aug 14 08:18:09 PDT 2019

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… :)


> 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

More information about the postgis-users mailing list