1 |
56326
|
michele.ar
|
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 |
56332
|
michele.ar
|
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 |
56326
|
michele.ar
|
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 |
56332
|
michele.ar
|
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 |
56326
|
michele.ar
|
|
92 |
|
|
|
93 |
57305
|
michele.ar
|
INSERT INTO organizations(id, name, type, lat, lng, city, country, created_by, modified_by) (SELECT 'tmp::'||md5(o.grid_id), o.name, COALESCE(t.type, 'UNKNOWN'), a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac' FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id) LEFT OUTER JOIN grid_types t ON (o.grid_id=t.grid_id)) ON CONFLICT DO NOTHING;
|
94 |
57304
|
michele.ar
|
INSERT INTO other_ids (id, otherid, type) (SELECT 'tmp::'||md5(grid_id), grid_id, 'grid.ac' FROM grid_institutes ) ON CONFLICT DO NOTHING;
|
95 |
|
|
INSERT INTO other_ids (id, otherid, type) (SELECT 'tmp::'||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 'tmp::'||md5(grid_id), 'en', name FROM grid_institutes ) ON CONFLICT DO NOTHING;
|
97 |
|
|
INSERT INTO other_names (id, lang, name) (SELECT 'tmp::'||md5(grid_id), iso639, label FROM grid_labels ) ON CONFLICT DO NOTHING;
|
98 |
|
|
INSERT INTO other_names (id, lang, name) (SELECT 'tmp::'||md5(grid_id), 'UNKNOWN', alias FROM grid_aliases ) ON CONFLICT DO NOTHING;
|
99 |
|
|
INSERT INTO acronyms (id, acronym) (SELECT 'tmp::'||md5(grid_id), acronym FROM grid_acronyms ) ON CONFLICT DO NOTHING;
|
100 |
|
|
INSERT INTO relationships(id1, reltype, id2) (SELECT 'tmp::'||md5(grid_id), relationship_type, 'tmp::'||md5(related_grid_id) FROM grid_relationships) ON CONFLICT DO NOTHING;
|
101 |
|
|
INSERT INTO urls (id, url) (SELECT 'tmp::'||md5(grid_id), link FROM grid_links ) ON CONFLICT DO NOTHING;
|
102 |
56326
|
michele.ar
|
|
103 |
57304
|
michele.ar
|
update organizations set id = DEFAULT;
|