SQL for Dating Apps: Finding Matches Within Distance & Filtering by Preferences

Geolocation-Based Matching

Dating apps must match users within a radius while respecting preferences (age range, gender, interests). Pure SQL with PostGIS makes this fast at scale.

1. Haversine Distance Within 50km

Find all users within 50km of a given location using the Haversine formula — no PostGIS extension required.

SELECT
    user_id,
    username,
    latitude,
    longitude,
    6371 *
        ACOS(
            COS(RADIANS(given_lat)) * COS(RADIANS(latitude))
            + SIN(RADIANS(given_lat)) * SIN(RADIANS(latitude))
            * COS(RADIANS(given_lon - given_lon))
        ) AS distance_km
FROM users
HAVING distance_km <= 50
ORDER BY distance_km;

2. Filter by Age Range and Dealbreakers

Apply preference filters: age 18-30, not smoking, and looking for men (if user is woman).

SELECT
    user_id,
    username,
    age,
    smoke,
    gender_preference,
    looking_for_gender
FROM users
WHERE age BETWEEN 18 AND 30
  AND smoke = false
  AND looking_for_gender = 'M'
  AND user_id != given_user_id;  -- Exclude the current user

3. Rank Matches by Compatibility Score

Rank matches by shared interests, age proximity, and distance — closest + most compatible first.

SELECT
    u.user_id,
    u.username,
    u.age,
    6371 * ACOS(
        COS(RADIANS(given_lat)) * COS(RADIANS(u.latitude))
        + SIN(RADIANS(given_lat)) * SIN(RADIANS(u.latitude))
        * COS(RADIANS(given_lon - u.longitude))
    ) AS distance_km,
    -- Compatibility: shared interests + age proximity
    (COUNT(DISTINCT i.interest) FILTER (WHERE u.interest = i.interest) * 10
     + 10 - ABS(u.age - given_age)) AS compatibility_score
FROM users u
JOIN interests i ON u.user_id = i.user_id
JOIN interests given_i ON given_user_id = given_i.user_id
WHERE u.user_id != given_user_id
  AND u.age BETWEEN 18 AND 30
  AND u.smoke = false
GROUP BY u.user_id, u.username, u.age
ORDER BY compatibility_score ASC, distance_km ASC;

Key Takeaways for Production

• The Haversine formula computes great-circle distance on a sphere — accurate enough for most dating apps (Earth radius = 6371km).
• For production scale, PostGIS with <-> (operator distance) or DISTANCE is orders of magnitude faster than pure SQL Haversine.
• Always exclude the current user (user_id != given_user_id) — otherwise they'd see themselves in matches!
• Index latitude, longitude, and gender_preference for performance on millions of users.