I think like someone mentioned you will want to transform your data to another projection besides lat long to take advantage of indexes or if your data set is small enough, you can just use distance_sphere. What was left out is you need a limit clause otherwise you are returning the whole table.
So first - you want to convert your lat long to a geometry like described here - http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut03 For your particular dataset SRID - 2163 (US National Atlas Equal Area meters) might be good enough for your needs (4326 I think is more common than the 4269 mentioned in the article - I forget the difference) Next if you are keeping your data in long lat (4326) then you can do the following For example if this is in PHP it would be something like //set $lon and $lat variables from posted input here if(is_numeric($lon) && is_numeric($lat)){ $sql = "SELECT ws.* FROM weatherstations ws ORDER BY distance_sphere(ws.the_geom, setsrid(makepoint($lon, $lat),4326)) LIMIT 5"; //execute statement goes here } The above would be really slow if you have a large data set which it sounds like you do. Lets say you created a geometry field in meters SRID 2163 and index it as described in above article, then you can use the expand function to utilize indexes. Here I assume the field is called the_geom_m and the distance between the a close station is no further than 10000 meters away $sql = "SELECT ws.* FROM weatherstations ws WHERE expand(transform(setsrid(makepoint($lon, $lat),4326), 2163), 10000) && ws.the_geom_m ORDER BY distance(ws.the_geom_m, transform(setsrid(makepoint($lon, $lat),4326), 2163)) LIMIT 5"; This technique is described here http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor And a more advanced technique using expanding expanding boxes when you want to do multiple simultaneous nn searches or want to set your expand box really really high with minimum penalty is here http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic Hope that helps, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of bdbeames Sent: Monday, August 13, 2007 12:05 PM To: [email protected] Subject: Re: [postgis-users] Spatial query for the Nearest location given alat and long? 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 ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
