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

Question

I found this Stack Overflow question.

Distance in meters between two Spacial Points in MySQL query

I want to implement it for my use case but I get this error:

1210 - #1210 Incorrect arguments to st_distance_sphere.

I have a database table with these columns:

CompanyObjectId
CompanyName
CityName
Latitude
Longitude
CampingLocationType
PhotoUrl

How can I take the Latitude and Longitude from each row inside tbl_cc table and calculate a result that has distance in meters which is less than or equal 300 meters?

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

I also tried to do this query but got the same error:

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

****UPDATE****

Now where I execute my script in php it generates this query sql for including meters:

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)

In mysql admin it's working but in php I receive this error that make me crazy:

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

My script php is :

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;
}

And the return is putted inside function for generating 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;

    }

Thanks very much for any helps
Cristian

Oh my god, i forgot the dbms. Mysql. – Cristian Capannini Dec 16 '19 at 10:12 — Cristian Capannini, Dec 16 '19 at 10:12
updated question pls! – Cristian Capannini Dec 16 '19 at 15:31 — Cristian Capannini, Dec 16 '19 at 15:31

score 0 · Answer 1 · answered Dec 16 '19 at 11:25

0

Looking at your first query, you are not passing the radius correctly:

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

should be

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

The error you mention is raised "if the radius argument is present but not positive". See this for further reference

answered Dec 16 '19 at 11:25

Lelio Faieta

6,457
7
40
74

Ok radius is 300? Is in meters and be considered as <= 300? thanks – Cristian Capannini Dec 16 '19 at 13:25
ehm I'm in ancona but not working it must show nearest city by coordinates.... – Cristian Capannini Dec 16 '19 at 13:27
_"not working"_ means nothing. What is not working? What error do you get? Do you have any place in your table that is 300 **meters** from the cohordinates you provided? – Lelio Faieta Dec 16 '19 at 13:53
ehm sorry for short answer...ehm no I've not minus than 300 meters.... if I'm in Ancona (Italy) 300 meters not showing any places entire 300 meters but showing places in Germany... – Cristian Capannini Dec 16 '19 at 14:14
then how is your query supposed to return anything? – Lelio Faieta Dec 16 '19 at 14:15
my query should restiti given a starting point the points near and within a range of x meters – Cristian Capannini Dec 16 '19 at 14:18
Try this link and I'll get this error! http://www.testcolan.it/campit/rest_api/getListaDoveSostare.php?user_id=&ord=crescente&kmmax=100&lat=43.5581473&lng=13.5132756 – Cristian Capannini Dec 16 '19 at 15:10

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

1 Answers1