MYSQL : Selecting from table passing 2 columns value : Incorrect arguments to st_distance_sphere

Question

我发现了这个Stack Overflow问题。

Distance in meters between two Spacial Points in MySQL query

我想为我的用例实现它，但出现此错误:

1210 - #1210 Incorrect arguments to st_distance_sphere.

我有一个包含以下列的数据库表:

• CompanyObjectId
• 公司名称
• 城市名称
• 纬度
• 经度
• 露营地点类型
• 照片网址

如何从tbl_cc表中的每一行获取纬度和经度并计算出距离以米为单位的结果，该结果小于大于或等于 300 米？

SELECT CompanyObjectId, CompanyName, CityName, Latitude, Longitude,
       CampingLocationType, PhotoUrl
FROM tbl_cc
WHERE st_distance_sphere(POINT(13.5145461,43.564368), POINT(Longitude, Latitude)) <= 300

我也尝试执行此查询，但得到了相同的错误:

SELECT * FROM (SELECT CompanyObjectId, CompanyName, CityName, Latitude, Longitude,
       CampingLocationType, PhotoUrl, (st_distance_sphere(POINT(13.5145461,43.564368), POINT(Longitude, Latitude))) AS MetriDaPosizione
FROM tbl_camper_contact) AS Q WHERE MetriDaPosizione <= 300

****更新****

现在，我在 php 中执行脚本，它会生成此查询 sql 以包含仪表:

SELECT CompanyObjectId, CompanyName, CityName, Latitude, Longitude,
                  CampingLocationType, PhotoUrl, st_distance_sphere(point(13.5129324,43.5578855), point(CAST(Longitude AS DECIMAL(18,14)), CAST(Latitude AS DECIMAL(18,14))), 100000) as Metri
                  FROM tbl_camper_contact
            WHERE st_distance_sphere(point(13.5129324,43.5578855), point(CAST(Longitude AS DECIMAL(18,14)), CAST(Latitude AS DECIMAL(18,14))), 100000)

在 mysql 管理中它可以工作，但是在 php 中我收到这个错误，这让我发疯:

Mon, 16 Dec 2019 16:14:26 GMT [error] [client 93.36.55.43] - www.testcolan.it - AH01215: PHP Warning: mysqli_query(): (HY000/1210): Incorrect arguments to st_distance_sphere in /web/htdocs/www.testcolan.it/home/campit/rest_api/sql/DBOperations.php on line 759: /web/htdocs/www.testcolan.it/home/campit/rest_api/getListaDoveSostare.php Mon, 16 Dec 2019 16:14:26 GMT [error] [client 93.36.55.43] - www.testcolan.it - AH01215: PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /web/htdocs/www.testcolan.it/home/campit/rest_api/GeneralFunctions.php on line 242: /web/htdocs/www.testcolan.it/home/campit/rest_api/getListaDoveSostare.php

我的 php 脚本是:

public function GetListaDoveSostare($lat, $lng, $kmmin, $ordinamentoData, $localita)
{
  $metri = (intval($kmmin) * 1000);

  $com = new DbConnect();
  $sql = "";


    $sql = "SELECT CompanyObjectId, CompanyName, CityName, Latitude, Longitude,
                  CampingLocationType, PhotoUrl, st_distance_sphere(point(".floatval($lng).",".floatval($lat)."), point(CAST(Longitude AS DECIMAL(18,14)), CAST(Latitude AS DECIMAL(18,14))), $metri) as Metri
                  FROM tbl_camper_contact
            WHERE st_distance_sphere(point(".floatval($lng).",".floatval($lat)."), point(CAST(Longitude AS DECIMAL(18,14)), CAST(Latitude AS DECIMAL(18,14))), $metri)";

  mysqli_set_charset($com->getDb(), 'utf8');
  $result = mysqli_query($com->getDb(), $sql);
  return $result;
}

并将返回值放入生成json的函数中:

 public function GetListaDoveSostare($resultDoveSostare, $lat, $lng)
    {
      $dbOperationsObject = new DBOperations();
      $doveSostare= array();

        while ($row = mysqli_fetch_array($resultDoveSostare)) {

          $dove_sostare = array(
            "CompanyObjectId" => $row['CompanyObjectId'],
            "CompanyName" => $row['CompanyName'],
            "CityName" => $row['CityName'],
            "Latitude" => $row['Latitude'],
            "Longitude" => $row['Longitude'],
            "CampingLocationType" => ($row['CampingLocationType'] == "Camperplace") ? "Area Sosta" : "Campeggio",
            "PhotoUrl" => $row['PhotoUrl'],
            "MetriDaPosizioneCorrente" =>  $row['Metri']//$metri

          );

          $json= json_encode($dove_sostare);
          $dove_sostare = json_decode($json);
          array_push($doveSostare, $dove_sostare);
      }




      return $doveSostare;

    } 

非常感谢您的帮助
克里斯蒂安

Answer 1

查看您的第一个查询，您没有正确传递半径:

SELECT CompanyObjectId, CompanyName, CityName, Latitude, Longitude,
       CampingLocationType, PhotoUrl
FROM tbl_cc
WHERE st_distance_sphere(POINT(13.5145461,43.564368), POINT(Longitude, Latitude)) <= 300

应该是

SELECT CompanyObjectId, CompanyName, CityName, Latitude, Longitude,
       CampingLocationType, PhotoUrl
FROM tbl_cc
WHERE st_distance_sphere(POINT(13.5145461,43.564368), POINT(Longitude, Latitude), 300)

您提到的错误会出现“如果半径参数存在但不为正”。请参阅this供进一步引用