Scripts SQLite para criar estrutura e importação de tabelas
Regiões Administrativas: criar tabela Copy
CREATE TABLE Admin_Regions (
ISO CHAR(2) NOT NULL,
Country CHAR(45) NOT NULL,
Language CHAR(2) NOT NULL,
Level INTEGER NOT NULL,
Category CHAR(45) NOT NULL,
CategoryEN CHAR(45) NOT NULL,
Region1Code CHAR(10) NOT NULL,
Region1 CHAR(85) NOT NULL,
Region2Code CHAR(10),
Region2 CHAR(85),
Region3Code CHAR(10),
Region3 CHAR(85),
Region4Code CHAR(10),
Region4 CHAR(85),
Latitude NUMERIC,
Longitude NUMERIC,
Elevation INTEGER,
ISO2 CHAR(10),
FIPS CHAR(10),
NUTS CHAR(12),
HASC CHAR(12),
STAT CHAR(20),
PRIMARY KEY (ISO, Language, Region1Code, Region2Code, Region3Code, Region4Code)
);
Importar arquivo: Copy
LOAD DATA INLINE '/path/Geo-XX-4-XXX.csv' INTO TABLE Admin_Regions FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Códigos postais: criar tabela Copy
CREATE TABLE Postal_Codes (
ISO CHAR(2) NOT NULL,
Country CHAR(45) NOT NULL,
Language CHAR(2) NOT NULL,
ID INTEGER NOT NULL,
PostalCode CHAR(10),
Region1 CHAR(85),
Region2 CHAR(85),
Region3 CHAR(85),
Region4 CHAR(85),
Locality CHAR(110),
Suburb CHAR(110),
Latitude NUMERIC,
Longitude NUMERIC,
Elevation INTEGER,
TimeZone CHAR(35),
UTC CHAR(6),
DST CHAR(6),
PRIMARY KEY (ISO, Language, ID)
);
Importar arquivo: Copy
LOAD DATA INLINE '/path/Geo-XX-5-XXX.csv' INTO TABLE Postal_Codes FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Ruas: criar tabela Copy
CREATE TABLE Streets (
ISO CHAR(2) NOT NULL,
Country CHAR(45) NOT NULL,
Language CHAR(2) NOT NULL,
ID INTEGER NOT NULL,
PostalCode CHAR(10),
Region1 CHAR(85),
Region2 CHAR(85),
Region3 CHAR(85),
Region4 CHAR(85),
Locality CHAR(110),
Suburb CHAR(110),
Street CHAR(110),
Range varchar(100),
Building CHAR(110),
Latitude NUMERIC,
Longitude NUMERIC,
Elevation INTEGER,
TimeZone CHAR(35),
UTC CHAR(6),
DST CHAR(6),
PRIMARY KEY ( ISO, Language, ID )
);
Importar arquivo: Copy
LOAD DATA INLINE '/path/Geo-XX-8-XXX.csv' INTO TABLE Streets FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
