Geocode addresses

INSTALL spatial; LOAD spatial; SET geometry_always_xy = true;

-- the address index is the reader's to supply: one row per address, the way an offline geocoder holds one (a country-wide index is the same table at scale)
CREATE TEMP TABLE addresses AS SELECT * FROM (VALUES
  (1, '10', 'Downing Street', 'London', 'SW1A 2AA', -0.1276, 51.5034), (2, '11', 'Downing Street', 'London', 'SW1A 2AB', -0.1277, 51.5035), (5, '12', 'Downing Street', 'London', 'SW1A 2AB', -0.1279, 51.5036),
  (3, '221B', 'Baker Street', 'London', 'NW1 6XE', -0.1585, 51.5237), (4, '1', 'Abbey Road', 'London', 'NW8 9AY', -0.1778, 51.5320)) t(id, number, street, city, postcode, lon, lat);
-- the text of an address in one form: lower case, no accents or punctuation, one space, and 'st' written out
CREATE MACRO norm(a) AS regexp_replace(regexp_replace(lower(strip_accents(trim(a))), '[.,]', ' ', 'g'), '\s+', ' ', 'g');
CREATE MACRO expand(a) AS trim(regexp_replace(' ' || norm(a) || ' ', ' st ', ' street ', 'g'));
CREATE TEMP TABLE indexed AS SELECT *, expand(number || ' ' || street || ' ' || city) AS full_text, norm(postcode) AS pc FROM addresses;
CREATE TEMP TABLE asked AS SELECT * FROM (VALUES (1, '10 downing street, london'), (2, '221b  Baker St., London'), (3, 'SW1A 2AB'), (4, 'NW8'), (5, '99 Nowhere Lane')) t(q, text);
-- forward: an exact address match, else a full postcode, else the centre of the addresses under a postcode outward code.
-- Each branch is grouped to one row per key first (a postcode holds many addresses, and an address text may be listed twice), so a query never doubles.
CREATE TEMP TABLE by_address AS SELECT full_text AS k, avg(lon) AS lon, avg(lat) AS lat FROM indexed GROUP BY full_text;
CREATE TEMP TABLE by_postcode AS SELECT pc AS k, avg(lon) AS lon, avg(lat) AS lat FROM indexed GROUP BY pc;
CREATE TEMP TABLE by_outward AS SELECT split_part(pc, ' ', 1) AS k, avg(lon) AS lon, avg(lat) AS lat FROM indexed GROUP BY 1;
CREATE TEMP TABLE forward AS
SELECT a.q, a.text, coalesce(m.lon, p.lon, o.lon) AS lon, coalesce(m.lat, p.lat, o.lat) AS lat,
       CASE WHEN m.k IS NOT NULL THEN 'address' WHEN p.k IS NOT NULL THEN 'postcode' WHEN o.k IS NOT NULL THEN 'outward code' END AS matched_on
FROM asked a
LEFT JOIN by_address m ON m.k = expand(a.text)
LEFT JOIN by_postcode p ON p.k = norm(a.text)
LEFT JOIN by_outward o ON o.k = norm(a.text);
-- backward: the nearest indexed address, and only when it is within 100 m
CREATE TEMP TABLE points AS SELECT * FROM (VALUES (1, -0.12771, 51.50351), (2, -0.1780, 51.5319), (3, -0.5, 51.0)) t(q, lon, lat);
CREATE TEMP TABLE backward AS
SELECT q, id AS address_id, round(metres, 1) AS metres FROM (
  SELECT p.q, a.id, ST_Distance_Sphere(ST_Point(p.lon, p.lat), ST_Point(a.lon, a.lat)) AS metres, row_number() OVER (PARTITION BY p.q ORDER BY ST_Distance_Sphere(ST_Point(p.lon, p.lat), ST_Point(a.lon, a.lat))) AS rk
  FROM points p CROSS JOIN addresses a) WHERE rk = 1 AND metres <= 100;
SELECT * FROM forward ORDER BY q;
SELECT p.q, b.address_id, b.metres FROM points p LEFT JOIN backward b USING (q) ORDER BY p.q;
SELECT CASE WHEN ((SELECT count(*) = 5 AND count(DISTINCT q) = 5 FROM forward)
  AND (SELECT list([round(lon, 6), round(lat, 6)] ORDER BY q) = [[-0.1276, 51.5034], [-0.1585, 51.5237], [-0.1278, 51.50355], [-0.1778, 51.532], [NULL, NULL]] AND list(matched_on ORDER BY q) = ['address', 'address', 'postcode', 'outward code', NULL] FROM forward)
  AND (SELECT count(*) = 2 AND list(address_id ORDER BY q) = [2, 4] FROM backward)
  AND (SELECT metres < 5 FROM backward WHERE q = 1)) THEN 'as expected' ELSE error('the five queries should give one row each, the four known texts should resolve to the coordinates of their addresses, postcode and outward code, the unknown street should not, and the first two points should resolve to addresses 2 and 4 while the far point resolves to none') END AS verdict;