912359f38b
scripts/migration/normalize_service_locations.sql : rattache chaque Service Location à l'adresse canonique Adresses Québec (table locale rqa_addresses) par CODE POSTAL + NUMÉRO (clé sélective indexée) + meilleure rue par similarité désaccentuée (unaccent + pg_trgm). Les 2 tables étant colocalisées dans la db ERPNext, tout se fait en SQL (~26 s pour 16 345 lignes, pas d'aller-retour applicatif). Remplit (sans toucher address_line/city/postal_code = origine préservée → table de translation) : - aq_address_id = rqa_addresses.id (clé de translation locale ; ⚠ id LOCAL, pas l'uuid AQ officiel absent de la table locale) - linked_address = adresse canonique conforme (address_full) - address_validation_status = validated (sim≥0.20) | review (sim<0.20, ex. surnoms de camping) | unmatched - latitude/longitude raffinées seulement si validated Résultat : validated 14 931 (87%) · review 1 414 (8%) · unmatched 766 (4,5%) → 16 345 liés (95,5%). Idempotent (ne traite que les lignes 'pending'). Couvre la tâche « backfill normalisation 17k SL ». Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
52 lines
2.5 KiB
PL/PgSQL
52 lines
2.5 KiB
PL/PgSQL
-- normalize_service_locations.sql — « rendre conformes » les adresses de service ERPNext via la base
|
|
-- LOCALE rqa_addresses (Adresses Québec), sans détruire l'adresse originale (table de translation embarquée).
|
|
--
|
|
-- Pour chaque Service Location : on cherche la correspondance RQA par CODE POSTAL + NUMÉRO (clé sélective,
|
|
-- indexée), on choisit la meilleure rue par similarité désaccentuée, puis on remplit :
|
|
-- aq_address_id = rqa_addresses.id (clé de translation locale) [⚠ id LOCAL, pas l'uuid AQ officiel]
|
|
-- linked_address = adresse canonique conforme (address_full)
|
|
-- address_validation_status = validated (sim≥0.20) | review (0<sim<0.20) | unmatched (aucun match)
|
|
-- latitude/longitude RAFFINÉES seulement si validated ; address_line/city/postal_code INCHANGÉS (origine préservée)
|
|
-- Idempotent : ne traite que les lignes encore 'pending' (re-run sûr). Les 2 tables sont colocalisées
|
|
-- dans la db ERPNext _eb65bdc0c4b1b2d6 → tout se fait en SQL (pas d'aller-retour applicatif).
|
|
\timing on
|
|
BEGIN;
|
|
|
|
-- Passe 1 : CODE POSTAL + NUMÉRO (forte) — couvre ~97 %.
|
|
UPDATE "tabService Location" sl SET
|
|
aq_address_id = m.id,
|
|
linked_address = m.address_full,
|
|
latitude = CASE WHEN m.sim >= 0.20 THEN m.lat ELSE sl.latitude END,
|
|
longitude = CASE WHEN m.sim >= 0.20 THEN m.lon ELSE sl.longitude END,
|
|
address_validation_status = CASE WHEN m.sim >= 0.20 THEN 'validated' ELSE 'review' END,
|
|
address_validated_at = NOW(),
|
|
modified = NOW()
|
|
FROM (
|
|
SELECT s.name, m.id, m.address_full, m.lat, m.lon, m.sim
|
|
FROM "tabService Location" s
|
|
CROSS JOIN LATERAL (
|
|
SELECT a.id::text AS id, a.address_full, a.latitude AS lat, a.longitude AS lon,
|
|
similarity(a.search_text, lower(unaccent(s.address_line))) AS sim
|
|
FROM rqa_addresses a
|
|
WHERE a.code_postal = replace(upper(coalesce(s.postal_code,'')), ' ', '')
|
|
AND a.numero = (regexp_match(s.address_line, '^\s*(\d+)'))[1]
|
|
ORDER BY similarity(a.search_text, lower(unaccent(s.address_line))) DESC
|
|
LIMIT 1
|
|
) m
|
|
WHERE s.address_validation_status = 'pending'
|
|
AND s.address_line NOT IN ('', 'N/A', 'xxx')
|
|
) m
|
|
WHERE m.name = sl.name;
|
|
|
|
-- Passe 2 : reste non résolu → 'unmatched' (pas de correspondance code postal+numéro).
|
|
UPDATE "tabService Location"
|
|
SET address_validation_status = 'unmatched', address_validated_at = NOW(), modified = NOW()
|
|
WHERE address_validation_status = 'pending';
|
|
|
|
-- Rapport
|
|
SELECT address_validation_status AS statut, count(*) AS n,
|
|
count(NULLIF(aq_address_id,'')) AS avec_lien_aq
|
|
FROM "tabService Location" GROUP BY 1 ORDER BY 2 DESC;
|
|
|
|
COMMIT;
|