'Re: [postgis-users] Nearest Neighbor question'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Nearest Neighbor question
From:       Stephen Woodbridge <woodbri () swoodbridge ! com>
Date:       2014-05-23 14:18:51
Message-ID: 537F58CB.9000408 () swoodbridge ! com
[Download RAW message or body]

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@swoodbridge.com <mailto:woodbri@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@arava.co.il
> <mailto:micha@arava.co.il>
> <mailto:micha@arava.co.il <mailto:micha@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@lists.osgeo.org
> <mailto:postgis-users@lists.osgeo.org>
> <mailto:postgis-users@lists.__osgeo.org
> <mailto:postgis-users@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@lists.osgeo.org
> <mailto:postgis-users@lists.osgeo.org>
> <mailto:postgis-users@lists.__osgeo.org
> <mailto:postgis-users@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@lists.osgeo.org <mailto:postgis-users@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@lists.osgeo.org <mailto:postgis-users@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@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 