how to extract patent applications with cross boarder collaboration

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Djay
Posts: 4
Joined: Tue Mar 12, 2019 8:26 am

how to extract patent applications with cross boarder collaboration

Post by Djay » Wed Mar 13, 2019 10:15 am

hi PATSTAT Team,

I'm trying to create a dataset for bilateral patent collaborations. I have the following code but its captures patents collaborations within the same country. How can I modify this code to capture only those patent applications with innovators spread across different countries.

SELECT DISTINCT tls201_appln.appln_id, person_ctry_code
FROM tls201_appln,tls206_person, tls207_pers_appln
WHERE tls201_appln.appln_id = tls207_pers_appln.appln_id
AND tls207_pers_appln.person_id = tls206_person.person_id
AND tls201_appln.granted = 'Y'
AND tls201_appln.appln_filing_year BETWEEN 2000 AND 2005
AND tls201_appln.nb_inventors >= 2
AND p1.person_ctry_code <> p2.person_ctry_code
ORDER BY tls201_appln.appln_id

Thank you very much.


EPO / PATSTAT Support
Posts: 97
Joined: Thu Feb 22, 2007 5:33 pm

Re: how to extract patent applications with cross boarder collaboration

Post by EPO / PATSTAT Support » Wed Mar 13, 2019 12:34 pm

Hello Djay,
such condition can be introduced to the query by means of a sub query via SQL EXISTS Operator.
Basically, what you want to do is to force the query to only retain those applications that have 2 inventors from different countries (at least, can be more). So we make a query that counts the number of "DISTINCT" person_ctry_code from each application. I also added the condition that the country code should not be blank. The condition "AND tls201_appln.nb_inventors >= 2" becomes unnecessary as we know that we will restrict the sample to applications that have minimum 2 country codes.

Here is a sample query that lists those application with the names:

Code: Select all

SELECT tls201_appln.appln_id, tls201_appln.appln_nr_epodoc,
invt_seq_nr,tls206_person.person_name, person_ctry_code
 FROM tls201_appln join tls207_pers_appln 
		on tls201_appln.appln_id = tls207_pers_appln.appln_id
 join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
 WHERE tls201_appln.granted = 'Y'
 AND tls201_appln.appln_filing_year BETWEEN 2000 AND 2005
 and appln_auth ='EP'
 and invt_seq_nr > 0
 AND exists
 (select ap.appln_id, count (distinct p.person_ctry_code) country
	 from tls207_pers_appln AP join tls206_person p 
		on ap.person_id = p.person_id
	 where ap.invt_seq_nr > 0 and p.person_ctry_code <> ''
	 and tls201_appln.appln_id = ap.appln_id
	 group by ap.appln_id
	 having count (distinct p.person_ctry_code)> 1)
ORDER BY tls201_appln.appln_id, invt_seq_nr
Only having the appln_id and the count would look then like this:

Code: Select all

SELECT tls201_appln.appln_id, count(distinct(person_ctry_code))
 FROM tls201_appln join tls207_pers_appln 
		on tls201_appln.appln_id = tls207_pers_appln.appln_id
 join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
 WHERE tls201_appln.granted = 'Y'
 AND tls201_appln.appln_filing_year BETWEEN 2000 AND 2005
 and appln_auth ='EP'
 and invt_seq_nr > 0
 AND exists
 (select ap.appln_id, count (distinct p.person_ctry_code) country
	 from tls207_pers_appln AP join tls206_person p 
		on ap.person_id = p.person_id
	 where ap.invt_seq_nr > 0 and p.person_ctry_code <> ''
	 and tls201_appln.appln_id = ap.appln_id
	 group by ap.appln_id
	 having count (distinct p.person_ctry_code)> 1)
group by tls201_appln.appln_id
ORDER BY tls201_appln.appln_id
Geert
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Djay
Posts: 4
Joined: Tue Mar 12, 2019 8:26 am

Re: how to extract patent applications with cross boarder collaboration

Post by Djay » Thu Mar 14, 2019 3:38 am

Dear Geert,

Thank you very much for your reply. The first code is more relevant to me since I need to identify the country codes. I need a small clarification on the 1st code you have provides. You have specified the application authority as "EP". I assume this gives me all the patents granted by the European Patent Office for the the mentioned period?

Also, I can only download 10000 rows at a time. Is it possible to download all at once?

Thank you very much for helping with the code. Really appreciate it.


EPO / PATSTAT Support
Posts: 97
Joined: Thu Feb 22, 2007 5:33 pm

Re: how to extract patent applications with cross boarder collaboration

Post by EPO / PATSTAT Support » Thu Mar 14, 2019 9:53 am

Hello Djay,
the limitations for downloads can not be changed.
But you can download data (In a CSV or MS ACCESS) from up to 100.0000 applications to begin with. (Not the PDF documents, that is limited.)
I added "EP" in order to simply reduce the data set, otherwise you will have close to 900.000 results.
Geert
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Djay
Posts: 4
Joined: Tue Mar 12, 2019 8:26 am

Re: how to extract patent applications with cross boarder collaboration

Post by Djay » Thu Mar 14, 2019 10:13 am

Thank you so much for the clarification. Appreciate your help.


Post Reply