Oracle scripts to create tables structure and import
Administrative Regions: create table Copy
CREATE TABLE Admin_Regions (
ISO varchar(2) NOT NULL,
Country nvarchar(45) NOT NULL,
Language varchar(2) NOT NULL,
Level integer NOT NULL,
Category varchar(45) NOT NULL,
CategoryEN varchar(45) NOT NULL,
Region1Code varchar(10) NOT NULL,
Region1 varchar(85) NOT NULL,
Region2Code varchar(10),
Region2 varchar(85),
Region3Code varchar(10),
Region3 varchar(85),
Region4Code varchar(10),
Region4 varchar(85),
Latitude number(10, 6),
Longitude number(10, 6),
Elevation integer,
ISO2 varchar(10),
FIPS varchar(10),
NUTS varchar(12),
HASC varchar(12),
STAT varchar(20),
CONSTRAINT PK_Admin_Regions PRIMARY KEY (ISO, Language, Region1Code, Region2Code, Region3Code, Region4Code)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY xtern_data_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
)
LOCATION ('/path/Geo-XX-4-XXX.csv')
);
Postal Codes: create table Copy
CREATE TABLE Postal_Codes (
ISO varchar(2) NOT NULL,
Country nvarchar(45) NOT NULL,
Language varchar(2) NOT NULL,
ID INTEGER NOT NULL,
PostalCode varchar(10) NOT NULL,
Region1 varchar(85) NOT NULL,
Region2 varchar(85),
Region3 varchar(85),
Region4 varchar(85),
Locality varchar(110) NOT NULL,
Suburb varchar(110),
Latitude number(10, 6),
Longitude number(10, 6),
Elevation integer,
TimeZone varchar(35),
UTC varchar(6),
DST varchar(6),
CONSTRAINT PK_Postal_Codes PRIMARY KEY (ISO, Language, ID)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY xtern_data_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
) LOCATION ('/path/Geo-XX-5-XXX.csv')
);
Boundaries: create table Copy
CREATE TABLE Boundaries(
ISO varchar(2) NOT NULL,
Layer int NOT NULL,
Code varchar(12) NOT NULL,
Name varchar(85) NOT NULL,
Geometry SDO_GEOMETRY NOT NULL,
CONSTRAINT PK_Boundaries PRIMARY KEY
(ISO ASC, Layer ASC, Code ASC)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY xtern_data_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';')
LOCATION ('/path/Geo-XX-7-XXX.csv')
);
Streets: create table Copy
CREATE TABLE Streets (
ISO varchar(2) NOT NULL,
Country nvarchar(45) NOT NULL,
Language varchar(2) NOT NULL,
ID INTEGER NOT NULL,
PostalCode varchar(10),
Region1 varchar(85),
Region2 varchar(85),
Region3 varchar(85),
Region4 varchar(85),
Locality varchar(110),
Suburb varchar(110),
Street varchar(110),
Range varchar(100),
Building varchar(110),
Latitude number(10, 6),
Longitude number(10, 6),
Elevation integer,
TimeZone varchar(35),
UTC varchar(6),
DST varchar(6),
CONSTRAINT PK_Streets PRIMARY KEY (ISO, Language, ID)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY xtern_data_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';')
LOCATION ('/path/Geo-XX-8-XXX.csv')
);
