Using a function within a select PostgreSQL

Question

I have a function that calculates the distance between two points given the latitude and longitude of the two points:

my_function (lat_point1 NUMERIC, long_point1 NUMERIC, lat_point2 NUMERIC, long_point2 NUMERIC)

I want to use the function in a select to calculate the distance between my point1, which is always the same, and my point2, which has multiple values.

I have a table with 3 columns and several rows. The first column is the name of the point in the map, the second is the latitude of the place and the third one has the longitude. The rows has the different points.

The function has 4 inputs my_function(latutude_point1, longitude_point1, latutude_point2, longitude_point2).

The problem is that I need to calculate the distance between the point1 with all the others. In other words, I need to calculate the distance between the point1 with point2, point1 with point3, point1 with point4, point1 with pointn.

How can I call the function in a select?

yes, but how can I tell which lon and lat one is the point1 and which ones longs and lats are the point2? — Sylvari
– Sylvari, Commented Jan 3, 2018 at 19:33
I have my points in a table. Imagine its the table tb_points that have this extructure num_point, long_point, lat_point. So the first row will be point1, 1.5641, 45.6555, the second one will be point2, 1.6895, 25.3654 and so on. I dont know how to tell the function witch points should take. I want to calcule the distance between point1 and point2, point1 and point3 ... point1 and pointn. — Sylvari
– Sylvari, Commented Jan 3, 2018 at 19:42
Calling the function will be trivial once you've got a Select that has all the colums you need. You'll likely need to call LEAD or LAG or you can use a self join. I don't know which because your question is missing some info — Conrad Frix
– Conrad Frix, Commented Jan 3, 2018 at 19:53

Gordon Linoff · Accepted Answer · 2018-01-03 20:17:44Z

1

You are looking for a cross join:

select t1.*, t2.*,
       my_function(t1.lat, t1.lng, t2.lat, lt2.lng)
from t t1 cross join
     t t2;

You may want to include where t1.lat <> t2.lat and t1.lng <> t2.lng to avoid self comparisons.

answered Jan 3, 2018 at 20:17

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Using a function within a select PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related