Tuesday, June 27, 2017

Splitting huge dump files

When importing a very big sql dump, it is possible that some configurations do not allow a correct upload in one's db (ie myisam vs innodb) or you may need to import only part of the tables.
If the dump is big enough to make any edit impossible, you may need to split it.

I found a very handy tool SQLdumpsplitter.


It has a very simple inteface and it allows to chose the size of split files and if to skip comment lines;
it also creates a separate file for the create tbales statements.
Then you can reassemble the parts you need with a dos line:
copy *.sql new.sql


Wednesday, May 31, 2017

NUTS coverage in PATSTAT 2017 spring ediction

Since 2016 autumn TLS206 has been enriched with NUTS codes for both applicants and inventors.

Source database has been made available by ECOOM (K.U. LEUVEN), and it contains two fields: NUTS field: 5 ASCII characters or empty 5 characters, and NUTS_LEVEL where f.i. = 3: indicates a NUTS 3 level, Example: "DE211" for Ingolstadt; empty: no region has been allocated.

The regionalisation procedure is performed on all person addresses of EP patents available in the PATSTAT 2016 Spring Edition, and adopts the new NUTS 2013 codes for 33 countries (EU28 plus Iceland, Liechtenstein, Norway, Switzerland and Turkey).
See table TLS904_NUTS for details about the NUTS codes.

 PATSTAT documents do not give an idea of the coverage so I add below a table I calculated for the coverage of nutsification for the 33 countries, across all application authorites in PATSTAT.




# persons in EU33
nuts %
EC
2
100,00%
EE
19623
0,00%
EG
3754
6,42%
EP
6527012
73,11%
ES
1058477
0,43%
FI
486171
0,44%
FR
1631170
0,29%
GA
1
0,00%
GB
1028051
0,68%
GC
285
0,00%
GE
952
0,32%
GR
60309
1,77%
GT
2772
0,00%
HK
53649
1,68%
HN
2435
0,16%
HR
49306
0,29%
HU
160780
0,33%
IB
270418
7,78%
ID
20450
1,26%
IE
52940
2,14%
IL
46955
4,42%
IN
38396
1,45%
IS
15805
0,08%
IT
542505
0,50%
JO
4199
0,26%
JP
36991
0,55%
KE
1520
5,26%
KR
550299
1,67%
KZ
7
0,00%
LT
12772
0,38%
LU
24806
0,38%
LV
17773
0,39%
MA
10668
1,42%
MC
2213
0,54%
MD
2487
0,04%
ME
1754
0,06%
MN
34
0,00%
MT
304
2,63%
MW
147
0,68%
MX
169803
0,95%
MY
46413
0,77%
NI
311
0,32%
NL
158776
0,12%
NO
317280
0,49%
NZ
5286
0,38%
OA
7175
0,95%
PA
805
0,37%
PE
37871
0,29%
PH
16192
1,02%
PL
154083
0,63%
PT
220448
1,03%
RO
167006
0,04%
RS
11655
0,06%
RU
237865
0,06%
SE
397630
0,34%
SG
63644
0,16%
SI
98298
0,28%
SK
74705
0,59%
SM
4115
0,05%
SU
55209
0,47%
SV
3196
0,06%
TH
28
0,00%
TJ
172
0,00%
TN
12526
0,01%
TR
115201
0,70%
TT
14
0,00%
TW
297992
2,52%
UA
35781
0,44%
US
4872625
0,15%
UY
27645
0,12%
VN
128
1,56%
YU
57437
0,51%
ZA
27946
0,79%
ZM
473
0,21%
ZW
1062
2,64%