Page 1 of 1

Patent Fractional Count at NUTS-2 (3) level

Posted: Sat Dec 12, 2020 6:57 pm
by Simone Robbiano
Hi everybody,
sorry for the question but I'm a beginner in Patstat and SQL language.

I'm conducting an empirical research that requires a panel dataset of patents in fractional count (inventor's region of residence) for EU28 NUTS-2 regions.
Some years ago I developed a simple query to do the same thing with italian NUTS-3 regions (I create the fractional count by means of another software). There is a way to modify such query (in order to have a column with inventor's NUTS-2 region of residence) or someone has some hints to create a new one?

Code: Select all

SELECT APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, 
APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, 
APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year,
APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, 
APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P
.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, 
PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, 
APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, N.nuts, N.nuts_level,
N.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, 
CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id,
CIT.npl_citn_seq_nr, CIT.citn_gener_auth
	FROM tls904_nuts N
 		 join tls206_person P on N.nuts = P.nuts
 		 join tls207_pers_appln PA on P.person_id = PA.person_id
 		 join tls201_appln APP on PA.appln_id = APP.appln_id
         join tls209_appln_ipc APIPC on APP.appln_id = APIPC.appln_id
         join tls226_person_orig PO on PA.person_id = PO.person_id
         join tls202_appln_title APT on APP.appln_id = APT.appln_id
         join tls211_pat_publn PPB on APP.appln_id = PPB.appln_id
         join tls229_appln_nace2 NA2 on APP.appln_id = NA2.appln_id
         join tls230_appln_techn_field ATF on APP.appln_id = ATF.appln_id
         join tls212_citation CIT on PPB.pat_publn_id = CIT.pat_publn_id
	WHERE N.nuts like 'IT%'
    	AND N.nuts_level=3
		AND APP.appln_filing_date >= '1978-01-01' 
		AND APP.appln_filing_date <= '1978-12-31'
GROUP BY APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, 
APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, 
APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year,
APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, 
APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P
.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, 
PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, 
APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, N.nuts, N.nuts_level,
N.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, 
CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id,
CIT.npl_citn_seq_nr, CIT.citn_gener_auth
order by appln_id

Thank you all

Best regards

Simone

Re: Patent Fractional Count at NUTS-2 level

Posted: Mon Dec 14, 2020 3:05 pm
by EPO / PATSTAT Support
Hello Simone,
NUTS codes are constructed according to a hierarchical system based on the number of digits.
For Italy you can retrieve all the NUTS codes via the query below:

Code: Select all

SELECT  nuts
      ,nuts_level
      ,nuts_label
  FROM tls904_nuts
  where nuts like 'IT%' 
  order by nuts
NUTS_Italy.xlsx
(1.22 MiB) Downloaded 179 times
If you look at the (sorted) results created with the above query, you can see that under nuts ITC1 for Piemonte, you will find: ITC11:Torino, ITC12:Vercelli, ITC13: Biella, ITC14 :Verbano-Cusio-Ossola etc...
Using a SQL "LEFT" function, you can truncate all the NUTS codes to 4 digits, so that an address that had ITC11:Torino will now be shown as ITC1:Torino.
If you want to replace the "Torino" by "Piemonte", then you will need to join with the tls904_nuts table using the 4-digit IT (nuts_2) codes an their respective names.
The SQL for such table could look like this:

Code: Select all

SELECT  nuts
      ,nuts_level
      ,nuts_label
  FROM tls904_nuts
  where nuts like 'IT%'and nuts_level <=2
  order by nuts
The forum contains some more posts on how to calculated weighted values on NUTS codes.

Integrating this in your query, it would become something like this: (there are other ways to obtain the same results)

Code: Select all

SELECT APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, 
 APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, 
APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, 
APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, 
APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, 
APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, 
P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, 
PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, 
APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, 
APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, left(N.nuts,4) NUTS_2, 
tls904_nuts.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant,
PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id,
CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
	FROM tls904_nuts N
 		 join tls206_person P on N.nuts = P.nuts
 		 join tls207_pers_appln PA on P.person_id = PA.person_id
 		 join tls201_appln APP on PA.appln_id = APP.appln_id
         join tls209_appln_ipc APIPC on APP.appln_id = APIPC.appln_id
         join tls226_person_orig PO on PA.person_id = PO.person_id
         join tls202_appln_title APT on APP.appln_id = APT.appln_id
         join tls211_pat_publn PPB on APP.appln_id = PPB.appln_id
         join tls229_appln_nace2 NA2 on APP.appln_id = NA2.appln_id
         join tls230_appln_techn_field ATF on APP.appln_id = ATF.appln_id
         join tls212_citation CIT on PPB.pat_publn_id = CIT.pat_publn_id
		 left join tls904_nuts  on left(N.nuts,4) = tls904_nuts.nuts and tls904_nuts.nuts like 'IT%' and tls904_nuts.nuts_level <=2 
WHERE p.person_ctry_code = 'IT' 
 	AND APP.appln_filing_date >= '1978-01-01' 
	AND APP.appln_filing_date <= '1978-12-31'
	and appln_auth = 'EP'
GROUP BY N.nuts, N.nuts_level, N.nuts_label, APP.appln_id, APP.appln_auth, 
APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, 
APP.appln_nr_original, APP.ipr_type, APP.receiving_office, 
APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date,
APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year,
APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, 
APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, 
P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level,
P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, 
PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, 
APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, 
NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, PPB.pat_publn_id, 
PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant,
PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, 
CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
,left(N.nuts,4),tls904_nuts.nuts_label
I did restrict your sample with "appln_auth = 'EP'" because only for EP applications we have good enough address data available for the assignments of the NUTS codes.

Re: Patent Fractional Count at NUTS-2 level

Posted: Tue Dec 15, 2020 6:04 pm
by Simone Robbiano
Thank you very much, the query works very well.

Best Regards

Simone

Re: Patent Fractional Count at NUTS-2 level

Posted: Fri May 07, 2021 2:12 pm
by Giuseppe Lubrano
Hi Simone,
I am working on innovation topics and I was trying to download the number of patent applications by NUTS3 for Italy. But I have not knowledge of SQL scripts. With your commmand I saw it was possible to download at that level. Could you please share the command for downloading at NUTS 3 level.

Kind regards

Giuseppe Lubrano

Re: Patent Fractional Count at NUTS-2 level

Posted: Wed May 12, 2021 10:45 am
by EPO / PATSTAT Support
Here is a (non-fractional count) example using NUTS-3 level (assigned to the patent applicant(s) on the EP-B1 publication), for EP applications that were granted between 2005 and 2019.
In the event of more then 1 applicant in different NUTS regions, each region will get "a count".
If you want this information for other countries, simply change IT in the " and tls206_person.nuts like 'IT%' " phrase to another country code.

Code: Select all

SELECT nuts.nuts ,   nuts.nuts_label, nuts.nuts_level,
COUNT(distinct(case when year(publn_date)  = 2005 then tls211_pat_publn.appln_id end)) as "2005",
COUNT(distinct(case when year(publn_date)  = 2006 then tls211_pat_publn.appln_id end)) as "2006",
COUNT(distinct(case when year(publn_date)  = 2007 then tls211_pat_publn.appln_id end)) as "2007",
COUNT(distinct(case when year(publn_date)  = 2008 then tls211_pat_publn.appln_id end)) as "2008",
COUNT(distinct(case when year(publn_date)  = 2009 then tls211_pat_publn.appln_id end)) as "2009",
COUNT(distinct(case when year(publn_date)  = 2010 then tls211_pat_publn.appln_id end)) as "2010",
COUNT(distinct(case when year(publn_date)  = 2011 then tls211_pat_publn.appln_id end)) as "2011",
COUNT(distinct(case when year(publn_date)  = 2012 then tls211_pat_publn.appln_id end)) as "2012",
COUNT(distinct(case when year(publn_date)  = 2013 then tls211_pat_publn.appln_id end)) as "2013",
COUNT(distinct(case when year(publn_date)  = 2014 then tls211_pat_publn.appln_id end)) as "2014",
COUNT(distinct(case when year(publn_date)  = 2015 then tls211_pat_publn.appln_id end)) as "2015",
COUNT(distinct(case when year(publn_date)  = 2016 then tls211_pat_publn.appln_id end)) as "2016",
COUNT(distinct(case when year(publn_date)  = 2017 then tls211_pat_publn.appln_id end)) as "2017",
COUNT(distinct(case when year(publn_date)  = 2018 then tls211_pat_publn.appln_id end)) as "2018",
COUNT(distinct(case when year(publn_date)  = 2019 then tls211_pat_publn.appln_id end)) as "2019"
FROM tls211_pat_publn join tls227_pers_publn on tls211_pat_publn.pat_publn_id = tls227_pers_publn.pat_publn_id
  join tls206_person  on tls206_person.person_id = tls227_pers_publn.person_id
  join (select nuts, nuts_label, nuts_level from tls904_nuts) nuts on nuts.nuts = tls206_person.nuts
    where publn_auth = 'ep'  and publn_kind = 'B1'
	and applt_seq_nr > 0 
	and year(publn_date)  between 2005 and 2019
	and tls206_person.nuts like 'IT%'
group by  nuts.nuts ,   nuts.nuts_label,nuts.nuts_level
order by   nuts.nuts