根据邮编/经度/纬度选择结果

i have 3 tables: 1 contains people with their city (comes from other table) and the range (int, 1-20) they want to be found in (in miles) 1 contains cities and the beginning of the postcode (i.e. BB2) 1 contains cities and their logitudes/latitudes

我有3个表:1包含了他们所在城市的人(来自其他表)和他们想要在(英里)中找到的范围(int, 1-20) 1包含了城市和邮编(即BB2) 1的开头包含了城市和它们的logitudes/纬度

what i am trying to do is select the people according to a postcode someone enters. if this postcode is for a city within their range then the person should be selected from the DB.

我要做的是根据某人输入的邮编来选择人。如果此邮编是针对其范围内的城市，则应该从DB中选择此人。

i have this php code:

我有这个php代码:

  // Latitude calculation
  $limit = (1 / 69.1703234283616) * $radius;
  $latitude_min = $latitude - $limit;
  $latitude_max = $latitude + $limit;

  // Longitude calculation
  $limit = (1 / (69.1703234283616 * cos($userLat * (pi/180)))) * $radius;
  $longitude_min = $longitude - $limit;
  $longitude_max = $longitude + $limit;

now heres the hard part. i dont know how i am going to select the people from the DB, depending on what postcode was entered and their range.

下面是难点。我不知道如何从DB中选择人员，这取决于输入的邮编及其范围。

can someone help me out a little here?

有人能帮我一下吗?

1 个解决方案

#1

Here is a stored function for MySQL I created a while ago - it returns the (approximate) distance between two pairs of latitude and longitude in meters.

这是我不久前创建的MySQL的一个存储函数——它返回(近似的)两对纬度和经度之间的距离(以米为单位)。

DELIMITER $$

CREATE FUNCTION LAT_LNG_DISTANCE(lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT) RETURNS int(11)
BEGIN
  DECLARE latD FLOAT;
  DECLARE lngD FLOAT;
  DECLARE latS FLOAT;
  DECLARE lngS FLOAT;
  DECLARE a FLOAT;
  DECLARE c FLOAT;

  SET lat1 = RADIANS(lat1);
  SET lng1 = RADIANS(lng1);
  SET lat2 = RADIANS(lat2);
  SET lng2 = RADIANS(lng2);

  SET latD = lat2 - lat1;
  SET lngD = lng2 - lng1;

  SET latS = SIN(latD / 2);
  SET lngS = SIN(lngD / 2);

  SET a = POW(latS, 2) + COS(lat1) * COS(lat2) * POW(lngS, 2);
  SET c = 2 * ASIN(LEAST(1, SQRT(a)));

  RETURN ROUND(c * 6378137);
END;
$$
DELIMITER ;

Now we have that defined, we need to get the origin of the search based on the postcode the user has given us. As I don't know the exact structure of your database, I will use generic joins and conditions that will hopefully be similar enough to the real thing for you to adapt easily .I'm going to use $var to imply passing the value from PHP; needless to say, it will need escaping in an appropriate manner.

现在我们已经定义好了，我们需要基于用户给我们的邮政编码来获取搜索的起源。由于我不知道您的数据库的确切结构，我将使用泛型连接和条件，希望它们与真实的连接足够相似，以便您轻松地适应。不用说，它需要以适当的方式进行转义。

SELECT l.lat, l.lng
FROM city_locations l
INNER JOIN city_postcodes c
ON l.city_id = c.city_id
WHERE c.postcode = $postcode;

Now we can pass the results of that query to the next query, which will use the LAT_LNG_DISTANCE function we defined earlier.

现在，我们可以将该查询的结果传递给下一个查询，该查询将使用我们前面定义的LAT_LNG_DISTANCE函数。

SELECT *
FROM people p
INNER JOIN city_locations l
ON p.city_id = l.city_id
WHERE LAT_LNG_DISTANCE(l.lat, l.lng, $search_lat, $search_lng) < p.range * 1609;

1609 is the number of meters in a mile, by the way. I hope I have understood the question correctly!

顺便说一下，1609是一英里的米数。我希望我理解对了这个问题!

#1