views:

111

answers:

1

Hi,

how can I search for geo-objects in a certain perimeter ? E.g. I have several objects with Lat/Lon coordinates stored in my DB. Now I want to retrieve all the objects which lie in a specific perimeter (10 miles or 20 miles) around a given point.

I guess what I would have to do is to form some query like:

SELECT * FROM objects o where o.lat < (myPositionLat+x) AND o.lon < (myPositionLon+y)

Is this right? How do I determine/set the values for x and y?

+1  A: 

You have to keep in mind that the distance between longitudes is not constant. Degrees of latitude are parallel and are approximately 111 kilometers (69 miles) apart, but a degree of longitude is widest at the equator at 111 kilometers and gradually converges to zero at the poles.

At 40° north or south the distance between a degree of longitude is about 85 kilometers (53 miles), while the distance between latitudes remains roughly the same at 111 kilometers (Source). If your geo-objects will be limited to a certain area only, and a very rough filter is acceptable, you can use this information to determine your x and y offsets.

However if your objects will be sparse around the globe, you should be calculating the great-circle distance instead. Luckily this is relatively easy with the haversine formula. You may want to read further and check out the implementation at Calculate distance, bearing and more between Latitude/Longitude points by Chris Veness.

If you will only have a handful of geo-objects, you can simply calculate the great-circle distance from your point to each geo-object. Then simply sort the result list by the distance, and filter by some threshold in miles or kilometers.

However, if you will be having many geo-objects, you should consider using a database with spatial indexing capabilities. MySQL, PostgreSQL and SQL Server 2008 all have geo-spatial features (either natively or via extensions), which include spatial indexing and implementations of the haversine formula.

Daniel Vassallo