<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div id="yui_3_16_0_1_1433819304202_2467030"><span id="yui_3_16_0_1_1433819304202_2467209">Hi Gerry,<br></span></div><div id="yui_3_16_0_1_1433819304202_2467296"><span id="yui_3_16_0_1_1433819304202_2467209"><br></span></div><div id="yui_3_16_0_1_1433819304202_2467275"><span id="yui_3_16_0_1_1433819304202_2467209">Not optimised but essentially get your city point, get the distances to each of the radar points, sort on distance, return the first n (5).<br></span></div><div id="yui_3_16_0_1_1433819304202_2467272"><span id="yui_3_16_0_1_1433819304202_2467209"><br></span></div><div id="yui_3_16_0_1_1433819304202_2467271"><span id="yui_3_16_0_1_1433819304202_2467209">select r.id from</span></div><div id="yui_3_16_0_1_1433819304202_2467171" dir="ltr"><span id="yui_3_16_0_1_1433819304202_2467226">radar r,</span></div><div id="yui_3_16_0_1_1433819304202_2467147" dir="ltr"><span id="yui_3_16_0_1_1433819304202_2467270">city c</span></div><div id="yui_3_16_0_1_1433819304202_2467181" dir="ltr"><span id="yui_3_16_0_1_1433819304202_2467208">where c.id=<city point id><br></span></div><div id="yui_3_16_0_1_1433819304202_2467121" dir="ltr"><span id="yui_3_16_0_1_1433819304202_2467180">order by ST_Distance(c.point, r.point)</span></div><div id="yui_3_16_0_1_1433819304202_2467223" dir="ltr"><span id="yui_3_16_0_1_1433819304202_2467180">limit 5;</span></div><div id="yui_3_16_0_1_1433819304202_2467359"><br></div><div>Any way you can reduce the number of radar points to test the distance for will improve performance - like if the radar table has a city id column you can restrict the search to just those by adding "and r.id = c.id" to the where clause. <br></div><div id="yui_3_16_0_1_1433819304202_2467360"><br></div><div><br></div><div>Cheers</div><div id="yui_3_16_0_1_1433819304202_2467361"><br></div><div dir="ltr"> Brent Wood<br></div><div id="yui_3_16_0_1_1433819304202_2467362"><br> </div><div id="yui_3_16_0_1_1433819304202_2467040" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1433819304202_2467039" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1433819304202_2467038" dir="ltr"> <hr id="yui_3_16_0_1_1433819304202_2467046" size="1"> <font id="yui_3_16_0_1_1433819304202_2467041" face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Gerry Creager - NOAA Affiliate <gerry.creager@noaa.gov><br> <b><span style="font-weight: bold;">To:</span></b> PostGIS Users Discussion <postgis-users@lists.osgeo.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Tuesday, June 23, 2015 7:30 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] How do I get 'n' points nearest another point?<br> </font> </div> <div id="yui_3_16_0_1_1433819304202_2467172" class="y_msg_container"><br><div id="yiv1861833773"><div id="yui_3_16_0_1_1433819304202_2467173" dir="ltr">I've two tables, one with a set of city centroids, and one with radar locations. I need to select the 4 (or maybe 5) closes radars to a given city point. <div id="yui_3_16_0_1_1433819304202_2467174"><br></div><div id="yui_3_16_0_1_1433819304202_2467175">I know I've done something similar, but that was 2 or 3 major releases ago, and the syntax is now foreign to me!</div><div id="yui_3_16_0_1_1433819304202_2467176"><br></div><div id="yui_3_16_0_1_1433819304202_2467177">Thanks in advance,<br clear="all"><div id="yui_3_16_0_1_1433819304202_2467178">gerry</div>-- <br><div class="yiv1861833773gmail_signature"><div dir="ltr">Gerry Creager<div>NSSL/CIMMS</div><div>405.325.6371</div><div>++++++++++++++++++++++</div><div><div>“Big whorls have little whorls,</div><div>That feed on their velocity; </div><div>And little whorls have lesser whorls, </div><div>And so on to viscosity.” </div><div>Lewis Fry Richardson (1881-1953)</div></div></div></div>
</div></div></div><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" 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" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br><br></div> </div> </div> </div></body></html>