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 (woe_id, iso, name, language, place_type, parent_woe_id) FROM '/tmp/geoplanet_places_7.5.2.tsv' WITH DELIMITER E'\t' CSV HEADER;

COPY aliases (woe_id, name, name_type, language) FROM '/tmp/geoplanet_aliases_7.5.2.tsv' WITH DELIMITER E'\t' CSV HEADER;

COPY adjacencies (woe_id, iso, neighbour_woe_id, neighbour_iso) FROM '/tmp/geoplanet_adjacencies_7.5.2.tsv' WITH DELIMITER E'\t' CSV HEADER;