The BW Geo System

Most of the code related to this can be found in /build/geo/

Rough description of the system:

• get geonameID from geonames (spaf accessited query) or from our current database (to import old data)
• retrieve all information for the geonameId and all its parents from geonames.org
• add information for each geonameId of the resultset to:
  • geonames_cache
  • geonames_alternate_names (translations)
  • geonames_hierarchy (always the parent for each geonameId
  • geo_usage (for example if a member signs up increase the count for geonameId | typeId (where typeId = member) by one)

• daily updates are retrieved from geonames
  • if there is an update for a geonameId stored in our system it will be backed up to geonames_cache_backup and replaced by the new info
  • updates to alternate_names will not be backed up

• if a member moves to a new place the count in geo_usage will be decreased by one
• once there is no reference to a location anymore it will be removed from our system

• if you want to add a city not know to geonames or change some inforamtion this will be done directly on geonames.org and updated the next day to our system

• if you want to add an arbitrary location not available in geonames (for example to show where you have taken a picture went climbing or want to meet somebody):
  • enter latidude/longitude or point it on a map and give it a name
  • the region hierarchy for this spot will be retrieved from geonames
  • it will be stored in geo_location, added to the geo_hierarchy and geo_usage (for locationId and all parent geonameIds)

• if you want to display all cities in germany where we have a member but don't want to display those places where we only have a picture or blog entry you can filter by the typeId in geo_usage. This will allow us to use the same system for all future tools (like gallery, blogs, trips, events, ...) and not only to store the location of our members

• all current code will continue to work as all tables that deal with geodata will be either computed out of the new tables or replaced by views.

geonameId and locationId - where is the difference

• geonameId refers to a location know to the geonames database. This could be a Country, a City, a Mountain or something similar. Information such as translations, population and so on can accompany this.
• locationId refers to any other location that is specified by a latitude or longitude (either entered directly or by map).

DB scheme

There are two types of tables:

• geonames_xxxx: contain information taken from geonames.org
• geo_xxx: contain additional information

geonames_admincodes

InnoDB free: 9216 kB; (country_code) REFER bewelcome/geonames_countries(`iso

code char '

country_code char '

admin_code char '

name varchar '

geonames_alternate_names

table in geonames format holding translated names for geogra; InnoDB free: 9216

alternateNameId int '

geonameId int '

isoLanguage varchar '

alternateName varchar '

isPreferredName binary '

isShortName binary '

geonames_cache

InnoDB free: 9216 kB; (fk_countrycode) REFER bewelcome/geonames_countries(`i

geonameid int '

latitude double '

longitude double '

name varchar '

population int '

fclass varchar '

fcode varchar '

fk_countrycode char '

fk_admincode char '

timezone int '

geonames_cache_backup

InnoDB free: 9216 kB

id int '

geonameid int '

latitude double '

longitude double '

name varchar '

population int '

fclass varchar '

fcode varchar '

fk_countrycode char '

fk_admincode char '

timezone int '

date_updated date '

geonames_countries

InnoDB free: 9216 kB

iso_alpha2 varchar '

name varchar '

continent enum '

languages varchar '

geonames_timezones

geonames based list of timezones; InnoDB free: 9216 kB

TimeZoneId? int '

OffsetJanuary? decimal '

OffsetJuly? decimal '

geo_hierarchy

table to store the hierarchy of geographic elements; InnoDB free: 9216 kB

id int '

geoId int can be geonameId or locationId

parentId int geonameId of the parent region

comment varchar '

geo_location

contains lat/long/name information for arbitrary locations n; InnoDB free: 9216

locationId int '

latitude double '

longitude double '

name varchar '

geo_type

table to differentiate between different types of georeferen; InnoDB free: 9216

id int typeId

name varchar short name to specify the usage type for geo information , eg member

description varchar '

geo_usage

table to keep track how often a geoId is used by a certain t; InnoDB free: 9216

id int '

geoId int geonameId or locationId

typeId int id specifying the usage type, eg member, blog or gallery

count int counts the number of references of type typeId to ths geoId