jeudi 30 juin 2016

Mysql - The Best Way to Manage and Find Point in Latitude Longitude Inside a Polygon


What is the best way to manage latitude and longitude database in MySQL? I have very big database contain many location of restaurant, and I need to find what are restaurants inside the polygon (the polygon especially rectangle)?

For example I have database:

Row 1: Latitude (-6.8374651) Longitude (107.56283)

Row 2: Latitude (-6.947151) Longitude (108.261528)

Row 3: Latitude (-9.125182) Longitude (115.121831)

etc

I have rectangle with North West Point: (-6.4516,107.19281) and South East Point: (-6.81726,106.19271)

My question is how can I find the list of restaurants inside this rectangle in the most efficient way? I have arround 50 million rows of data and will always increase everyday

PS. I have created an index for latitude and longitude and try to query it like this:

SELECT * FROM location WHERE latitude<=-6.4516 AND latitude>=-6.81726 AND longitude<=107.19281 AND longitude>=106.19271

But the MySQL keep searching it to entire table and makes the query become so slow

Thanks


Aucun commentaire:

Enregistrer un commentaire