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

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of bdbeames
Sent: Monday, August 13, 2007 12:05 PM
To: postgis-users at postgis.refractions.net
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
postgis-users at postgis.refractions.net
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070813/634217c9/attachment.html>