php - How to find records based on given latitude and longitude with given distance in PostgreSQL
Get the solution ↓↓↓I am using this formula.
SELECT id, ( 3959 * acos( cos( radians(14.685327) ) * cos( radians( latitude ) )
* cos( radians( longitude ) - radians(80.054889) ) + sin( radians(14.685327) ) * sin(radians(latitude)) ) ) AS distance
FROM kr_user
HAVING distance < 50
ORDER BY distance
But I got error the following error:
ERROR: function radians(character varying) does not exist
LINE 1: ..., ( 3959 * acos( cos( radians(14.685327) ) * cos( radians( l...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
How to solve this. Or any other suggestion for this. I am using laravel platform for this.
Answer
Solution:
I guess your latitude and longitude columns are of type varchar and not of type numeric (as 14.685327 is).
So, try :
SELECT id, ( 3959 * acos( cos( radians(14.685327) ) * cos( radians( latitude::numeric ) )
* cos( radians( longitude::numeric ) - radians(80.054889) ) + sin( radians(14.685327) ) * sin(radians(latitude::numeric)) ) ) AS distance
FROM kr_user
HAVING distance < 50
ORDER BY distance
Now as you point out in the comments, you can not filter on a calculated column, personally I would use a lateral join to solve this issue :
SELECT id, t.distance
FROM kr_user
INNER JOIN LATERAL (
SELECT 3959 * acos(
cos( radians(14.685327) ) *
cos( radians( latitude::numeric ) ) *
cos( radians( longitude::numeric ) - radians(80.054889) ) +
sin( radians(14.685327) ) *
sin(radians(latitude::numeric))
) AS distance
WHERE latitude IS NOT NULL AND longitude IS NOT NULL
) t ON TRUE
WHERE t.distance < 50
ORDER BY t.distance
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: trying to access array offset on value of type bool in
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.