CODEWITHSUNDEEP
sqlpostgresql
Sylvari
Sylvari

Reputation: 23

Using a function within a select PostgreSQL

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?

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

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.

Upvotes: 1

Related Questions