MySQL scripts to create tables structure and import
Administrative Regions: create table Copy
CREATE TABLE Admin_Regions (
ISO character varying(2) NOT NULL,
Country character varying(45) NOT NULL,
Language character varying(2) NOT NULL,
Level integer NOT NULL,
Category character varying(45) NOT NULL,
CategoryEN character varying(45) NOT NULL,
Region1Code character varying(10) NOT NULL,
Region1 character varying(85) NOT NULL,
Region2Code character varying(10),
Region2 character varying(85),
Region3Code character varying(10),
Region3 character varying(85),
Region4Code character varying(10),
Region4 character varying(85),
Latitude decimal(10, 6),
Longitude decimal(10, 6),
Elevation integer,
ISO2 character varying(10),
FIPS character varying(10),
NUTS character varying(12),
HASC character varying(12),
STAT character varying(20),
PRIMARY KEY (ISO, Language, Region1Code, Region2Code, Region3Code, Region4Code)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;
Import file: Copy
LOAD DATA INFILE '/path/Geo-XX-4-XXX.CSV' INTO TABLE Admin_Regions FIELDS TERMINATED BY ';' ESCAPED BY '\\' IGNORE 1 LINES;
Postal Codes: create table Copy
CREATE TABLE Postal_Codes (
ISO character varying(2) NOT NULL,
Country character varying(45) NOT NULL,
Language character varying(2) NOT NULL,
ID bigint(20) NOT NULL,
PostalCode character varying(10),
Region1 character varying(85),
Region2 character varying(85),
Region3 character varying(85),
Region4 character varying(85),
Locality character varying(110),
Suburb character varying(110),
Latitude decimal(10, 6),
Longitude decimal(10, 6),
Elevation integer,
TimeZone character varying(35),
UTC character varying(6),
DST character varying(6),
PRIMARY KEY (ISO, Language, ID)
)
ENGINE=MyISAM DEFAULT charSET=utf8;
Import file: Copy
LOAD DATA INFILE '/path/Geo-XX-5-XXX.CSV' INTO TABLE Postal_Codes FIELDS TERMINATED BY ';' ESCAPED BY '\\' IGNORE 1 LINES;
Streets: create table Copy
CREATE TABLE Streets (
ISO character varying(2) NOT NULL,
Country character varying(45) NOT NULL,
Language character varying(2) NOT NULL,
ID bigint(20) NOT NULL,
PostalCode character varying(10),
Region1 character varying(85),
Region2 character varying(85),
Region3 character varying(85),
Region4 character varying(85),
Locality character varying(110),
Suburb character varying(110),
Street character varying(110),
Range character varying(100),
Building character varying(85),
Latitude decimal(10, 6),
Longitude decimal(10, 6),
Elevation integer,
TimeZone character varying(35),
UTC character varying(6),
DST character varying(6),
PRIMARY KEY (ISO, Language, ID)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;
Import file: Copy
LOAD DATA INFILE '/path/Geo-XX-8-XXX.CSV' INTO TABLE Streets FIELDS TERMINATED BY ';' ESCAPED BY '\\' IGNORE 1 LINES
Boundaries: create table Copy
CREATE TABLE Boundaries (
ISO character varying(2) NOT NULL,
Layer integer NOT NULL,
Code varchar(12) NOT NULL,
Name nvarchar(85) NOT NULL,
Geometry geometry NOT NULL,
PRIMARY KEY (ISO, Layer, Code)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;
Import file: Copy
LOAD DATA INFILE '/path/Geo-XX-7-XXX.CSV' INTO TABLE Boudaries FIELDS TERMINATED BY ';' ESCAPED BY '\\' IGNORE 1 LINES
