PostgreSQL查询添加用户指定半径内事件筛选条件失败求助

现有PostgreSQL查询可按用户偏好筛选事件，需添加仅获取用户指定半径范围内事件的条件，尝试Haversine距离公式后无效，相关信息及修正方案如下：

现有查询语句 #

const get_straight_events_query = `
SELECT 
    e.id AS event_id,
    u.gender,
    u.age ,
    u.height_feet,
    u.height_inches,
    u.has_kids,
    es.interested_sex,
    es.interested_has_kids,
    es.intersted_min_height_feet,
    es.intersted_min_height_inches,
    es.min_age,
    es.max_age,
    u.gender,
    e.latitude,
    e.longitude
FROM events e
JOIN event_search_settings es ON e.created_by = es.created_by
JOIN users u ON e.created_by = u.id
WHERE 
    e.created_by != $1 AND
    u.gender = $2 AND
    u.age >= $3 AND
    u.age <= $4 AND
    u.height_feet >= $5 AND
    u.height_inches >= $6 AND
    u.has_kids = $7 AND
    es.interested_sex = $8 AND
    es.max_age >= $9 AND
    es.min_age <= $9 AND
    es.intersted_min_height_feet <= $10 AND
    es.intersted_min_height_inches <= $11 AND
    es.interested_has_kids = $12 
;
`;

events表结构 #

CREATE TABLE events (
    id VARCHAR(255) PRIMARY KEY,
    active BOOLEAN,
    created_by VARCHAR(255),
    updated_by VARCHAR(255),
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    location VARCHAR(255),
    description VARCHAR(255),
    state VARCHAR(255),
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    latitude FLOAT,
    longitude FLOAT
);

数据查询服务类 #

class EventService {
    async get_events(data) {
        console.log("[EventService]  get_events " ,data);
        if (data.user_interested_sex == BOTH) {
          // 省略其他逻辑
        } else {
            return await pool.query(get_straight_events_query, [
                data.user_id,
                data.user_interested_sex,
                data.user_interested_min_age,
                data.user_interested_max_age,
                data.user_intersted_min_height_feet,
                data.user_intersted_min_height_inches,
                data.user_interested_has_kids,
            
                data.user_gender,
                data.user_age,
                data.user_height_feet,
                data.user_height_inches,
                data.user_has_kids,    
            ]);
        }
    }
}

尝试过的无效距离筛选条件 #

AND (
    6371 * acos(
        cos(radians($13)) * cos(radians(e.latitude)) * cos(radians(e.longitude) - radians($14)) +
        sin(radians($13)) * sin(radians(e.latitude))
    )
) * 0.621371

修正后的实现方案 #

问题分析 #

之前的距离公式仅计算了数值，但未与用户指定的半径阈值做小于等于的比较，这是筛选无效的核心原因；同时需确保参数对应正确的用户当前经纬度。

修正后的查询语句 #

添加距离筛选条件，并可选择返回计算出的距离用于验证：

const get_straight_events_query = `
SELECT 
    e.id AS event_id,
    u.gender,
    u.age ,
    u.height_feet,
    u.height_inches,
    u.has_kids,
    es.interested_sex,
    es.interested_has_kids,
    es.intersted_min_height_feet,
    es.intersted_min_height_inches,
    es.min_age,
    es.max_age,
    u.gender,
    e.latitude,
    e.longitude,
    -- 可选：返回计算出的距离（单位：英里）
    (6371 * acos(
        cos(radians($13)) * cos(radians(e.latitude)) * cos(radians(e.longitude) - radians($14)) +
        sin(radians($13)) * sin(radians(e.latitude))
    )) * 0.621371 AS distance_miles
FROM events e
JOIN event_search_settings es ON e.created_by = es.created_by
JOIN users u ON e.created_by = u.id
WHERE 
    e.created_by != $1 AND
    u.gender = $2 AND
    u.age >= $3 AND
    u.age <= $4 AND
    u.height_feet >= $5 AND
    u.height_inches >= $6 AND
    u.has_kids = $7 AND
    es.interested_sex = $8 AND
    es.max_age >= $9 AND
    es.min_age <= $9 AND
    es.intersted_min_height_feet <= $10 AND
    es.intersted_min_height_inches <= $11 AND
    es.interested_has_kids = $12 AND
    -- 筛选距离小于等于用户指定半径（单位：英里）
    (6371 * acos(
        cos(radians($13)) * cos(radians(e.latitude)) * cos(radians(e.longitude) - radians($14)) +
        sin(radians($13)) * sin(radians(e.latitude))
    )) * 0.621371 <= $15
;
`;

对应服务类修改 #

需在参数列表中添加用户的纬度、经度，以及指定的半径值：

class EventService {
    async get_events(data) {
        console.log("[EventService]  get_events " ,data);
        if (data.user_interested_sex == BOTH) {
          // 省略其他逻辑
        } else {
            return await pool.query(get_straight_events_query, [
                data.user_id,
                data.user_interested_sex,
                data.user_interested_min_age,
                data.user_interested_max_age,
                data.user_intersted_min_height_feet,
                data.user_intersted_min_height_inches,
                data.user_interested_has_kids,
            
                data.user_gender,
                data.user_age,
                data.user_height_feet,
                data.user_height_inches,
                data.user_has_kids,
                // 新增参数：用户纬度、用户经度、指定半径（英里）
                data.user_latitude,
                data.user_longitude,
                data.radius    
            ]);
        }
    }
}

注意事项 #

• 若使用公里作为半径单位，去掉公式中的* 0.621371，并确保$15传入的是公里数值。
• 确保data对象中包含user_latitude、user_longitude、radius三个字段的有效值。

内容的提问来源于stack exchange，提问作者user17281101