Import Yahoo GeoPlanet data into PostgreSQL.

geoplanet_import_psql.sql

-- Schema and import for Yahoo GeoPlanet data into a PostgreSQL database.

CREATE TABLE places (
    woe_id VARCHAR(15) PRIMARY KEY,
    iso VARCHAR(6),
    name TEXT,
    language VARCHAR(6),
    place_type VARCHAR(15),
    parent_woe_id VARCHAR(15)
);

CREATE TABLE aliases (
    woe_id VARCHAR(15),
    name TEXT,
    name_type VARCHAR(6),
    language VARCHAR(6),

    FOREIGN KEY (woe_id) REFERENCES places (woe_id)
);

CREATE TABLE adjacencies (
    woe_id VARCHAR(15),
    iso VARCHAR(6),
    neighbour_woe_id VARCHAR(15),
    neighbour_iso VARCHAR(6),

    FOREIGN KEY (woe_id) REFERENCES places (woe_id),
    FOREIGN KEY (neighbour_woe_id) REFERENCES places (woe_id)
);


\copy places FROM '/tmp/geoplanet_places_7.5.2.tsv' WITH DELIMITER E'\t' CSV HEADER;

\copy aliases FROM '/tmp/geoplanet_aliases_7.5.2.tsv' WITH DELIMITER E'\t' CSV HEADER;

\copy adjacencies FROM '/tmp/geoplanet_adjacencies_7.5.2.tsv' WITH DELIMITER E'\t' CSV HEADER;