[postgis-users] Distance between two furthest points of a group

[Nick Ves]

Didn't know about st_LongestLine.

Just tried and it amazed me!

on a dataset of 220k points (on a projected crs) it took ~ 1.5 secs to
answer the querry :

select 1 as id,  ST_LongestLine(st_collect(geom),st_collect(geom)) geom
from points ;

N


On Sat, Jun 27, 2015 at 2:41 PM, Nicklas Avén <nicklas.aven at jordogskog.no>
wrote:

> Hallo
>
> I haven't followed the whole conversation.
>
> But one way is to collect alk points and usr ST_maxdistance on the
> colnections. or st_longestline. longestline will return a line where the
> end points is the two points furthest from eath other. Those functions is
> quite fast.
>
> /Nicklas
>
>
>  Sent from my Cat® phone.
> Den 27 jun 2015 13:25 skrev Rémi Cura <remi.cura at gmail.com>:
>
> Maybe I'm wrong, but your 2 farthest points should be on the boundary of
> the maximum bounding circle (feels right but couldn't prove it).
> Thus you would compute this circle, then filter points not too far from
> it, then take the points with the max distance using an inner join (same as
> Nick, but you can save half the computation because dist(A,B)=dist(B,A), so
> simply add a condition a.id<b.id)).
>
> Another solution is to use bbox n nearest neighbour, which is indexed.
> You wouldn't car too much about using bbox, because for points it only
> reduce precision to float instead of double.
> This would be like :
>
> SELECT a.id,b.id, st_distance(a.geom,b.geom) AS d
> FROM my_points AS a , my_points AS b
> ORDER BY a.geom <-> b.geom DESC
> LIMIT 1
>
> This is the probably the better easiness/speed ratio.
>
> Cheers,
> Rémi-C
>
> 2015-06-26 22:06 GMT+02:00 Nick Ves <vesnikos at gmail.com>:
>
>> You can cross join to create the cartesian product of them and use it to
>> calculate the distance of each with regards to the other:
>>
>> select a.id,b.id, st_distance(a.geom,b.geom) d from points a cross join
>> points b order by d desc limit 1;
>>
>> ofc that will take forever because it will have to create an m x n table
>> (800 secs and counting...)
>>
>> As I see it the two points with the furthest distance between them should
>> touch the borders of you datasets convexhull. So you can filter out those
>> inside the boundaries and do the calculations with  the remaining points
>> along the borders :
>>
>> with f as
>> (
>> select a.geom,a.id from
>> points foo,
>> (select ST_ExteriorRing (st_convexhull(st_collect(geom))) geom from
>> points) bar
>> where st_Dwithin(foo.geom,bar.geom,0.00000001)
>> )
>> select a.id,b.id, st_distance(a.geom,b.geom) d from f  a cross join f b
>> order by d desc limit 1;
>>
>> ​should give you the id of your targets and the distance between them​
>>
>>
>>
>> On Fri, Jun 26, 2015 at 2:54 PM, Jonathan Moules <
>> J.Moules at hrwallingford.com> wrote:
>>
>>>  Hi List,
>>>
>>> I have sets of points (up to 250,000 in a set) and I want to get the
>>> furthest distance between any of them.
>>>
>>>
>>>
>>> In theory the simplest way is to use
>>> ST_MinimumBoundingCircle(ST_Collect(geography) and then get the diameter of
>>> that.
>>>
>>>
>>>
>>> The problem is – I don’t seem to be able to get the diameter of that
>>> circle (which would give me the distance I want).
>>>
>>>
>>>
>>> Does anyone have any thoughts on this? Is there a good way to get the
>>> diameter? Or some other way of getting the distance I desire.
>>>
>>>
>>>
>>> Thanks,
>>>
>>> Jonathan
>>>
>>> ------------------------------
>>>
>>> *HR Wallingford and its subsidiaries* uses faxes and emails for
>>> confidential and legally privileged business communications. They do not of
>>> themselves create legal commitments. Disclosure to parties other than
>>> addressees requires our specific consent. We are not liable for
>>> unauthorised disclosures nor reliance upon them.
>>> If you have received this message in error please advise us immediately
>>> and destroy all copies of it.
>>>
>>> HR Wallingford Limited
>>> Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
>>> Registered in England No. 02562099
>>>
>>> ------------------------------
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150630/2150be07/attachment.html>