|
1
|
CREATE TEMP TABLE grid_institutes (
|
|
2
|
grid_id text,
|
|
3
|
name text,
|
|
4
|
wikipedia_url text,
|
|
5
|
email_address text,
|
|
6
|
established int
|
|
7
|
);
|
|
8
|
|
|
9
|
CREATE TEMP TABLE grid_geonames (
|
|
10
|
geonames_city_id text,
|
|
11
|
city text,
|
|
12
|
nuts_level1_code text,
|
|
13
|
nuts_level1_name text,
|
|
14
|
nuts_level2_code text,
|
|
15
|
nuts_level2_name text,
|
|
16
|
nuts_level3_code text,
|
|
17
|
nuts_level3_name text,
|
|
18
|
geonames_admin1_code text,
|
|
19
|
geonames_admin1_name text,
|
|
20
|
geonames_admin1_ascii_name text,
|
|
21
|
geonames_admin2_code text,
|
|
22
|
geonames_admin2_name text,
|
|
23
|
geonames_admin2_ascii_name text
|
|
24
|
);
|
|
25
|
|
|
26
|
CREATE TEMP TABLE grid_addresses (
|
|
27
|
grid_id text,
|
|
28
|
line_1 text,
|
|
29
|
line_2 text,
|
|
30
|
line_3 text,
|
|
31
|
lat double precision,
|
|
32
|
lng double precision,
|
|
33
|
postcode text,
|
|
34
|
is_primary boolean,
|
|
35
|
city text,
|
|
36
|
state text,
|
|
37
|
state_code text,
|
|
38
|
country text,
|
|
39
|
country_code text,
|
|
40
|
geonames_city_id int
|
|
41
|
);
|
|
42
|
|
|
43
|
CREATE TEMP TABLE grid_external_ids (
|
|
44
|
grid_id text,
|
|
45
|
external_id_type text,
|
|
46
|
external_id text
|
|
47
|
);
|
|
48
|
|
|
49
|
CREATE TEMP TABLE grid_labels (
|
|
50
|
grid_id text,
|
|
51
|
iso639 text,
|
|
52
|
label text
|
|
53
|
);
|
|
54
|
|
|
55
|
CREATE TEMP TABLE grid_relationships (
|
|
56
|
grid_id text,
|
|
57
|
relationship_type text,
|
|
58
|
related_grid_id text
|
|
59
|
);
|
|
60
|
|
|
61
|
CREATE TEMP TABLE grid_types (
|
|
62
|
grid_id text,
|
|
63
|
type text
|
|
64
|
);
|
|
65
|
|
|
66
|
CREATE TEMP TABLE grid_links (
|
|
67
|
grid_id text,
|
|
68
|
link text
|
|
69
|
);
|
|
70
|
|
|
71
|
CREATE TEMP TABLE grid_acronyms (
|
|
72
|
grid_id text,
|
|
73
|
acronym text
|
|
74
|
);
|
|
75
|
|
|
76
|
CREATE TEMP TABLE grid_aliases (
|
|
77
|
grid_id text,
|
|
78
|
alias text
|
|
79
|
);
|
|
80
|
|
|
81
|
COPY grid_institutes (grid_id,name,wikipedia_url,email_address,established) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/institutes.csv' CSV HEADER;
|
|
82
|
COPY grid_geonames (geonames_city_id,city,nuts_level1_code,nuts_level1_name,nuts_level2_code,nuts_level2_name,nuts_level3_code,nuts_level3_name,geonames_admin1_code,geonames_admin1_name,geonames_admin1_ascii_name,geonames_admin2_code,geonames_admin2_name,geonames_admin2_ascii_name) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/geonames.csv' CSV HEADER;
|
|
83
|
COPY grid_addresses (grid_id,line_1,line_2,line_3,lat,lng,postcode,is_primary,city,state,state_code,country,country_code,geonames_city_id) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/addresses.csv' CSV HEADER;
|
|
84
|
COPY grid_external_ids (grid_id,external_id_type,external_id) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/external_ids.csv' CSV HEADER;
|
|
85
|
COPY grid_labels (grid_id,iso639,label) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/labels.csv' CSV HEADER;
|
|
86
|
COPY grid_relationships (grid_id,relationship_type,related_grid_id) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/relationships.csv' CSV HEADER;
|
|
87
|
COPY grid_types (grid_id,type) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/types.csv' CSV HEADER;
|
|
88
|
COPY grid_links (grid_id,link) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/links.csv' CSV HEADER;
|
|
89
|
COPY grid_acronyms (grid_id,acronym) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/acronyms.csv' CSV HEADER;
|
|
90
|
COPY grid_aliases (grid_id,alias) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/aliases.csv' CSV HEADER;
|
|
91
|
|
|
92
|
|
|
93
|
INSERT INTO organizations(id, name, lat, lng, city, country, source) (SELECT 'openaire____::'||md5(o.grid_id), o.name, a.lat, a.lng, a.city, a.country_code, 'grid.ac' FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id)) ON CONFLICT DO NOTHING;
|
|
94
|
INSERT INTO other_ids (id, otherid, type) (SELECT 'openaire____::'||md5(grid_id), grid_id, 'grid.ac' FROM grid_institutes ) ON CONFLICT DO NOTHING;
|
|
95
|
INSERT INTO other_ids (id, otherid, type) (SELECT 'openaire____::'||md5(grid_id), external_id, external_id_type FROM grid_external_ids ) ON CONFLICT DO NOTHING;
|
|
96
|
INSERT INTO other_names (id, lang, name) (SELECT 'openaire____::'||md5(grid_id), 'en', name FROM grid_institutes ) ON CONFLICT DO NOTHING;
|
|
97
|
INSERT INTO other_names (id, lang, name) (SELECT 'openaire____::'||md5(grid_id), iso639, label FROM grid_labels ) ON CONFLICT DO NOTHING;
|
|
98
|
INSERT INTO other_names (id, lang, name) (SELECT 'openaire____::'||md5(grid_id), '', alias FROM grid_aliases ) ON CONFLICT DO NOTHING;
|
|
99
|
INSERT INTO acronyms (id, acronym) (SELECT 'openaire____::'||md5(grid_id), acronym FROM grid_acronyms ) ON CONFLICT DO NOTHING;
|
|
100
|
INSERT INTO relationships(id1, reltype, id2) (SELECT 'openaire____::'||md5(grid_id), relationship_type, 'openaire____::'||md5(related_grid_id) FROM grid_relationships) ON CONFLICT DO NOTHING;
|
|
101
|
INSERT INTO types (id, type) (SELECT 'openaire____::'||md5(grid_id), type FROM grid_types ) ON CONFLICT DO NOTHING;
|
|
102
|
INSERT INTO urls (id, url) (SELECT 'openaire____::'||md5(grid_id), link FROM grid_links ) ON CONFLICT DO NOTHING;
|
|
103
|
|