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

Paul Ramsey pramsey at cleverelephant.ca
Wed Aug 14 09:36:25 PDT 2019

```It’s pretty easy to synthesize a case where the extrema aren’t (both) on the bounding box.
I like the idea of using the convex hull to reduce the number of input points to a cross join.
As pointed out on IRC, we have the code already in place in ST_MinimumBoundingRadius we just aren’t returning all the information necessary to derive the extrema.
P

> On Aug 14, 2019, at 9:29 AM, Regina Obe <lr at pcorp.us> wrote:
>
> Why wouldn't it get an index assistance? ST_DWithin I would think would be separated apart
>
> So if you fill in something like ST_DWithin(a,b, a_distance_you_know_is_legit_for_distance_but biggish)
>
> Anyrate as was mentioned on IRC, I think the furthest points since you are dealing with just points should lie on the extent
>
> So would be just
>
> SELECT ST_LongestLine(ST_Extent(geom))
> FROM your_table;
>
> I would think would give you the answer if you are just dealing with points and don't care which points are furthest.
>
>
> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paul Ramsey
> Sent: Wednesday, August 14, 2019 12:00 PM
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] [Longest distance between two from list of points?]
>
> I dunno, 10000 * 10000 = a really big number  and the join condition on NOT doesn t get an index assist  P
>
>> On Aug 14, 2019, at 8:58 AM, Regina Obe <lr at pcorp.us> wrote:
>>
>> 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
>>
>> _______________________________________________
>> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

```