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

Regina Obe lr at pcorp.us
Wed Aug 14 09:29:49 PDT 2019


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



More information about the postgis-users mailing list