4

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?

RedBlueThing
  • 42,006
  • 17
  • 96
  • 122
Asif
  • 833
  • 2
  • 14
  • 23
  • Shouldn't Amsterdam (or any other location) be represented by an irregular polygon representing the city boundaries? – Paul Tomblin Mar 09 '09 at 12:32
  • God this reminds me of my physics days: assume amsterdam is a sphere... – annakata Mar 09 '09 at 12:36
  • Isn't that duplicate of your own question: http://stackoverflow.com/questions/610496? – vartec Mar 09 '09 at 13:01
  • Yah Vertec, it is little bit simillar. in that situation we have range/radius from both side. But now in this case we have just one. – Asif Mar 09 '09 at 13:42

4 Answers4

2

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).

Assaf Lavie
  • 73,079
  • 34
  • 148
  • 203
  • This solution may not work since either i have to change database schema or entire database. Is there another way, for e.g. i can get bounding box in which Amsterdam resides and the object's bounding box whose service range overlaps Amstedam's bounding box – Asif Mar 09 '09 at 12:58
  • I don't see how it could work if you don't have the polygon for Amsterdam. – Assaf Lavie Mar 09 '09 at 13:32
  • I'm not sure you can, you'll have to check the documentation. But I'm pretty sure that if you're talking about countries/major cities you can find this information in the web somewhere. – Assaf Lavie Mar 09 '09 at 14:11
1

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.

Can Berk Güder
  • 109,922
  • 25
  • 130
  • 137
  • How i can compute polygon data based on point which i have? also to use MySQL spatial extension, is there any need to change database structure? – Asif Mar 09 '09 at 13:05
  • You use the Polygon functions, or if it's a circle, you can simply use Distance. Yes, you need to use spatial fields and indexes. – Can Berk Güder Mar 09 '09 at 13:47
  • Yes i have to use polygon function, But for polygon function to be used i need set of coordinates that defines my country or city are. Form where we can get that? – Asif Mar 10 '09 at 08:29
  • Google Maps, maybe? This is what Paul Tomblin meant in his comment. – Can Berk Güder Mar 10 '09 at 11:51
1

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

Community
  • 1
  • 1
cgreeno
  • 31,943
  • 7
  • 66
  • 87
  • Thanx for help. But this only will give you distance between two points. Also you are assuming that you are going to find out the points which are in 10 miles range. – Asif Mar 09 '09 at 13:37
  • In my case i want to find out points which are in specific country/city (irrespective of range or radius) also the points which are not in this country or city but in nearby region having service that might overlap the country/city boundry – Asif Mar 09 '09 at 13:39
1

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

Joe Liversedge
  • 4,124
  • 26
  • 19