Given address ids 1,2,3, we would want a single query to returns rows for every combination of directions, ie:
1,2
1,3
2,1
2,3
3,1
3,2
Example Table (just distance but we'd want time, geometry, etc):
CREATE TABLE address_distances (
from_address_id INT NOT NULL,
to_address_id INT NOT NULL,
distance FLOAT,
PRIMARY KEY (from_address_id, to_address_id)
);
To get the results for every combination of from_address_id and to_address_id given a set of address IDs, you can use the following query:
WITH address_ids AS (
SELECT UNNEST(ARRAY[1, 2, 3]) AS address_id
)
SELECT ad1.address_id AS from_address_id,
ad2.address_id AS to_address_id,
d.distance
FROM address_ids ad1
CROSS JOIN address_ids ad2
LEFT JOIN address_distances d
ON ad1.address_id = d.from_address_id AND ad2.address_id = d.to_address_id
WHERE ad1.address_id <> ad2.address_id;
Explanation: Common Table Expression (CTE): The WITH clause creates a CTE named address_ids that contains the input address IDs. Cross Join: The CROSS JOIN generates all combinations of from_address_id and to_address_id. Left Join: The LEFT JOIN fetches the distance for each combination from the address_distances table. Filter: The WHERE clause ensures that combinations where from_address_id is not equal to to_address_id are included.
Find Missing Combinations (ex: 1,3 and 3,1 not cached yet)
WITH address_ids AS (
SELECT UNNEST(ARRAY[1, 2, 3]) AS address_id
),
all_combinations AS (
SELECT ad1.address_id AS from_address_id,
ad2.address_id AS to_address_id
FROM address_ids ad1
CROSS JOIN address_ids ad2
WHERE ad1.address_id <> ad2.address_id
)
SELECT ac.from_address_id, ac.to_address_id
FROM all_combinations ac
LEFT JOIN address_distances d
ON ac.from_address_id = d.from_address_id AND ac.to_address_id = d.to_address_id
WHERE d.from_address_id IS NULL;
Explanation: Common Table Expression (CTE) - address_ids: The WITH clause creates a CTE named address_ids that contains the input address IDs. CTE - all_combinations: The second CTE generates all possible combinations of from_address_id and to_address_id using a cross join. The WHERE clause ensures that combinations where from_address_id is not equal to to_address_id are included. Left Join with address_distances: The left join matches the combinations from all_combinations with the entries in the address_distances table. Filter Missing Combinations: The WHERE clause d.from_address_id IS NULL ensures that only the combinations that are not present in the address_distances table are selected.