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.
How to retrieve applicant's address
-
- Posts: 176
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
Re: How to retrieve applicant's address
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.
Best regards,
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
-
- Posts: 18
- Joined: Thu Aug 27, 2015 12:43 pm
Re: How to retrieve applicant's address
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.
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.
TU Dortmund
-
- Posts: 176
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
Re: How to retrieve applicant's address
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: (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
Best regards,
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
-
- Posts: 18
- Joined: Thu Aug 27, 2015 12:43 pm
Re: How to retrieve applicant's address
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.
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.
TU Dortmund
-
- Posts: 140
- Joined: Wed Jul 08, 2009 5:51 pm
- Contact:
Re: How to retrieve applicant's address
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.
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.
________________________________________
Nico Doranov
Data Manager
Daigu Academic Services & Data Stewardship
http://www.daigu.nl/
Nico Doranov
Data Manager
Daigu Academic Services & Data Stewardship
http://www.daigu.nl/