[postgis-users] How do I get 'n' points nearest another point?

[Brent Wood]

Hi Gerry,

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).

select r.id fromradar r,city cwhere c.id=<city point id>
order by ST_Distance(c.point, r.point)limit 5;
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. 


Cheers
  Brent Wood

      From: Gerry Creager - NOAA Affiliate <gerry.creager at noaa.gov>
 To: PostGIS Users Discussion <postgis-users at lists.osgeo.org> 
 Sent: Tuesday, June 23, 2015 7:30 AM
 Subject: [postgis-users] How do I get 'n' points nearest another point?
   
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. 
I know I've done something similar, but that was 2 or 3 major releases ago, and the syntax is now foreign to me!
Thanks in advance,
gerry-- 
Gerry CreagerNSSL/CIMMS405.325.6371++++++++++++++++++++++“Big whorls have little whorls,That feed on their velocity; And little whorls have lesser whorls, And so on to viscosity.” Lewis Fry Richardson (1881-1953)
_______________________________________________
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/20150622/cabca441/attachment.html>