从MySQL搜索Google地图多边形区域

Question

we are a non profit charity organization. Planning to open up some support centers. We have a MySQL database of our support centers as following :

booth_id
booth_name
latitude
longitude
coverage
phone

Detail:

booth_id

Numeric unique ID for support booth.

booth_name

Alpha-numeric name for every support booth.

latitude

Google Map latitude for each booth.

longitude

Google Map longitude for each booth.

coverage

Json formated Google Map polygon for each booth. This is the support coverage area of each booth.

phone

Phone number of each support booth.

---

What we are trying to do is; getting user location and search if he is within any support booth coverage.

Here is the data stored on coverage field:

{
  "0": "23.86998,91.20611",
  "1": "23.86599,91.20609",
  "2": "23.86597,91.2091",
  "3": "23.86563,91.21251",
  "4": "23.87021,91.21249"
}

Which has this visual view

Depend on the coverage area here could be more Json indexed objects. Each Json objects has latitude and longitude comma separated.

Now my question is how to find from MySQL if latitude : 23.867467 and longitude : 91.2074338 (it can be any value) within coverage area or within which booth's coverage area. Is this possible to make this search with MySQL query with help of MySQL function?

Please let me know, that will be very helpful.

Thanks

Answer 1

You can use spatial functions provided by MySQL to store geometric data.

And then use http://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html#function_st-within