Geonames Hierarchy
In case you’re wondering how to get the hierarchy of a Geonames place, this is my recipe. As per Marc suggestion, I preprocessed the Geonames dump and fill a table with some handy columns and indexes.
You’ll get with something like this:
mysql> select * from places_preprocessed where name=’Almagro’\G
*************************** 1. row ***************************
id: 3436397
name: Almagro
alternames:
ansiname: Almagro
lat: -34.6
lon: -58.4166667
parent_ids: |3865483|3433955|3436397|
parent_names: |Argentine Republic|Distrito Federal|Almagro|
feature_code: PPLX
depth: 3
The following code is in Ruby, using Activerecord.
There’re 2 tables, “geonames” which holds the Geonames dump and “places_preprocessed” with the result data.
CREATE TABLE `geonames` (
`id` int(10) unsigned NOT NULL,
`name` varchar(200) NOT NULL default ”,
`ansiname` varchar(200) NOT NULL default ”,
`alternames` varchar(2000) NOT NULL default ”,
`latitude` double NOT NULL default ‘0’,
`longitude` double NOT NULL default ‘0’,
`feature_class` char(1) default NULL,
`feature_code` varchar(10) default NULL,
`country_code` char(2) default NULL,
`cc2` varchar(60) default NULL,
`admin1_code` varchar(20) default ”,
`admin2_code` varchar(80) default ”,
`admin3_code` varchar(20) default ”,
`admin4_code` varchar(20) default ”,
`population` int(11) default ‘0’,
`elevation` int(11) default ‘0’,
`gtopo30` int(11) default ‘0’,
`timezone` varchar(40) default NULL,
`modification_date` date default ‘0000-00-00’,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8CREATE TABLE `places_preprocessed` (
`id` int(10) unsigned NOT NULL,
`name` varchar(200) default NULL,
`alternames` varchar(255) default NULL,
`ansiname` varchar(200) default NULL,
`lat` double default NULL,
`lon` double default NULL,
`parent_ids` varchar(200) default NULL,
`parent_names` tinytext,
`feature_code` varchar(10) default NULL,
`depth` smallint(6) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_ids` (`parent_ids`),
KEY `feature_code` (`feature_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And now, the code:
class Geonames < ActiveRecord::Base
FEATURE_CLASS_HIERARCHY=[‘ADM1’, ‘ADM2’, ‘ADM3’, ‘ADM4’, ‘ADMD’, ‘LTER’, ‘PCL’, ‘PCLD’, ‘PCLF’, ‘PCLI’, ‘PCLI’, ‘PCLS’, ‘PRSH’, ‘TERR’, ‘ZN’, ‘ZNB’]
MAIN_HIERARCHY=[ ‘PCLI’,’ADM1’, ‘ADM2’, ‘ADM3’, ‘ADM4’,[‘PPLC’,’PPLA’,’PPLX’]]
def find_main_children
pos = MAIN_HIERARCHY.index(self.feature_code)
return [] if not pos
next_feature_codes = MAIN_HIERARCHY[pos + 1 .. -1]
puts “Children codes: #{next_feature_codes.inspect} (current fcode: #{self.feature_code})”
r = []
next_feature_codes.each { |next_feature_code|
if next_feature_code
puts “Finding children with code #{next_feature_code}”
sql = “country_code = ? and admin2_code = ? and admin3_code = ? and admin4_code = ? and feature_code in (?)”
param = [self.country_code, self.admin2_code,self.admin3_code,self.admin4_code,next_feature_code]
if self.admin1_code and self.admin1_code != ‘00’
sql += ” and admin1_code = ? “
param « self.admin1_code
end
puts “** SQL: #{sql} p: #{param.inspect}”
r = Geonames.find(:all,:conditions => [sql, *param])
return r if not r.empty?
end
}
r
end
def find_children
next_feature_code = FEATURE_CLASS_HIERARCHY[FEATURE_CLASS_HIERARCHY.index(self.feature_code) + 1]
Geonames.find(:all,:conditions => [“country_code = ? and admin1_code = ? and admin2_code = ? and admin3_code = ? and admin4_code = ? and feature_code in (?)”,self.country_code, self.admin1_code,self.admin2_code,self.admin3_code,self.admin4_code,next_feature_code]) if next_feature_code
end
def parent
FEATURE_CLASS_HIERARCHY.reverse.each{ |feature_code|
break if feature_code == self.feature_code
place = Geonames.find(:first,:conditions => [“country_code = ? and admin1_code = ? and admin2_code = ? and admin3_code = ? and admin4_code = ? and feature_code = ? “,country_code, admin1_code, admin2_code, admin3_code, admin4_code, feature_code]);
return place if place
}
end
end
And the code to preprocess:
class Place < ActiveRecord::Base
set_table_name :places_preprocessed;
has_many :properties
def self.preprocess
ActiveRecord::Base.connection.execute(“TRUNCATE TABLE `#{table_name}`”);
Geonames.find(:all, :conditions => “feature_code = ‘PCLI’ and feature_class = ‘A’ and admin1_code = ‘00’”).each{ |country|
Place.preprocess_place(country)
}
end
def self.preprocess_place(place, parent = nil, pad = ”)
puts pad + “Preprocess place: #{place.inspect}”
parent = parent || place.parent
puts pad + ” * parent is: #{parent}”
if parent and parent.is_a?(Place)
parent_ids = parent.parent_ids
parent_names = parent.parent_names
depth = parent.depth
else
parent_ids = “|”
parent_names = “|”
depth = 0
end
preprocessed = Place.new
preprocessed.id = place.id
preprocessed.name = place.name;
preprocessed.feature_code = place.feature_code
preprocessed.lat = place.latitude
preprocessed.lon = place.longitude
preprocessed.parent_ids = parent_ids + place.id.to_s + “|”
preprocessed.parent_names = parent_names + preprocessed.name + “|”
preprocessed.alternames = place.alternames
preprocessed.ansiname = place.ansiname
preprocessed.depth = depth + 1
puts pad + ” * presave #{preprocessed}”
preprocessed.save()
puts pad + ” * processing children… “
children = place.find_main_children
puts pad + ” * found #{children.length} children”
children.each{|child|
begin
Place.find(child.id)
puts pad + ” * #{child.to_s} (#{child.id}) already processed!”
rescue
Place.preprocess_place(child, preprocessed, pad + ‘ ‘)
end
}
end
end
Usage, just run:
Place.preprocess()
