{
curl -X “GET” “https://musicalscarecrow.backendless.app/api/services/Website/matchesTab?phoneNumber=0000000000&postalCode=452016&pagging=10&page_no=1”
-H ‘Content-Type: application/json’
-H ‘Accept: application/json’
}
Hi @vladimir-upirov ,
I got this issue, it’s because of sort by distance. So, Is there any other way to sort by distance?
Backendless may change the distance method. So I have done with the new method but I got an Error in this too. Please resolve this as soon as possible.
Old Code
New Code
And Now getting this issue
Hi Backendless Team,
Waiting for your response.
Hi @Ayaz_Khan,
In your case you didn’t pass correct arguments for distance(...) function. This function expects to receive two arguments of spatial point type but you passed 4 arguments of string type.
In case with distanceOnSphere(...) it seems to be the same situation - your location column has wrong type. It should have spatial type.
Regards, Andriy
Hi @Andriy_Konoz ,
I have done this distance(…) in the old code and it was working fine but suddenly API give me the error that invalid where clause.
https://backendless.com/docs/js/data_search_by_distance.html
then I have read some topics regarding this topic and I got this topic.
Then I have changed my code according to new backendless changes can you please suggest to me how can I pass spatial type latitude and longitude column names in distanceOnSphere(…)?
Here is the table columns:-
Your new code seems to be valid. Could you please try to create the same query using REST Console?
The only thing which can case such error is invalid values which you pass using variables. If they are out of range, then this error can appear
Regards, Andriy
Getting Error “Incorrect arguments to st_distance_sphere”
And Can you please suggest how can I pass separate latitude and longitude columns like before I used in the distance(…)?
Example :- distance(38.89,-77.03 ,latitude , longitude) as dist
This is very strange error because it appears in your app but doesn’t appear in my test app. At the same time if to use distance(...) function instead with the same params there will be no problem (I tried it in your app). Not sure at the current moment what exactly causes this problem.
And Can you please suggest how can I pass separate latitude and longitude columns like before I used in the distance(…)?
It is not possible to pass longitude and latitude as separate params to this function in new geolocation API. I will ask my colleagues if it possible to dynamically create spatial points in “where clause” and properties
Regards, Andriy
Can you please investigate and help me resolve this issue, a few weeks ago all code working fine, and suddenly this error comes, My app features not working because of this and I have to resolve this issue as soon as possible.
Thanks
Returning to your original problem for which you have created this topic.
That code, from your first screenshot, where you pass 4 params in distance(...) was removed 5 moths ago in favor of native distance function from DB. Even when it was present it was not precise. We forgot to update our documentation at that point. We a very sorry for misguiding you.
About distanceOnSphere(...) function. This function requires that your points should be in WGS84 system of coordinates while distance(...) function works with Cartesian system of coordinates. Valid values for longitude and latitude in WGS84 belong to the range [-90, 90]. In your case some of the points from location column were not valid in WGS84. Due to this you got that error “Incorrect arguments to st_distance_sphere”.
And Can you please suggest how can I pass separate latitude and longitude columns like before I used in the distance(…)?
I have got an answer from my colleagues. It is not possible with the existing means to create spatial points for “where clause” dynamically during request. You should have a column of spatial point type for queries which use geo functions.
Regards, Andriy
About
distanceOnSphere(...)function. This function requires that your points should be in WGS84 system of coordinates whiledistance(...)function works with Cartesian system of coordinates. Valid values for longitude and latitude in WGS84 belong to the range [-90, 90]. In your case some of the points fromlocationcolumn were not valid in WGS84. Due to this you got that error “Incorrect arguments to st_distance_sphere”.
Is there any other way to sort by distance closest to further work with the Cartesian system of coordinates? like distance() function work.
Thanks,
It seems that I have confused you 
You can use distance(...) for this. Instead of passing to it 4 separate coordinates you should pass points. Your “where clause” will look in the next way: distance(location, 'POINT(<your longitude> <your latitude>)') <= 100000. Or if you want to define property and sort by it you can put distance(location, 'POINT(<your longitude> <your latitude>)') as dist in property definition and then sort by it. In both examples column location of Point type.
Regards, Andriy
Thanks for your help now it’s working fine.
In another table, my latitude and longitude columns separate, So how can I use the distance function in that case?
Thanks
You should create additional column which will hold location as a point and use that column in queries. Unfortunately there is no other way.
Regards, Andriy
I have a bulk of records in many tables which have separate latitude and longitude columns how can I create a new column and merge latitude and longitude in Point(…) because at a time query takes only 100 records. Is there any simple way?
Hello @Ayaz_Khan
You can use the generated column Introducing The Generated Columns Feature | Backendless
Regards,
Inna
Hi @Inna_Shkolnaya ,
There is no option for location points in generated columns.
I need to generate a column for location points(latitude longitude). I have separate columns for latitude and longitude
Hi @Ayaz_Khan ,
My colleague misguided you a little bit. Generated columns concept will not work for your case.
You will need to create a client side script which will iterate over each of those tables and generate value for new column of “Point” type. To iterate over all records you will need to use pagination since you can extract only up to 100 records per request. You can check this link to get more information about pagination in Backendless Data Service.
Regards, Andriy