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

Nicklas Avén nicklas.aven at jordogskog.no
Wed Aug 14 10:25:27 PDT 2019

```Hi

Sorry if I misunderstand. But don't we also have all this already in
ST_LongestLine.

It also uses convexhull to reduce points to calculate.

atb

Nicklas

On 8/14/19 6:36 PM, Paul Ramsey wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
```