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

[Paul Ramsey]

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