There is a table in MySQL with a variety of GPS coordinates in the form of latitude and longitude .

The task is to split the entire world map into many parts (grid with a certain step) and then be able to determine which cell (coordinates of its corners) any GPS point belongs to. For example:

$activeCellCoordinates = cellForCoordinates({10.055402736564236, 38.1005859375});

Then it will be necessary to record the coordinates of the corners of each such cell into the database (if any of the GPS points fall into it, or are near the edges), but this is no longer the case.

img

Actually, how is it more or less optimally implemented in php ? As we know, the boundaries of latitude are -90 and 90, and longitude : 180 and -180. And take, for example, a step of 0.01 - this is already 648,000,000 cells: (

Thanks in advance!

    1 answer 1

    What's the difference how many cells? Consider a simple case: the X axis, the maximum value of 180, is divided into 100 segments. It is necessary to find the coordinates of the segment where the point with x=10.0554 . The x contains ceil(x/(180/100)) whole segments, the left coordinate of the last segment will be equal to floor(x/(180/100))*(180/100) , the right ceil(x/(180/100))*(180/100)

    For the Y axis, respectively, we consider the coefficient 90/100 .

    Thus, for your point along both axes we get: x1 = 9.000, x2 = 10.8000, y1 = 37.8000, y2 = 38.7000 (rounded to 4 characters).

    Maybe I made it somewhere with tsiferkami, but the principle is this, + we also add processing of negative coordinates, + we optimize arithmetic.

    PS Yes, and it makes no sense to enter cell coordinates into the database in advance. Cells that contain objects will be orders of magnitude smaller. Got a point, calculated the coordinates of the cell. There is such in the database - they created a “point-cell” connection, no - they brought a cell and created a connection. Naturally, each cell should be in the database in one copy, and not for each point its own copy.

    • Yes, my calculation of the cells in advance, it was a completely stupid approach. Calculations should be done upon appeal. It remains now only to apply your formula correctly. And for the fact that everyone considered me a special thank you. I'll go figure it out. Regarding the entry in the database, yes, everything is cool, I have a problem with the coordinates. - levbruk
    • one
      By the way, it was thought: to search for a square in the database, it is not necessary to compare all 4 coordinates. When creating a record, it is possible to calculate a certain function of them, which would be unique for each square. For example, the same md5 from the coordinates. Or some other, if only the accuracy of calculations is not korezhil. Then the search can be faster. - user6550
    • one
      Or compare 2 coordinates instead of 4 - the center of the square ... Eck broke through me :) - user6550