Sql query using database function

Question

i have DataBase function that calculate distance by coordinates

CREATE OR REPLACE FUNCTION distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS FLOAT AS $$
DECLARE                                                   
    x float = 69.1 * (lat2 - lat1);                           
    y float = 69.1 * (lon2 - lon1) * cos(lat1 / 57.3);        
BEGIN                                                     
    RETURN sqrt(x * x + y * y);                               
END  
$$ LANGUAGE plpgsql;

now i have 2 tables (with one to one relation) one called

person with columns --> Personid, name, lastname

and one

location with columns --> Personid,latitude,longtitude.

now i try to get the person that the distance is less to 5 using the distnace function

i start with this following query to get the id first

select loc.id,loc.latitude,loc.longitude from location loc
where distance(123,456,loc.latitude,loc.longitude)<0.5

but don't know how to get the all persons with the id from the above query.

how can i do that? thanks in advance.

barsju · Accepted Answer · 2012-04-05 21:33:01Z

2

Is this what your are looking for?

select p.*, loc.id,loc.latitude,loc.longitude 
from location loc inner join person p on (p.Personid=loc.Personid)
where distance(123,456,loc.latitude,loc.longitude)<0.5

answered Apr 5, 2012 at 21:33

barsju

4,4561 gold badge23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

barsju Over a year ago

This is very basic! Read up: postgresql.org/docs/8.3/static/tutorial-join.html

Collectives™ on Stack Overflow

Sql query using database function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related