<HTML><HEAD><TITLE>RE: [postgis-users] Spatial query for the Nearest location given alat and long?</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3132" name=GENERATOR></HEAD>
<BODY>
<P><FONT size=2>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.</FONT></P>
<P><FONT size=2>So first - you want to convert your lat long to a geometry like described here - </FONT></P>
<P><FONT size=2><A href="http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut03">http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut03</A></FONT></P>
<P><FONT size=2></FONT> </P>
<P><FONT size=2>For your particular dataset SRID - 2163 (US National Atlas Equal Area meters) might be good enough for your needs</FONT></P>
<P><FONT size=2> (4326 I think is more common than the 4269 mentioned in the article - I forget the difference)</FONT></P>
<P><FONT size=2></FONT> </P>
<P><FONT size=2>Next if you are keeping your data in long lat (4326) then you can do the following</FONT></P>
<P><FONT size=2>For example if this is in PHP it would be something like</FONT></P>
<P><FONT size=2>//set $lon and $lat variables from posted input here</FONT></P>
<P><FONT size=2>if(is_numeric($lon) && is_numeric($lat)){</FONT></P>
<P>$sql = "SELECT ws.*</P>
<P>FROM weatherstations ws</P>
<P>ORDER BY distance_sphere(ws.the_geom, setsrid(makepoint($lon, $lat),4326)) LIMIT 5";</P>
<P>//execute statement goes here </P>
<P>}</P>
<P><FONT size=2>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</FONT></P>
<P><FONT size=2></FONT> </P>
<P>$sql = "SELECT ws.*</P>
<P>FROM weatherstations ws</P>
<P>WHERE expand(transform(setsrid(makepoint($lon, $lat),4326), 2163), 10000) && ws.the_geom_m</P>
<P>ORDER BY distance(ws.the_geom_m, transform(setsrid(makepoint($lon, $lat),4326), 2163)) LIMIT 5";</P>
<P>This technique is described here</P>
<P><A href="http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor">http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor</A></P>
<P> </P>
<P>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</P>
<P><A href="http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic">http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic</A></P>
<P> </P>
<P>Hope that helps,</P>
<P>Regina</P>
<P> </P>
<P> </P>
<P><FONT size=2><BR><BR>-----Original Message-----<BR>From: postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of bdbeames<BR>Sent: Monday, August 13, 2007 12:05 PM<BR>To: postgis-users@postgis.refractions.net<BR>Subject: Re: [postgis-users] Spatial query for the Nearest location given alat and long?<BR><BR><BR>Ok I'm completely lost.<BR><BR>I did more research and I ran across some information about the SRID, but<BR>none of this makes sense to me. I've never looked at a spatial query<BR>before.<BR><BR>Lets say that I lave the lat and long points 41.7833, -111.855. I now have<BR>a table called table_1 with id, name, lat, long, extra. This is a very<BR>large data base 1-2 T-Bites. Weather stations from across the US that are<BR>updated every hour. I want to find the nearest station to the given lat and<BR>long point.<BR><BR>Could you be more specific of how to go about this. <BR><BR>I also found mention of a Distance function, but no documentation of how it<BR>works. Could I use this to find the top 1-5 nearest stations. If so, could<BR>someone given me an example of how to write the query.<BR><BR>This is a postgres database NOT MYSQL<BR><BR>Thanks<BR>--<BR>View this message in context: <A href="http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12129033">http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12129033</A><BR>Sent from the PostGIS - User mailing list archive at Nabble.com.<BR><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></P></FONT></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>