Call Local Function in Postgres Query

Question

I have a function which queries my database to find nearby users. However, my database stores the longitude and latitude of users, and to find the distance from the user, I need to call a function (distance) to check which users to return in the query. Is there a way to do what I have in the code (call my local distance function inside my querystring?)

exports.getNearby = function (longitude, latitude) {
var long1 = longitude;
var lat1 = latitude;
return new Promise(((resolve, reject) => {
    const queryString = `SELECT ${userModel.userId}
     FROM ${userModel.collectionName} where SELECT distance(${userLocationModel.latitude},
     ${userLocationModel.longitude}, ${lat1}, ${long1}) < 4`;
    db.query(queryString, (err, res) => {
        if (err) {
            logger.log('error', ` getUser - ${userIdArr} - ${err}`);
            reject(err);
        } else {
            resolve(res.rows[0]);
        }
    });
}));
};

function distance(lat1, lon1, lat2, lon2) {             //returns in km
  return 12742 * asin(sqrt(0.5 - cos((lat2 - lat1) * 0.01745329252)/2 
    + cos(lat1 * 0.01745329252) * cos(lat2 * 0.01745329252) * 
    (1 - cos((lon2 - lon1) * 0.01745329252)) / 2));
}

Function written in Postgres (as suggested by @LaurenzAlbe)

CREATE FUNCTION distance(
lat1 double precision,
lng1 double precision,
lat2 double precision,
lng2 double precision
) RETURNS double precision AS 
'SELECT 12742
     * asin(
         |/ (
             0.5 - cos(
                 (lat2 - lat1) * 0.01745329252
             )/2 
             + cos(lat1 * 0.01745329252)
             * cos(lat2 * 0.01745329252)
             * (1 - cos(
                 (lon2 - lon1) * 0.01745329252
             )) / 2
         )
    )'
LANGUAGE sql IMMUTABLE;

Laurenz Albe · Accepted Answer · 2019-02-27 15:48:35Z

1

If you want to use the function in an SQL statement, you have to define it inside the database using CREATE FUNCTION.

For a function like that, I'd choose LANGUAGE sql.

Have you considered using the PostGIS extension if you want to do geographical data processing inside the database? It offers all imaginable functions out of the box.

Here is how your function could look as SQL function:

CREATE FUNCTION distance(
    lat1 double precision,
    lng1 double precision,
    lat2 double precision,
    lng2 double precision
) RETURNS double precision AS 
'SELECT 12742
         * asin(
             |/ (
                 0.5 - cos(
                     (lat2 - lat1) * 0.01745329252
                 )/2 
                 + cos(lat1 * 0.01745329252)
                 * cos(lat2 * 0.01745329252)
                 * (1 - cos(
                     (lon2 - lon1) * 0.01745329252
                 )) / 2
             )
        )'
    LANGUAGE sql IMMUTABLE;

edited Feb 27, 2019 at 15:48

answered Feb 26, 2019 at 3:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

11 Comments

Abhishek Patel Over a year ago

Would the edit I added be the correct code syntax to create a postgres function?

Laurenz Albe Over a year ago

I have added my rendition.

Laurenz Albe Over a year ago

I forgot the IMMUTABLE.

Abhishek Patel Over a year ago

what does that keyword do?

Laurenz Albe Over a year ago

It tells PostgreSQL's query optimizer that the function will always return the same result for the same arguments. It also makes the function usable in indexes. In your case, the function will get inlined for efficiency.

|

Collectives™ on Stack Overflow

Call Local Function in Postgres Query

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related