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

[Rémi Cura]

Maybe you can even reduce this with cte :

with collected_geom AS (
select st_collect(geom) as geoms
from your_points
)
SELECT ST_LonguestLine(t1.geoms,t2.geoms)
FROM collected_geom AS t1, collected_geom AS t2

Cheers,
Rémi-C

2015-06-30 8:48 GMT+02:00 Nick Ves <vesnikos at gmail.com>:

> 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
>>
>
>
> _______________________________________________
> 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/a22a40ec/attachment.html>