[postgis-users] Nearest Neighbor question

[Stephen Woodbridge]

On 5/23/2014 9:58 AM, Gerry Creager - NOAA Affiliate wrote:
> I'll try this after putting out a few new fires! mylat and mylon are WKT?


SELECT poi.label, ST_Distance(geom, 'POINT(-74.0 42.0)'::geometry) AS dist
      FROM poi
      ORDER BY dist ASC LIMIT 4;

You might also need to deal with SRID if you have them defined for your 
poi table. Then you might need:

'SRID=4326;POINT(-74.0 42.0)'::geometry

or you can do something like:

st_setsrid(st_makepoint(-74.0, 42.0), 4326)

-Steve


> Thanks!
> gerry
>
>
> On Fri, May 23, 2014 at 8:54 AM, Stephen Woodbridge
> <woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.com>> wrote:
>
>     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>
>         <mailto: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> <tel: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>
>               <mailto:postgis-users at lists.__osgeo.org
>             <mailto:postgis-users at lists.osgeo.org>>
>
>             http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users
>             <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>
>         <mailto:postgis-users at lists.__osgeo.org
>         <mailto:postgis-users at lists.osgeo.org>>
>
>         http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
>
>
>         --
>         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)
>
>
>         _________________________________________________
>         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 <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
>     _________________________________________________
>     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
>     <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
>