<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Maybe you can even reduce this with cte :<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br>with collected_geom AS (<br></div><div class="gmail_default" style="font-family:monospace,monospace">select st_collect(geom) as geoms<br></div><div class="gmail_default" style="font-family:monospace,monospace">from your_points<br></div><div class="gmail_default" style="font-family:monospace,monospace">)<br></div><div class="gmail_default" style="font-family:monospace,monospace">SELECT ST_LonguestLine(t1.geoms,t2.geoms)<br></div><div class="gmail_default" style="font-family:monospace,monospace">FROM collected_geom AS t1, collected_geom AS t2<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-06-30 8:48 GMT+02:00 Nick Ves <span dir="ltr"><<a href="mailto:vesnikos@gmail.com" target="_blank">vesnikos@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif">Didn't know about st_LongestLine.</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Just tried and it amazed me!</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">on a dataset of 220k points (on a projected crs) it took ~ 1.5 secs to answer the querry :</div><div class="gmail_default"><font face="tahoma, sans-serif"><br></font></div><div class="gmail_default"><font face="tahoma, sans-serif">select 1 as id, ST_LongestLine(st_collect(geom),st_collect(geom)) geom from points ;</font><br></div><div class="gmail_default"><font face="tahoma, sans-serif"><br></font></div><div class="gmail_default"><font face="tahoma, sans-serif">N</font></div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Sat, Jun 27, 2015 at 2:41 PM, Nicklas Avén <span dir="ltr"><<a href="mailto:nicklas.aven@jordogskog.no" target="_blank">nicklas.aven@jordogskog.no</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p dir="ltr">Hallo</p>
<p dir="ltr">I haven't followed the whole conversation. </p>
<p dir="ltr">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.</p>
<p dir="ltr">/Nicklas<br><br><br></p>
<p dir="ltr"> Sent from my Cat® phone.</p><div><div>
<div>Den 27 jun 2015 13:25 skrev Rémi Cura <<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>>:<br type="attribution"><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">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).<br></div><div class="gmail_default" style="font-family:monospace,monospace">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 href="http://a.id" target="_blank">a.id</a><<a href="http://b.id" target="_blank">b.id</a>)).<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Another solution is to use bbox n nearest neighbour, which is indexed.<br></div><div class="gmail_default" style="font-family:monospace,monospace">You wouldn't car too much about using bbox, because for points it only reduce precision to float instead of double.<br></div><div class="gmail_default" style="font-family:monospace,monospace">This would be like :<br><br><font face="tahoma, sans-serif">SELECT <a href="http://a.id" target="_blank">a.id</a>,<a href="http://b.id" target="_blank">b.id</a>, st_distance(a.geom,b.geom) AS d <br>FROM my_points AS a </font>, my_points AS b<code><span><br>ORDER</span><span> </span><span>BY</span><span> a.geom </span><span><-></span><span> </span></code>b.geom DESC<br></div><div class="gmail_default" style="font-family:monospace,monospace">LIMIT 1<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">This is the probably the better easiness/speed ratio.<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-06-26 22:06 GMT+02:00 Nick Ves <span dir="ltr"><<a href="mailto:vesnikos@gmail.com" target="_blank">vesnikos@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default"><span style="font-family:tahoma,sans-serif">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: </span></div><div class="gmail_default"><span style="font-family:tahoma,sans-serif"><br></span></div><div class="gmail_default"><font face="tahoma, sans-serif">select <a href="http://a.id" target="_blank">a.id</a>,<a href="http://b.id" target="_blank">b.id</a>, st_distance(a.geom,b.geom) d from points a cross join points b order by d desc limit 1;</font><br></div><div class="gmail_default"><font face="tahoma, sans-serif"><br></font></div><div class="gmail_default"><font face="tahoma, sans-serif">ofc that will take forever because it will have to create an m x n table (800 secs and counting...)</font></div><div class="gmail_default"><font face="tahoma, sans-serif"><br></font></div><div class="gmail_default"><font face="tahoma, sans-serif">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 :</font></div><div class="gmail_default"><font face="tahoma, sans-serif"><br></font></div><div class="gmail_default"><font face="tahoma, sans-serif"><div class="gmail_default">with f as </div><div class="gmail_default">( </div><div class="gmail_default">select a.geom,<a href="http://a.id" target="_blank">a.id</a> from </div><div class="gmail_default">points foo,</div><div class="gmail_default">(select ST_ExteriorRing (st_convexhull(st_collect(geom))) geom from points) bar </div><div class="gmail_default">where st_Dwithin(foo.geom,bar.geom,0.00000001)</div><div class="gmail_default">)</div><div class="gmail_default">select <a href="http://a.id" target="_blank">a.id</a>,<a href="http://b.id" target="_blank">b.id</a>, st_distance(a.geom,b.geom) d from f a cross join f b order by d desc limit 1;</div><div><br></div></font></div><div class="gmail_extra"><div class="gmail_default" style="font-family:tahoma,sans-serif">should give you the id of your targets and the distance between them</div><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On Fri, Jun 26, 2015 at 2:54 PM, Jonathan Moules <span dir="ltr"><<a href="mailto:J.Moules@hrwallingford.com" target="_blank">J.Moules@hrwallingford.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><div>
<div link="blue" vlink="purple" lang="EN-GB">
<div>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">Hi List,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">I have sets of points (up to 250,000 in a set) and I want to get the furthest distance between any of them.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">In theory the simplest way is to use ST_MinimumBoundingCircle(ST_Collect(geography) and then get the diameter of that.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">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).<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">Thanks,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:Arial,sans-serif;color:rgb(60,60,60)">Jonathan<u></u><u></u></span></p>
</div>
<br>
<hr>
<p style="font-family:arial;font-size:8pt"><b>HR Wallingford and its subsidiaries</b> 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.
<br>
If you have received this message in error please advise us immediately and destroy all copies of it.
<br>
<br>
HR Wallingford Limited<br>
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom<br>
Registered in England No. 02562099<br>
</p>
<p></p>
<hr>
<p></p>
</div>
<br></div></div><span>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></span></blockquote></div><br></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</blockquote></div></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>