Finland's places dedupe tiebreaks on font size, demoting 36 clusters a tier #93

New issue

Closed

opened 2026-08-09 16:08:55 +00:00 by art-bot · 1 comment

art-bot commented

2026-08-09 16:08:55 +00:00

Collaborator

#80 (PR #90) added a spatial dedupe to Finland's places query: cluster by normalized name, then
DISTINCT ON (name, cl) to pick one row per cluster. The pick is ordered

ORDER BY name, cl, (kielikoodi = 'fin') DESC, kirjasinkoko DESC NULLS LAST

— i.e. it tiebreaks on font size, and importance is then read off whichever row won. But the
tiers' font ranges overlap:

SELECT kohdeluokka, count(*), min(kirjasinkoko), max(kirjasinkoko)
FROM fi_paikannimi WHERE kohdeluokka IN (48111,48112,48120,48130) GROUP BY 1;
--  48111 |    346 | 330 | 570
--  48112 |    462 | 330 | 505
--  48120 |  54772 | 200 | 375
--  48130 | 338890 | 160 | 220

48130 reaches 220 where 48120 starts at 200, so a hamlet row can outrank the village row in its
own cluster and the cluster imports one tier lower than the register says.

Scope

36 clusters, all of them 2 → 1 (village demoted to hamlet). No importance-3 city is affected —
the 48111/48112 floor of 330 sits above 48120's ceiling of 375 only partially, but empirically no
top-tier cluster loses. Sample: Åkö, Annala, Fröjdö, Haikari, Hietaranta, Isomäki, Kurkimäki,
Liuksiala — all minor places, which is why this did not hold up the #80 merge.

Reproduce by rebuilding the inner cluster step and comparing the chosen row's tier against
max(importance) over the cluster.

What to do

Order the tiebreak on the importance tier before the font size, so the cluster is represented by
its highest tier and kirjasinkoko only breaks ties within a tier:

ORDER BY name, cl,
         (kielikoodi = 'fin') DESC,
         (CASE WHEN kohdeluokka IN (48111,48112) THEN 3
               WHEN kohdeluokka = 48120 THEN 2 ELSE 1 END) DESC,
         kirjasinkoko DESC NULLS LAST

The importance CASE is already written twice in that query; worth hoisting it into the innermost
subquery so the ordering and the emitted column cannot drift apart.

Acceptance

Zero clusters where the selected row's tier is below the cluster's maximum. Row count unchanged
(267 812) — this changes which row represents a cluster, not how many clusters there are. The z5
top-4 and the per-zoom rank <= N budgets should be unaffected; confirm the R85 query still
returns the same order.

Note

Language preference stays ahead of the tier deliberately: that is the R92 collapse, and demoting
it below the tier would let a Swedish top-tier row beat the Finnish name for the same place.

Found reviewing PR #90.

#80 (PR #90) added a spatial dedupe to Finland's `places` query: cluster by normalized name, then `DISTINCT ON (name, cl)` to pick one row per cluster. The pick is ordered ```sql ORDER BY name, cl, (kielikoodi = 'fin') DESC, kirjasinkoko DESC NULLS LAST ``` — i.e. it tiebreaks on font size, and `importance` is then read off whichever row won. But the tiers' font ranges overlap: ```sql SELECT kohdeluokka, count(*), min(kirjasinkoko), max(kirjasinkoko) FROM fi_paikannimi WHERE kohdeluokka IN (48111,48112,48120,48130) GROUP BY 1; -- 48111 | 346 | 330 | 570 -- 48112 | 462 | 330 | 505 -- 48120 | 54772 | 200 | 375 -- 48130 | 338890 | 160 | 220 ``` 48130 reaches 220 where 48120 starts at 200, so a hamlet row can outrank the village row in its own cluster and the cluster imports one tier lower than the register says. ## Scope 36 clusters, all of them 2 → 1 (village demoted to hamlet). No importance-3 city is affected — the 48111/48112 floor of 330 sits above 48120's ceiling of 375 only partially, but empirically no top-tier cluster loses. Sample: Åkö, Annala, Fröjdö, Haikari, Hietaranta, Isomäki, Kurkimäki, Liuksiala — all minor places, which is why this did not hold up the #80 merge. Reproduce by rebuilding the inner cluster step and comparing the chosen row's tier against `max(importance)` over the cluster. ## What to do Order the tiebreak on the importance tier before the font size, so the cluster is represented by its highest tier and `kirjasinkoko` only breaks ties within a tier: ```sql ORDER BY name, cl, (kielikoodi = 'fin') DESC, (CASE WHEN kohdeluokka IN (48111,48112) THEN 3 WHEN kohdeluokka = 48120 THEN 2 ELSE 1 END) DESC, kirjasinkoko DESC NULLS LAST ``` The importance CASE is already written twice in that query; worth hoisting it into the innermost subquery so the ordering and the emitted column cannot drift apart. ## Acceptance Zero clusters where the selected row's tier is below the cluster's maximum. Row count unchanged (267 812) — this changes which row represents a cluster, not how many clusters there are. The z5 top-4 and the per-zoom `rank <= N` budgets should be unaffected; confirm the R85 query still returns the same order. ## Note Language preference stays ahead of the tier deliberately: that is the R92 collapse, and demoting it below the tier would let a Swedish top-tier row beat the Finnish name for the same place. *Found reviewing PR #90.*

art-bot self-assigned this

2026-08-09 16:08:55 +00:00