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 <vesni...@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.a...@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.c...@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 <vesni...@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.mou...@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@lists.osgeo.org >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>> >>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@lists.osgeo.org >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >> _______________________________________________ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users