[postgis-users] Spatial query for the Nearest location given a lat and long?

[Rick Zoolker]

There's a bunch of ways.  I'm still not sure if Milo's version will
work with a properly specified SRID, but I can't seem to be able to
make it work.  The quick and dirty method is to use the
distance_sphere() function in place of the distance() function in
milo's query.  It will give you the distance between two lat/long
points assuming the earth is a sphere.  Probably good enough for what
you're doing.  If you want something more accurate use the
distance_spheroid() function and specify the spheroid you want to use,
but my guess is that the distance_sphere() will be A-OK.
To be complete:
select id, name, distance_sphere(ST_POINT(table_1.lat,
table_1.long),ST_POINT(@lat, @long) ) as dist from table_1 order by
dist asc


On 8/13/07, bdbeames <bdbeames at cc.usu.edu> wrote:
>
> Ok I'm completely lost.
>
> I did more research and I ran across some information about the SRID, but
> none of this makes sense to me.  I've never looked at a spatial query
> before.
>
> Lets say that I lave the lat and long points 41.7833, -111.855.  I now have
> a table called table_1 with id, name, lat, long, extra.  This is a very
> large data base 1-2 T-Bites.  Weather stations from across the US that are
> updated every hour.  I want to find the nearest station to the given lat and
> long point.
>
> Could you be more specific of how to go about this.
>
> I also found mention of a Distance function, but no documentation of how it
> works.  Could I use this to find the top 1-5 nearest stations.  If so, could
> someone given me an example of how to write the query.
>
> This is a postgres database NOT MYSQL
>
> Thanks
> --
> View this message in context: http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12129033
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>