php算法，从2D平方内收集所有坐标到数组

Question

I have a map with places on it. The coordinates for the places are saved in MySQL as a lat/lon string ("3456,789"). I'm trying to write a function that will give me an array of all coordinates in the shape of a square with my coordinates as the centre-point. I will then hit the database to see if any other places have coordinates in this array.

I can define the corner points of the square and can write a clunky function to fill the array with all the coordinates one by one with a for loop or something like that, but I'm wondering if there is a clever algorithm that can take care of this for me. Example below with expected output:

function getNearbyPlaces($distance = 4){
   $radius = $distance / 2;
   $coords = explode(",",$object->metadata["Coordinates"]["value"]); // array("3456","789")
   $topLeft = array($coords[0] - $radius,$coords[1] - $radius);
   $topRight = array($coords[0] + $radius,$coords[1] - $radius);
   $botLeft = array($coords[0] - $radius,$coords[1] + $radius);
   $topRight = array($coords[0] + $radius,$coords[1] + $radius);

   // calculate all coords within square here

   // Expected output
   // array("3454,787","3455,787","3456,787","3457,787","3458,787","3454,788","3455,788","3456,788","3457,788","3458,788","3454,789","3455,789","3456,789","3457,789","3458,789","3454,790","3455,790","3456,790","3457,790","3458,790","3454,791","3455,791","3456,791","3457,791","3458,791");
}

Does anyone know of a simple way of doing this?

To answer some potential questions early, the coordinates must be stored as lat/lon strings in the database, there is currently no way around this. This has no connection with Google maps.

Many thanks in advance

EDIT Added base SQL query for convenience:

"SELECT * FROM ".$database_table_prefix."user_objects_metadata WHERE..."
// the coordinates are saved in column "value"

Answer 1

It seems that what you need is to select all the database points within your range.

Without the table data it is impossible to write a full SQL query for you, however I had a fiddle and produced the following for you which should convert the strings into floats which you can then compare. It should work if all the strings are the same format, otherwise you can do much the same using instr() to select the location of the comma.

SELECT CAST( CONCAT_WS('.', LEFT( '1234,567', 4 ), RIGHT( '1234,567', 3 ) ) AS BINARY ) 

With this you might try calculating your expected min and max lat and long in php then plug them in vis:

SELECT lat,long FROM locations WHERE
CAST( CONCAT_WS('.', LEFT( lat, 4 ), RIGHT( lat, 3 ) ) AS BINARY ) >= '$minlat' AND
CAST( CONCAT_WS('.', LEFT( lat, 4 ), RIGHT( lat, 3 ) ) AS BINARY ) <= '$maxlat' AND
CAST( CONCAT_WS('.', LEFT( long, 4 ), RIGHT( long, 3 ) ) AS BINARY ) >= '$minlong' AND
CAST( CONCAT_WS('.', LEFT( long, 4 ), RIGHT( long, 3 ) ) AS BINARY ) <= '$maxlong'

I realise this isn't quite what you had in mind in the question but to me it seems the way to go.

EDIT:

Coping with the variable precision of the lat and long is done with instr() by calling:

 CAST( CONCAT_WS('.', LEFT( lat, INSTR(lat,',')-1),SUBSTRING( lat, INSTR(lat,',')+1 ) ) AS BINARY )

You do need to use the SUBSTRING() method rather than RIGHT() though.