[postgis-users] Nearest Neighbor question

[Stephen Woodbridge]

SELECT poi.label, ST_Distance(MakePoint(lon, lat), MakePoint(mylon, 
mylat)) AS dist
      FROM poi
      ORDER BY dist ASC LIMIT 4;

-- or --

SELECT poi.label, ST_Distance(geom, MakePoint(mylon, mylat)) AS dist
      FROM poi
      ORDER BY dist ASC LIMIT 4;

where mylon, and mylat are you arbitray point locations.

-Steve

On 5/23/2014 9:46 AM, Gerry Creager - NOAA Affiliate wrote:
> The inherent problem is that the point will be arbitrarily specified. I
> need to find the 4 points in the table 'sites' (it has label, geom, and
> lat, lon columns) closest to an arbitrary point given a lat/lon
> coordinate pair.
>
> Or, am I making this harder than I think I am?
>
> Thanks!
> gerry
>
>
> On Fri, May 23, 2014 at 5:49 AM, Micha Silver <micha at arava.co.il
> <mailto:micha at arava.co.il>> wrote:
>
>     Use ORDER BY and LIMIT 4 to get the nearest 4 points:
>     Assuming your point table is called poi, and it has a 'geom' column,
>     and a 'label' column, and "lon' and 'lat' give the coordinates of
>     the starting location, then
>     SELECT poi.label, ST_Distance(poi.geom, MakePoint(lon, lat)) AS dist
>     FROM poi
>     ORDER BY dist ASC LIMIT 4;
>
>
>
>     On 22-May-14 11:47 PM, Gerry Creager - NOAA Affiliate wrote:
>>     I need to query a rather smallish database and return 4 points
>>     closest to a lat/lon (or lon/lat, if you prefer) statement. I've
>>     been off-line from PostGIS and my skills are worse than rusty
>>     right now...
>>
>>     Thanks
>>     Gerry
>>     --
>>     Gerry Creager
>>     NSSL/CIMMS
>>     405.325.6371 <tel:405.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)
>>
>>     This mail was received via Mail-SeCure System.
>>
>>
>>     _______________________________________________
>>     postgis-users mailing list
>>     postgis-users at lists.osgeo.org  <mailto:postgis-users at lists.osgeo.org>
>>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>     This mail was received via Mail-SeCure System.
>>
>>
>
>     --
>     Moshav Idan
>     D.N. Arava, 86840
>     cell: 0523-665918
>     http://www.surfaces.co.il
>
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> --
> Gerry Creager
> NSSL/CIMMS
> 405.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
>