[postgis-users] Nearest Neighbor question

[Gerry Creager - NOAA Affiliate]

I'll try this after putting out a few new fires! mylat and mylon are WKT?

Thanks!
gerry


On Fri, May 23, 2014 at 8:54 AM, Stephen Woodbridge <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>> 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
>>
>>
> _______________________________________________
> postgis-users mailing list
> 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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140523/43b5f0d5/attachment.html>