使用MySQL SELECT AS获取当前的纬度/经度

问题描述：

I'm trying to retrieve listings that are near a user's current lat/long position.

Have tried MySQL's geospatial POINT to no avail, so returning to using DECIMAL lat/lon values for each record.

The below function is meant to get the listings within 10km (based on this formula) I realise the SELECT isn't quite right, but I'm not sure how to define the ((ACOS ... 1.1515) as distance and then also select the other columns.

// functions.php
function getDeals($type_of_deal) {
    $current_lat = -37.905;
    $current_lon = 175.505
    $result = mysql_query("
    SELECT i, company, 
        TIME_FORMAT(valid_time_start, '%l:%i %p'), 
        TIME_FORMAT(valid_time_end, '%l:%i %p'), 
        DATE_FORMAT(valid_expiry, '%D %b %y'),
        ((ACOS(SIN("$current_lat" * PI() / 180) * SIN(`company_lat` * PI() / 180) + 
        COS("$current_lat" * PI() / 180) * COS(`company_lat` * PI() / 180) * 
        COS(("$current_lon" – `company_lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)
    AS distance
    FROM listings
    WHERE deal_type = '" .$type_of_deal. "'
    HAVING distance<=’10′ 
    ORDER BY `distance` ASC
    ");
return $result;

// location.php
$type_of_deal = food;
$result = getDeals($type_of_deal);
    if (mysql_num_rows($result)) {
        while($row = mysql_fetch_array($result)) {
            echo $row["deal_title"];
            }
     }

我正在尝试检索靠近用户当前纬度/经度位置的列表。 p>

尝试过MySQL的地理空间POINT无济于事，因此返回使用每条记录的DECIMAL lat / lon值。 p>

以下函数用于获取10公里内的物品（基于这个公式）我意识到SELECT不是很正确，但我不确定如何将（（ACOS ... 1.1515）定义为距离，然后还选择其他列。 p>

  // functions.php 
function getDeals（$ type_of_deal）{
 $ current_lat = -37.905; 
 $ current_lon = 175.505 
 $ result = mysql_query（“
 SELECT i，company，  
 TIME_FORMAT（valid_time_start，'％l：％i％p'），
 TIME_FORMAT（valid_time_end，'％l：％i％p'），
 DATE_FORMAT（valid_expiry，'％D％b％y'），  
（（ACOS（SIN（“$ current_lat”* PI（）/ 180）* SIN（`company_lat` * PI（）/ 180）+ 
 COS（“$ current_lat”* PI（）/ 180）* COS  （`company_lat` * PI（）/ 180）* 
 COS（（“$ current  _lon“ - `company_lon`）* PI（）/ 180））* 180 / PI（））* 60 * 1.1515）
 AS距离
 FROM FROM listing 
 WHERE deal_type ='”。$ type_of_deal。  “'
 HAVING distance＆lt; ='10'
 ORDER BY`distance`ASC 
”）; 
return $ result; 
 
 // location.php 
 $ type_of_deal = food; 
 $ result =  getDeals（$ type_of_deal）; 
 if（mysql_num_rows（$ result））{
 while（$ row = mysql_fetch_array（$ result））{
 echo $ row [“deal_title”]; 
} 
} 
   code>  pre> 
  div>

答

You can try looking into MySQL great circle distance. And you can also take a look @ Google maps SQL api. It shows how to find a location near another location.

Now looking specifically at your code you have a couple of errors.

No semicolon after statements:

$current_lon = 175.505 $result = mysql_query("
No concatenation of strings (you use double quotes for your strings but also inside them).
No } after your function ends.

Here's a quick cleanup of your code:

function getDeals($type_of_deal) {
    $current_lat = -37.905;
    $current_lon = 175.505
    $result = mysql_query('SELECT i, company, 
        TIME_FORMAT(valid_time_start, "%l:%i %p"), 
        TIME_FORMAT(valid_time_end, "%l:%i %p"), 
        DATE_FORMAT(valid_expiry, "%D %b %y"),
        ((ACOS(SIN('.$current_lat.' * PI() / 180) * SIN(`company_lat` * PI() / 180) + 
        COS('.$current_lat.' * PI() / 180) * COS(`company_lat` * PI() / 180) * 
        COS(('.$current_lon.' – `company_lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)
    AS distance
    FROM listings
    WHERE deal_type = "'.$type_of_deal.'"
    HAVING distance<=10 
    ORDER BY `distance` ASC');
    return $result;
}

使用MySQL SELECT AS获取当前的纬度/经度

相关推荐