How do I select pairs from table with distance less than x?

Question

In my database, I have a table of houses. Each house has a street id and a distance in meters from the start of the street.

I want to find all pairs of houses along the same street that are in a distance less than e.g. 20 meters from each other.

This is a sample table called HOUSES

house_id  street_id  distance_from_beginning
1         100        5
2         100        10
3         100        15
4         200        5
5         200        20
6         200        10

I am searching for an SQL statement that looks like

SELECT 
    h1.house_id, 
    h2.house_id, 
    abs(h1.distance_from_beginning - h2.distance_from_beginning) distance
from HOUSES h1, h2 
where h1.street_id=h2.street_id and distance < 20

Is this possible? What would be the correct syntax for a Postgresql database?

Answer 1

Use a self-join on the HOUSE table where houses are in the same street :

SELECT 
    h1.house_id, h2.house_id, ABS(h1.distance - h2.distance)
FROM 
    HOUSES h1
    INNER JOIN HOUSES h2 
        ON  h1.street_id = h2.street_id 
        AND h1.house_id < h2.house_id
        AND ABS(h1.distance - h2.distance) < 20

Tested in this db fiddle.