Address Cache Proof of Concept

concept.md

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.