Geo Proximity Search

Question

There is requirement in application which states that find out all the objects that are bounded to specific country and/or city into the google map.

We have objects with respective latitude and longitude precalucated using google map api and stored in database. Some times these objects are provides service withing certain service range/radius within which they can provide their services.

For e.g. now the situation is like i wanna to find out all the objects which resides in Amsterdam for instance.

The application should find out all the objects that are located in Amsterdam, also the objects which are not exactly located in Amsterdam but nearby, having service range up to Amsterdam.

So i have known latitude, longitude values for Amsterdam returned by google map, also the objects having respective lat,lng value and service range/radius stored in database. How can i make it possible?

Answer 1

You should probably represent countries/cities as polygons in the DB, and then use OpenGIS-style polygon intersection functions to intersect.

But, MySQL does not implement polygon intersection properly (only MBR - Minimum Bounding Rectangle). So this approach, while correct in theory, will simply not work for you with MySQL. You might want to consider moving to Postgres.

What you can do is use your DB's MBR intersection capabilities and supplement them with your own code that does polygon/point intersection (you can find libraries that do this).

Answer 2

You might want to take a look at MySQL's spatial extensions.

You'll need to use the Contains function. However, as Paul stated in his comment, regions should be represented as polygons. If they are not, then I believe your best bet is to create a polygon centered on the point you already have.

Answer 3

OK from What I understand you are basically trying to calculate the distance between 2 lat/long points. I would start by discounting the ones that where outside you sphere of (lets say) 10 miles. So from your central point you will want to get the the coordinates 10 miles, East, West, South and North. To do this you need to use the Great-circle distance formula.

From that point you have you Data if you wish to break this data up further then you need to order the points by distance from the central point. To do this you need to use the Haversine formula

I can see that you have a PHP tag but I have included some formulas and examples in both SQL(mainly) and C#.

Haversine Formula in C# and in SQL

Determine the distance between ZIP codes using C#

Great Circle SQL

Great Circle 2

Answer 4

You could also look at something like LocalSolr/LocalLucene if you wanted to leave the mechanics of proximity to an external service.