A CSV of UK postal out codes.
Please feel free to add contributions and corrections to the CSV. Use a text editor as office applications and suites may add unnecessary formatting.
Table creation.
CREATE TABLE outcodes (
id INT NOT NULL AUTO_INCREMENT,
postcode VARCHAR(5) NOT NULL,
eastings INT(7) NOT NULL,
northings INT(7) NOT NULL,
latitude DECIMAL(10, 8) NOT NULL,
longitude DECIMAL(11, 8) NOT NULL,
town VARCHAR(255) NULL,
region VARCHAR(255) NULL,
country VARCHAR(3) NULL,
country_string VARCHAR(255) NULL,
PRIMARY KEY(id)
);Import CSV file to table.
mysql -u root -p --local-infile=1LOAD DATA LOCAL INFILE '/path/to/postcodes.csv'
INTO TABLE databasename.outcodes
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
@postcode, @eastings, @northings, @latitude, @longitude, @town, @region,
@country, @country_string
)
SET
postcode=@postcode, eastings=@eastings, northings=@northings,
latitude=@latitude, longitude=@longitude, town=@town, region=@region,
country=@country, country_string=@country_string
;Table creation.
CREATE TABLE outcodes (
postcode VARCHAR(5) NOT NULL,
eastings INT(7) NOT NULL,
northings INT(7) NOT NULL,
latitude DECIMAL(10, 8) NOT NULL,
longitude DECIMAL(11, 8) NOT NULL,
town VARCHAR(255) NULL,
region VARCHAR(255) NULL,
country VARCHAR(3) NULL,
country_string VARCHAR(255) NULL,
);Import CSV file to table. Navigate to the directory where your postcodes.csv is stored.
psql [your_database_name]\copy outcodes FROM 'postcodes.csv' WITH HEADER CSVAdd an additional 'id' column to make corrections and/or updates easier.
ALTER TABLE outcodes ADD id serial NOT NULL PRIMARY KEY;mongoimport -d [your_database_name] -c [collection_name] --type csv --file postcodes.csv --headerline