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

[Paul Ramsey]

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