Crios-Patstat Database: Sources, Contents and Access Rules

Ok this is self-advertizing,
but is now available for download at http://papers.ssrn.com/sol3/papers.cfm?abstract_id=2404344
the full WP about Crios-Patstat Database: Sources, Contents and Access Rules.

 any comment, suggestions etc are welcome!

Mysql script for importing OECD regpat

OECD regpat is a set plug and play tables that set on Patstat adding a batch of useful info as NUTS code, IPC, counts etc (see for more info http://www.oecd.org/sti/inno/oecdpatentdatabases.htm)

I put here a small script for importing in mysql 2 of the gived tables: ep_app_reg and ep_inv_reg giving especially regional NUTS3 code for EP inventors and applicants.

just be careful to replace YOURDIRECTORYHERE with the folder where you put the files from regpat.



-- Import REGPAT Mysql script
-- List of EPO applicants

drop table if exists EP_APP_REG;
drop table if exists EP_INV_REG;

create table  EP_APP_REG
(App_nbr varchar(13),
Appln_id varchar(11),
Pub_nbr varchar(11), -- EPO patent publication number
Person_id varchar(11), -- Surrogate key - person identifier in PATSTAT, October 2013
App_name varchar(300), -- Applicantfs name
Address varchar(500), -- Address
Reg_code varchar(10), -- NUTS3/TL3 region code
Ctry_code varchar(2), -- ISO 2 country code
Reg_share2 varchar(7), -- Multiple allocation to a region - Share . 1
App_share3 varchar(7)) -- Applicantfs share . 1
ENGINE=MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

create table  EP_INV_REG
(App_nbr varchar(13),
Appln_id varchar(11),
Pub_nbr varchar(11), -- EPO patent publication number
Person_id varchar(11), -- Surrogate key - person identifier in PATSTAT, October 2013
Inv_name varchar(300), -- Applicantfs name
Address varchar(500), -- Address
Reg_code varchar(10), -- NUTS3/TL3 region code
Ctry_code varchar(2), -- ISO 2 country code
Reg_share2 varchar(7), -- Multiple allocation to a region - Share . 1
Inv_share3 varchar(7)) -- Applicantfs share . 1
ENGINE=MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

LOAD DATA INFILE 'YOURDIRECTORYHERE/201401_EPO_App_reg.txt' INTO TABLE EP_APP_REG
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

LOAD DATA INFILE 'patstat/raw/201401_EPO_Inv_reg.txt' INTO TABLE EP_INV_REG
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES -- fixes format data: default import = text

ALTER TABLE ep_app_reg MODIFY COLUMN Appln_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Person_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Reg_share2 DOUBLE DEFAULT NULL,
 MODIFY COLUMN App_share3 DOUBLE DEFAULT NULL;

ALTER TABLE ep_inv_reg MODIFY COLUMN Appln_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Person_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Reg_share2 DOUBLE DEFAULT NULL,
 MODIFY COLUMN Inv_share3 DOUBLE DEFAULT NULL;

matching scientific subfields/journals with NACE and FP areas

Some interesting concordance matrixes from Science Metrix, taken from the paper you may find at this link.
They provide a link among scientific subfields and nace and Framework Program priority areas; the exercise is based on scopus data.
A complete list of journals by field and subfield in Science-Metrix’ ontology of science, visit: http://www.science-metrix.com/SM_Ontology_102.xls

Table LXXIV The matching scheme between FP7 priorities and the research classification
FP7 Thematic Priorities	SM_Field	SM_SubField
1. Health	Biomedical Research	All subfields
1. Health	Clinical Medicine	All subfields
1. Health	Psychology & Cognitive Sciences	All subfields
1. Health	Public Health & Health Services	All subfields
1. Health	Engineering	Biomedical Engineering
2a. Food, Agriculture and Fisheries	Agriculture, Fisheries & Forestry	All subfields
2b. Biotechnology	Enabling & Strategic Technologies	Biotechnology
2b. Biotechnology	Enabling & Strategic Technologies	Bioinformatics
3. Information and Communication Technologies	Information & Communication Technologies	All subfields
4a.Nanosciences and Nanotechnologies	Enabling & Strategic Technologies	Nanoscience & Nanotechnology
4b. Materials (exluding nanotechnologies)	Enabling & Strategic Technologies	Materials
4b. Materials (exluding nanotechnologies)	Chemistry	Polymers
4c. New Production Technologies	Engineering	Industrial Engineering & Automation
4c. New Production Technologies	Engineering	Operations Research
4d. Construction and Construction Technologies	Built Environment & Design	All subfields
5. Energy	Enabling & Strategic Technologies	Energy
6. Environment (including Climate Change)	Earth & Environmental Sciences	All subfields
6. Environment (including Climate Change)	Engineering	Environmental Engineering
6. Environment (including Climate Change)	Biology	Ecology
7a. Aeronautics	Engineering	Aerospace & Aeronautics
7b. Automobiles	Engineering	Automobile Design & Engineering
7c. Other Transport Technologies	Economics & Business	Logistics & Transportation
7c. Other Transport Technologies	Engineering	Mechanical Engineering & Transports
7c. Other Transport Technologies	Engineering	Civil Engineering
8a. Socio-Economic Sciences	Communication & Textual Studies	Communication & Media Studies
8a. Socio-Economic Sciences	Economics & Business	All subfields except Logistics & Transportation
8a. Socio-Economic Sciences	Social Sciences	All subfields
8b. Humanities;	Historical Studies	All subfields
8b. Humanities;	Communication & Textual Studies	Languages & Linguistics
8b. Humanities;	Communication & Textual Studies	Literary Studies
8b. Humanities;	Philosophy & Theology	All subfields
8b. Humanities;	Visual & Performing Arts	All subfields
9. Space	Engineering	Aerospace & Aeronautics
10. Security.	Enabling & Strategic Technologies	Strategic, Defence & Security Studies

Table CIII List of economic sectors and their matched scientific subfields/journals
NACE codes	Matched Scientific Subfields/Journals
15 & 29.53	Food Science
15 & 29.53	Nutrition & Dietetics
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Advanced Composite Materials
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Cellulose
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Chemical Engineering Research & Design
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Dyes and Pigments
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Fibers and Polymers
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Fibre Chemistry
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Fibres & Textiles in Eastern Europe
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Indian Journal of Fibre and Textile Research
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Industria Textila
17 , 29.54, 51.42/2, 51.83 & 52.41/2	International Journal of Clothing Science and Technology
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Adhesion Science and Technology
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Applied Polymer Science
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Cotton Science
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Fashion Marketing and Management
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Industrial Textiles
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Natural Fibers
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Non-New tonian Fluid Mechanics
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Photopolymer Science and Technology
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Polymer Research
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Polymer Science Part B
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Polymers and the Environment
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Journal of Textile Institute
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Kagaku Kogaku Ronbunshu
17 , 29.54, 51.42/2, 51.83 & 52.41/2	KGK Kautschuk Gummi Kunststoffe
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Kobunshi Ronbunshu
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Macromolecular Research
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Research on Chemical Intermediates
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Sen'i Gakkaishi
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Tekstil
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Textile Chemist and Colorist and American Dyestuff Reporter
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Textile Research Journal
17 , 29.54, 51.42/2, 51.83 & 52.41/2	Wool Technology and Sheep Breeding
22.3 & 24.64/5	Applied Physics
22.3 & 24.64/5	Artificial Intelligence & Image Processing
22.3 & 24.64/5	Computer Hardw are & Architecture
22.3 & 24.64/5	Mechanical Engineering & Transports
22.3 & 24.64/5	Softw are Engineering
24.1, 24.3 & 24.62	Chemical Engineering
24.1, 24.3 & 24.62	General Chemistry
24.1, 24.3 & 24.62	Physical Chemistry
24.1, 24.3 & 24.62	Polymers
24.1, 24.3 & 24.62	Toxicology
24.4	Medicinal & Biomolecular Chemistry
24.4	Pharmacology & Pharmacy
25.2	Polymers
26	Applied Physics
26	Building & construction
26	Materials
26	Optoelectronics & Photonics
29.1, 29.2 & 29.4	Design Practice & Management
29.1, 29.2 & 29.4	Industrial Engineering & Automation
29.1, 29.2 & 29.4	Mechanical Engineering & Transports
29.3	Agronomy & Agriculture
29.3	Design Practice & Management
29.3	Electrical & Electronic Engineering
29.3	Environmental Engineering
29.3	Industrial Engineering & Automation
29.3	Mechanical Engineering & Transports
29.6	Aerospace & Aeronautics
29.6	Astronomy & Astrophysics
29.6	Geological & Geomatics Engineering
29.6	Meteorology & Atmospheric Sciences
29.6	Networking & Telecommunications
29.6	Optoelectronics & Photonics
29.7	Acoustics
29.7	Building & Construction
29.7	Electrical & Electronic Engineering
29.7	Mechanical Engineering & Transports
30	Computer Hardw are & Architecture
31.1	Electrical & Electronic Engineering
31.1	Energy
31.2, 31.3, 31.4 & 40	Civil Engineering
31.2, 31.3, 31.4 & 40	Energy
31.5	Applied Physics
31.5	Building & Construction
31.5	Electrical & Electronic Engineering
31.5	Optoelectronics & Photonics
31.61	Automobile Design & Engineering
31.61	Electrical & Electronic Engineering
31.61	Logistics & Transportation
32.1	Biomedical Engineering
32.1	Computer Hardw are & Architecture
32.2 & 32.3	Applied Physics
32.2 & 32.3	Electrical & Electronic Engineering
32.2 & 32.3	Netw orking & Telecommunications
33.1	Electrical & Electronic Engineering
33.1	Nuclear Medicine & Medical Imaging
33.2, 33.3 & 33.4	Analytical Chemistry
33.2, 33.3 & 33.4	Bioinformatics
33.2, 33.3 & 33.4	Microscopy
33.2, 33.3 & 33.4	Networking & Telecommunications
33.2, 33.3 & 33.4	Optics
33.2, 33.3 & 33.4	Optoelectronics & Photonics
34.1 & 34.3	Automobile Design & Engineering
34.1 & 34.3	Design Practice & Management
35.3	Aerospace & Aeronautics
35.3	Design Practice & Management
37	Environmental Engineering
37	Environmental Sciences
41 & 45.24	Chemical Engineering
41 & 45.24	Civil Engineering
41 & 45.24	Environmental Engineering
41 & 45.24	Environmental Sciences
45 except 45.24	Building & construction
45 except 45.24	Civil Engineering
63.1	Logistics & Transportation
63.1	Operations Research
64.2	Networking & Telecommunications
64.2	Optoelectronics & Photonics
64.2	Speech-Language Pathology & Audiology
72 except 72.5	Computation Theory & Mathematics
72 except 72.5	Computer Hardw are & Architecture
72 except 72.5	Distributed Computing
72 except 72.5	Human Factors
72 except 72.5	Information Systems
72 except 72.5	Software Engineering

Table CIV List of scientific subfields/journals and the economic sectors to which they have been matched
Matched Scientific Subfields/Journals	NACE codes
Acoustics	29.7
Advanced Composite Materials	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Aerospace & Aeronautics	29.6
Aerospace & Aeronautics	35.3
Agronomy & Agriculture	29.3
Analytical Chemistry	33.2, 33.3 & 33.4
Applied Physics	22.3 & 24.64/5
Applied Physics	26
Applied Physics	31.5
Applied Physics	32.2 & 32.3
Artificial Intelligence & Image Processing	22.3 & 24.64/5
Astronomy & Astrophysics	29.6
Automobile Design & Engineering	31.61
Automobile Design & Engineering	34.1 &34.3
Bioinformatics	33.2, 33.3 & 33.4
Biomedical Engineering	32.1
Building & construction	26
Building & Construction	29.7
Building & Construction	31.5
Building & construction	45 except 45.24
Cellulose	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Chemical Engineering	24.1, 24.3 & 24.62
Chemical Engineering	41 & 45.24
Chemical Engineering Research & Design	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Civil Engineering	31.2, 31.3, 31.4 & 40
Civil Engineering	41 & 45.24
Civil Engineering	45 except 45.24
Computation Theory & Mathematics	72 except 72.5
Computer Hardw are & Architecture	22.3 & 24.64/5
Computer Hardw are & Architecture	30
Computer Hardw are & Architecture	32.1
Computer Hardw are & Architecture	72 except 72.5
Design Practice & Management	29.1, 29.2 & 29.4
Design Practice & Management	29.3
Design Practice & Management	34.1 & 34.3
Design Practice & Management	35.3
Distributed Computing	72 except 72.5
Dyes and Pigments	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Electrical & Electronic Engineering	29.3
Electrical & Electronic Engineering	29.7
Electrical & Electronic Engineering	31.1
Electrical & Electronic Engineering	31.5
Electrical & Electronic Engineering	31.61
Electrical & Electronic Engineering	32.2 & 32.3
Electrical & Electronic Engineering	33.1
Energy	31.1
Energy	31.2, 31.3, 31.4 & 40
Environmental Engineering	29.3
Environmental Engineering	37
Environmental Engineering	41 & 45.24
Environmental Sciences	37
Environmental Sciences	41 & 45.24
Fibers and Polymers	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Fibre Chemistry	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Fibres & Textiles in Eastern Europe	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Food Science	15 & 29.53
General Chemistry	24.1, 24.3 & 24.62
Geological & Geomatics Engineering	29.6
Human Factors	72 except 72.5
Indian Journal of Fibre and Textile Research	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Industria Textila	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Industrial Engineering & Automation	29.1, 29.2 & 29.4
Industrial Engineering & Automation	29.3
Information Systems	72 except 72.5
International Journal of Clothing Science and Technology	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Adhesion Science and Technology	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Applied Polymer Science	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Cotton Science	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Fashion Marketing and Management	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Industrial Textiles	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Natural Fibers	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Non-New tonian Fluid Mechanics	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Photopolymer Science and Technology	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Polymer Research	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Polymer Science Part B	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Polymers and the Environment	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Journal of Textile Institute	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Kagaku Kogaku Ronbunshu	17 , 29.54, 51.42/2, 51.83 & 52.41/2
KGK Kautschuk Gummi Kunststoffe	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Kobunshi Ronbunshu	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Logistics & Transportation	31.61
Logistics & Transportation	63.1
Macromolecular Research	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Materials	26
Mechanical Engineering & Transports	22.3 & 24.64/5
Mechanical Engineering & Transports	29.1, 29.2 & 29.4
Mechanical Engineering & Transports	29.3
Mechanical Engineering & Transports	29.7
Medicinal & Biomolecular Chemistry	24.4
Meteorology & Atmospheric Sciences	29.6
Microscopy	33.2, 33.3 & 33.4
Networking & Telecommunications	29.6
Networking & Telecommunications	32.2 & 32.3
Networking & Telecommunications	33.2, 33.3 & 33.4
Networking & Telecommunications	64.2
Nuclear Medicine & Medical Imaging	33.1
Nutrition & Dietetics	15 & 29.53
Operations Research	63.1
Optics	33.2, 33.3 & 33.4
Optoelectronics & Photonics	26
Optoelectronics & Photonics	29.6
Optoelectronics & Photonics	31.5
Optoelectronics & Photonics	33.2, 33.3 & 33.4
Optoelectronics & Photonics	64.2
Pharmacology & Pharmacy	24.4
Physical Chemistry	24.1, 24.3 & 24.62
Polymers	24.1, 24.3 & 24.62
Polymers	25.2
Research on Chemical Intermediates	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Sen'i Gakkaishi	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Software Engineering	22.3 & 24.64/5
Software Engineering	72 except 72.5
Speech-Language Pathology & Audiology	64.2
Tekstil	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Textile Chemist and Colorist and American Dyestuff Reporter	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Textile Research Journal	17 , 29.54, 51.42/2, 51.83 & 52.41/2
Toxicology	24.1, 24.3 & 24.62
Wool Technology and Sheep Breeding	17 , 29.54, 51.42/2, 51.83 & 52.41/2