0

GeoPlanet data - MySQL database download available

I've imported the GeoPlanet data into a MySQL database and thought it might be useful to others. I'll try and keep it up to date with new releases of the GeoPlanet data.

You can download it from here.

The file is in Gzip format and is 127M in size.

The file is a mysqldump file and it defines 4 InnoDB tables (place, place_type, places_aliases, places_neighbours)

All the best

Jon Hill

by
6 Replies
  • Thanks Jon ... what version of the data is this based on? It would also be really helpful if you could publish your table schema?

    Best

    Gary Gale
    Director of Engineering, Yahoo! Geo Technologies
    0
  • The GeoPlanet source is version 7.4.1

    I've now put the version into the filename [geoplanet_7.4.1.sql.gz] and made it available for download

    The Schema I've used is shown below and also available from here:

    CODE
    CREATE TABLE `place` (
    `woeid` bigint(20) NOT NULL,
    `country` varchar(2) NOT NULL,
    `name` varchar(255) NOT NULL,
    `language` char(3) NOT NULL,
    `place_type` bigint(20) NOT NULL,
    `parent` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`woeid`),
    KEY `parent` (`parent`),
    KEY `place_type` (`place_type`),
    KEY `country` (`country`),
    KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



    CREATE TABLE `places_aliases` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `woeid` bigint(20) NOT NULL,
    `name` varchar(255) NOT NULL,
    `name_type` char(1) NOT NULL,
    `language` char(3) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `woeid` (`woeid`,`name_type`,`language`),
    KEY `name_type` (`name_type`),
    KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



    CREATE TABLE `places_neighbours` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `place_woeid` bigint(20) NOT NULL,
    `neigbour_woeid` bigint(20) NOT NULL,
    `place_country` char(2) NOT NULL,
    `neighbour_country` char(2) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `place_woeid` (`place_woeid`,`neigbour_woeid`),
    KEY `neigbour_woeid` (`neigbour_woeid`),
    KEY `place_country` (`place_country`),
    KEY `neighbour_country` (`neighbour_country`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



    CREATE TABLE `place_type` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


    ALTER TABLE `place`
    ADD CONSTRAINT `place_ibfk_2` FOREIGN KEY (`place_type`) REFERENCES `place_type` (`id`);

    ALTER TABLE `places_aliases`
    ADD CONSTRAINT `places_aliases_ibfk_1` FOREIGN KEY (`woeid`) REFERENCES `place` (`woeid`);

    ALTER TABLE `places_neighbours`
    ADD CONSTRAINT `places_neighbours_ibfk_1` FOREIGN KEY (`place_woeid`) REFERENCES `place` (`woeid`),
    ADD CONSTRAINT `places_neighbours_ibfk_2` FOREIGN KEY (`neigbour_woeid`) REFERENCES `place` (`woeid`);


    All the best

    Jon
    0
  • Thanks Jon ... that's great.

    Gary Gale
    Director of Engineering, Yahoo! Geo Technologies
    0
  • I load the sql into my mysql server,but I find the data is more larger than you thought; and it never stop, so I guess it's that something wrong ?
    0
  • Hi

    Sorry for the delay in replying; I should check into the forum more often!

    Uncompressed, the sql file is 615M, so it will take a while to import it. I'll run another test on my machine so that there is a benchmark.

    Jon
    0
  • <br><br>QUOTE (Jon @ 24 Mar 2010 7:55 AM) Hi<br><br>Sorry for the delay in replying; I should check into the forum more often!<br><br>Uncompressed, the sql file is 615M, so it will take a while to import it. I&#39;ll run another test on my machine so that there is a benchmark.<br><br>Jon<br>No longer exists.&nbsp;
    0
This forum is locked.

Recent Posts

in GeoPlanet General Discussion