Engineering notes · Open data
Forty years of Spanish trademarks, six broken files, and one endpoint that gave up
Spain's commercial registry tells you what a company is called. It does not tell
you what it is called *on the invoice*. A restaurant group filed as BARROCO MV
SOCIEDAD LIMITADA trades as DON PABLO; the deed and the signage share nothing.
For anyone matching a customer, a supplier or a lead to a legal entity, that gap
is the whole problem.
The patent office publishes the bridge. Every Spanish trademark and *nombre
comercial* since 1987 is available in bulk, free, as WIPO ST.66 XML, and the
nombre comercial — the trading name — is precisely the edge the registry
lacks. We just finished loading all of it: 1,335,777 rights, linked to
182,728 companies.
Almost none of the work was parsing XML.
The archives do not finish
Each year is one ZIP. Thirty-three of the thirty-nine arrived intact. Six did not — and they were the six heaviest, 2016 through 2021:
2016 756 MB truncated
2017 771 MB truncated
2018 441 MB truncated
2019 278 MB truncated
2020 347 MB truncated
2021 468 MB truncatedSize alone does not explain it: 2022 weighs 850 MB and arrives whole every time.
Nothing errors. The download completes, the file is *there*, and unzip says:
End-of-central-directory signature not found.A ZIP keeps its index at the end. Lose the tail, lose the index, and every
standard tool refuses the file whole — including the parts that arrived
perfectly. Python's zipfile raises BadZipFile and hands you nothing.
But a ZIP is also a stream. Each member carries its own local header, and a deflate stream announces where it ends:
i = blob.find(b"PK\x03\x04", off)
n, m = struct.unpack("<HH", blob[i + 26:i + 30])
name = blob[i + 30:i + 30 + n].decode("latin-1")
start = i + 30 + n + m
d = zlib.decompressobj(-15)
data = d.decompress(blob[start:])
off = len(blob) - len(d.unused_data) # where the next header beginsThat recovered 282,464 records from one file the tooling called garbage.
Every retry cuts somewhere else
Here is the part that turned a workaround into a strategy. Re-download a truncated year and it truncates again — but not in the same place. 2019 came down at 278 MB, then 436 MB, then 836 MB, then finally whole at 857 MB. So each attempt carries a different subset of days.
Which makes the loader's shape the important decision: upsert, never insert. Every pass fills the gaps of the last, and repeated passes converge:
2019 rights after pass 1: 37,930
after pass 2: 49,986 (+12,056)
after pass 3: 62,226 (+12,240)
after pass 4: 62,238 (+12)The rule that falls out: re-download while the row count still grows. On the fourth pass 2019 added twelve rows out of sixty-two thousand, and the year was done. Had we stopped at the first clean-looking result, that year would sit in the database missing forty per cent of itself, and nothing about the file would have said so.
The names stop shipping on 1 October 2023
Until then, each mark ships with APPLICANT-*.xml beside it: name, address,
city, province, all inside the archive. From 2 October 2023 the archives carry
DATA-*.xml alone, and the holder lives behind one HTTP request per applicant.
We found the boundary by walking a year day by day:
10/20231001.zip APPLICANT present
10/20231002.zip DATA onlyThat single date decides what a full backfill costs. Everything before it —
about nine tenths of the corpus — is downloads and CPU. Everything after it is
one request per holder, and the key is scoped to the filing (M4398660-001 is
the first applicant *of that application*), so a company that files twenty marks
costs twenty requests. There is nothing to cache.
Then the endpoint stopped answering
We dereferenced a year's worth — 202,748 requests over about two and a half hours — and it worked fine. The next morning, the same endpoint:
M4398617-001 HTTP 200 3.8s
N0503712-001 HTTP 503 7.2s
M4379229-001 HTTP 503 7.1s
M3625606-003 HTTP 200 7.0s
M2365972-001 HTTP 404 7.3s ← this key returned data the day before
M4398660-001 HTTP 200 6.1sA third refusing, one false 404, everything six seconds slow. We dropped from ten workers to three; seven minutes of running wrote zero rows. That is the tell that it is not your pace: backing off should show up immediately as a lower error rate, and it did not.
Whether we caused it or merely arrived during it, the answer is the same — stop, and use a different door. The office grants access to a SOAP service on request, and that one answers in two seconds and returns *more*: the province, the ownership share, whether the holder is a company or a person. It gives less of exactly one thing — co-applicants — so those rows stay empty rather than being guessed at.
Three ways the data quietly lies
Records before ~2016 declare no kind. No <IPRKind> element at all. The
modality lives in the file number instead: M is a trademark, N a trading
name, R a shop sign. Miss that and every trading name in the old years — the
part actually worth having — silently becomes an unclassified row.
Eight files carry a year with its century dropped. 0022-02-15 for a mark
filed in 2022, 0216-02-10 for one from 2016. Guessing the century is not safe.
Publishing the typo to someone running a company check is worse. Both fields go
empty, with a floor at 1826 — the year of the first Spanish grant.
A third of trading names are just the company's own name. TEAM DENTAL LAB
held by TEAM DENTAL LAB SL. Storing those inflates the layer with links that
teach a caller nothing, because ordinary name normalisation already finds them.
We drop them — 59,051 of them — and keep the two thirds that are real:
EDICIONES PLAN B → EDICIONES LITERARIAS INDEPENDIENTES SL
TODOPARATUINDUSTRIA.COM → HIDROTECNIA DE COMPONENTES SL
FSA → FREIXENET SA
Cortijo Los Santeros → PBS Y LEGADO SOCIEDAD LIMITADAWhat it is worth
424,143 links between a brand and the company that holds it, across 182,728 companies. Sixty per cent of those companies have no tax ID in our data — they are the dormant, the small and the never-contracted, the part of a registry that is hardest to identify by any other means.
And one thing the office does not publish anywhere, in any of its channels, bulk or API: a NIF. Not in the ST.66 files, not behind the applicant endpoint, not in the bulletin, not in the web-service specification. Every link here is made on a normalised name, and every answer we serve says so. A brand is evidence about a company. It is not identity, and the difference is the product.