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 <[EMAIL PROTECTED]> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
