How to retrieve applicant's address

[Hustenhaft]

Dear community,

I'm working on a PATSTAT Online (Autumn 2015) patent query. My aim is to find the address of certain inventors who submitted an application in germany in a certain year.
So I went with following query:

SELECT DISTINCT TLS206_PERSON.SECTOR, TLS206_PERSON.HRM_L2, TLS206_PERSON.PERSON_ADDRESS
FROM TLS201_APPLN
LEFT JOIN TLS207_PERS_APPLN ON TLS201_APPLN.APPLN_ID = TLS207_PERS_APPLN.APPLN_ID
LEFT JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID
WHERE
UPPER (TLS201_APPLN.APPLN_AUTH) = 'DE'
AND TLS201_APPLN.EARLIEST_FILING_YEAR = 2000
AND TLS206_PERSON.SECTOR NOT LIKE 'INDIVIDUAL'
AND TLS206_PERSON.PERSON_CTRY_CODE = 'DE'
AND TLS207_PERS_APPLN.INVT_SEQ_NR > 0;

It seems to work as intended. But following problem has occurred: The attribute address (TLS206_PERSON.PERSON_ADDRESS) stays empty, even though the attribute in the table TLS206_PERSON is not. I tried different approaches, but i can not seem to solve this problem. If try to look up the address of a match found afterwards, it clearly is not empty too.

Thanks in advance.

[Geert Boedt]

Because of data protection issues, addresses have been removed from applicants and inventors that could not be identified as being companies. With other words, natural persons addresses have been removed. This is only the case for PATSTAT Online. The "raw data" distribution on DVD's from the PATSTAT distribution still has the addresses available for analysis.

[Tim Grünebaum]

Unfortunately the problem also occurs in the DVD version, I checked it.
I don't know why, but only the address column stays empty for every applicant, no matter if company or individual.
Maybe the name harmonization (hrm_l2) makes it impossible to identify one specific address.

[Geert Boedt]

new patent indicator.pdf

(238.54 KiB) Downloaded 353 times

Hello Tim,
I answered the question too quickly, simply assuming that you were using the on-line service.
Apart from the fact that we removed "natural person" addresses, only few patent authorities deliver the street address data to the EPO. (see data catalog) Your query looks for patents filed in Germany, and DPMA does not provide us with the addresses from DE national applications. The only work-around I can see is using the harmonised name to "replenish" the missing addresses, or develop a "replennishing model" based on for example family members and/or priority filings.
This document explains a possible method of doing so:

new patent indicator.pdf

(238.54 KiB) Downloaded 353 times

(Check chapter 3, similar approach could also be used for the address instead of the only the country.)

Using the "harmonised names" is making use of the fact that names have been grouped, where some have addresses and others not. The SQL query below illustrates this. You could develop your own model to decide which candidate you would retain as the "representative". A quick and dirty method would be to retain persons that contain at least a country code and the "longest" (character count) address. Other approaches I have seen is to count the number of applications linked to possible representative names and use that as an additional decision making factor.

Code: Select all

SELECT DISTINCT tls906_person.person_id,tls906_person.psn_id, TLS906_PERSON.psn_SECTOR, 
tls906_person.person_name, TLS906_PERSON.psn_name, TLS906_PERSON.PERSON_ADDRESS,
tls906_person.person_ctry_code, tls906_person.psn_id, pers2.person_name,
pers2.person_address, pers2.person_ctry_code,pers2.psn_id
FROM TLS201_APPLN
JOIN TLS207_PERS_APPLN ON TLS201_APPLN.APPLN_ID = TLS207_PERS_APPLN.APPLN_ID
JOIN TLS906_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS906_PERSON.PERSON_ID
join tls906_person pers2 on tls906_person.psn_id = pers2.psn_id
WHERE
TLS201_APPLN.APPLN_AUTH = 'de'
AND TLS201_APPLN.earliest_filing_year= 2000
AND TLS906_PERSON.psn_SECTOR <> 'INDIVIDUAL'
AND TLS906_PERSON.PERSON_CTRY_CODE = 'DE'
AND TLS207_PERS_APPLN.invt_seq_nr > 0
order by person_id

Small observation: I am not sure if the logics in the query are good; the fact that you take sector <> "individual" is a bit contrary to the fact that you are looking at inventors (who should normally always be natural persons)

[Tim Grünebaum]

Hello Geert,

thank you for your extensive answer.

The problem is somehow different:
When we look at the raw data (say at table TLS206_PERSON) we see that every applicant, no matter which sector and country code (also DE) provides an address, at least ZIP-Code and City. They are simply not displayed after using the query above.
So one may look up every address "by hand" in the table, but after using the query only the PERSON_ADDRESS column stays completely empty, whereas it is in fact not empty in the raw table, which is quite puzzling. Thus it is not a matter of data mining for the right address, the problem has to be somehow in the code I think, but it seems ok to me.

Using different harmonization stages for the name does not help either.

[nico.rasters]

SELECT tls206_person.person_id, person_address, hrm_l2
FROM tls206_person
INNER JOIN tls207_pers_appln ON tls206_person.person_id = tls207_pers_appln.person_id
INNER JOIN tls201_appln ON tls207_pers_appln.appln_id = tls201_appln.appln_id
WHERE tls207_pers_appln.applt_seq_nr >0
AND tls201_appln.appln_auth='DE'
AND tls201_appln.earliest_filing_year=2000
AND tls206_person.person_ctry_code='DE'
AND person_address <> '';
This query gives zero results. Now let's tweak some clauses...

tls207_pers_appln.applt_seq_nr = 0 gives 0 results.
tls201_appln.appln_auth <> 'DE' gives 29692 results.
tls201_appln.earliest_filing_year < 2000 gives 3414 results.
tls201_appln.earliest_filing_year > 2000 gives 0 results.
tls206_person.person_ctry_code <> 'DE' gives 0 results.

Can you provide some appln_ids for which the query says the address is empty while the raw data says it's not? As there can be several instances for a patent (in a family), you are probably finding the address of a family member while the query is returning the data for the German national patent. And those have no address just like Geert said, except for the years before 2000.