Mapping Hacks

424

|

Chapter 7, Names and Places

#84 Build a Free World Gazetteer

HACK

To store our world model, we’ll use a simple SQL schema. This could be

any SQL database, such as MySQL. We used

sqlite, an SQL interface to

dbm files, to build this example. To get you started, we’ve provided a SQL

file with the model of countries and their codes, and the various feature

types, which you can download from http://mappinghacks.com/gazetteer/,

along with the original files and short scripts from which it was built:

create table country(

id integer primary key not null,

name varchar(255),

iso varchar(2),

fips varchar(2)

);

create table feature (

id integer primary key not null,

name varchar(64),

code varchar(4),

fc varchar(2)

);

CREATE TABLE place (

id integer primary key not null,

name varchar(255),

country integer,

ufi integer,

feature_type integer,

lat double,

lon double,

alt double

);

create index name_index on place (name);

The Code

We wrote a quick script to go through the GEOnet.zip files one by one,

unpacking them and looking up their country and feature codes:

#!/usr/bin/perl

use strict;

use Archive::Zip;

use Data::Dumper;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:gaz.db','','',{AutoCommit => 0});

my $dir = shift;

opendir( DIR, $dir) or die "Couldnt read $dir : $!";

my @files = grep {/zip/} readdir(DIR);

my (%countries,%types);

Build a Free World Gazetteer #84

Chapter 7, Names and Places

|

425

HACK

my $sth = $dbh->prepare("select ...