The best way to develop locations within the radius of the starting point

Question

I am going to create a function in my last project, preferably using PHP. When each user signs up, they are about to enter their zip code. Then, hopefully, I will convert this to lat / long using the Open Street Map.

In any case, I want to know other users who are close to the current user. I have seen many people using the Haversin formula, however this would mean that the user requested all other user data to determine the distance. I could cache this, but it will soon become obsolete when new users arrive.

How will the following query be executed on my system?

sql = "SELECT zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) 
    * cos( radians( latitude ) ) * cos( radians( longitude ) 
    - radians( {$coords['longitude']} ) ) 
    + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) 
    AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";

What pulled from someone’s blog.

I do not have any numbers for the registration speed or the number of users who are still under development.

I would appreciate any feedback or other methods that I could use to find suitable users within a certain radius.

Answer 1

Version 4.1 has GIS and spatial extensions mySql, see here . From the description you will find that it is used for problems like you here:

( ) , , , . , . , ​​ , .

MySql, :

• Spacial keys POINT:

  CREATE TABLE ( CHAR (80) NOT NULL, address_loc POINT NOT NULL, (), (address_loc) );

• INSERT INTO address VALUES ('Foobar street 12', GeomFromText ('POINT (2671 2500)'));

• SELECT c.cabdriver, ROUND (GLength (LineStringFromWKB (LineString (AsBinary (c.cab_loc),                                            AsBinary (a.address_loc)))))   AS FROM cab c, ORDER BY distance ASC LIMIT 1;

(, )

Answer 2

, " ", . "", "" "" ( ). :

SELECT * FROM location WHERE
  lat BETWEEN (my_lat - radius) AND (my_lat + radius)
  AND long BETWEEN (my_long - radius) AND (my_long + radius);

, , .

Answer 3

, Javascript, PHP, , .

, . , , , .

....

<script type="text/javascript">
function getDistance(lat1,lng1,lat2,lng2)
 {
  p1 = new VELatLong(lat1,lng1);
  p2 = new VELatLong(lat2,lng2);
  miles = true;
  p1.Latitude= latLonToRadians(p1.Latitude);
  p1.Longitude= latLonToRadians(p1.Longitude);
  p2.Latitude= latLonToRadians(p2.Latitude);
  p2.Longitude= latLonToRadians(p2.Longitude);
  var R = 6371; // earth mean radius in km
  var dLat  = p2.Latitude- p1.Latitude;
  var dLong = p2.Longitude- p1.Longitude;
  var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
  Math.cos(p1.Latitude) * Math.cos(p2.Latitude) * Math.sin(dLong/2) * 
Math.sin(dLong/2);
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
  var disKm = R * c;
  var disMiles = disKm * 0.6214;
  alert (miles ? disMiles : disKm);
 }
 //  convert lat/long in degrees to radians
 function latLonToRadians( point )
 {
  return point * Math.PI / 180;
 }
</script>

Oh, and VELatLong objects come from the virtual earth API ( http://msdn.microsoft.com/en-us/library/bb412519.aspx ), but basically an illustrious structure, so you should find a suitable replacement