wiki:NewGeosystem

Some documentation on how all this has finally been implemented (or not) - GeoSystem.

As our current geodata is based on the geonames database and contains the original geonamesid as regions.id and cities.id it will be easy to switch to a new model that allows for:

  • getting updates from geonames
  • individual setting of region hierarchy
  • defining which regions should be displayed on a countries/regions browse page
  • improved signup / accepter tools

Stage I

must be done as quickly as possible in a separate branch

  • create new tables
  • populate the geonames_cache data with content of old regions / city tables
  • populate the regions_regions table with data from the old regions / city tables and geonames.hirarchy service
  • change all queries to use the new db model

Stage II

  • add a new location selector to the signup form
  • geo admin tool to allow modification of regions_regions relations

Stage III

whatever else is sexy.

database model

countries table

geonames.id : the primary key (the one to be used to refer to this country, don't use the name !), taken from geonames

countries.Name : the generic (english) name of the country

counties.isoalpha2 : the short code

countries.AltName? : the other names for this country in other languages (this will be used for search by names)country

countries.WordCode? : the word code asociated to this country to provide various exact translation for each languages, nota in word it will be something like CNTRY_the_word_code_value

countries.NbMembers? : the number of active members in BW in this area, this redudancy will be needed because if not mysql will slow down

geonames_cache table

geonames.id : the primary key (the one to be used to refer to this region, don't use the name !)

geonames_cache.IdCountry? : for when a region belong to one country

geonames_cache.Name : the generic (english) name of the country

geonames_cache.AltName? : the other names for this country in other languages (this will be used for search by names)country

geonames_cache.Population : used for sorting

geonames_cache.latitude : a latitude refering to the region (the middle, the capital ?)

geonames_cache.longitude : a latitude refering to the region (the middle, the capital ?)

geonames_cache.Admincode: the type of the region (city / state)

regions_additional

some data that is useful but not derived from geonames.

geonames.id

regions.WordCode? : the word code asociated to this regionto provide various exact translation for each languages, nota in word it will be something like REGION_the_word_code_value. We should think about a way to avoid the translation too to include by default all the furture untranslated words for region, because translators will run away !

regions.NbMembers? : the number of active members in BW in this area, this redudancy will be needed because if not mysql will slow down

regions.type : this is to be discussed, this is a set (several values allowed) I propose :

-IsACity: I think this has a meaning for many people, not for all earthmen however

--IsAnADM1: (per geonames meaning)

-IsTerminal?: (when they are no child region for this (think about recursivity)). This is some redudancy.

-InSeveralCountry?: (like les Alpes, no need to look for a regions.IdCountry? then)

-...

regions.Comment: a text comment (usually empty, and I wish good luck to translators with some cases like Palestine is it really in Israel ? or others, this is not intented to be display, but intented to help geograph not to destroy each other work)

regions_regions table

This table allow to link regions within regions

geonames.id

regions_regions.IdParentRegion? : the id of a parent region

regions_regions.IdChildRegion? : the id of the child region

regions_regions.Comment : a text comment (usually empty, and I wish good luck to translators with some cases like Palestine or others, this is not intented to be display, but intented to help geograph not to destroy each other work)

tags_regions table

IdTag? : the id of the tag in the future tags table

IdRegion? : the id of the region

addresse table

this is the current BW addresse table, I think it make sense to explain it here.

A member can have several Addresses, but an Addresses belong to only one member. (if two members live at the same place we create two record, one day, one member can leave).

addresses.id : the primary key (the one to be used to refer to this addresses, don't use the name !)

addresses.StreetName? : It is crypted owner can hide it

addresses.IdGeoName? : new, the geoname identifier (it will provide the name)

addresses.IdGoogleName? : new, the google identifier (it will provide the google name, I am unsure about this)

addresses.Latitude : a latitude refering to the region, a location has a latitude and only one

addresses.Longitude : a latitude refering to the region, a location has a longitude and only one

addresses.Zip : It is crypted owner can hide it

addresses.HouseNumber? : It is crypted owner can hide it

addresses.IdMember? : the member who own this addresse

addresses.IdCity? : here it is a link to a region of with type=IsCity?, so the addresseswill be inside all parent regions of this region (but not in sub regions, may be this is a problem)

addresses.rank : the rank of the addresses (0 = main addresse for this member, 1, second etc etc)

Implementing a new geosystem

Related Tickets:

#315
location selector interface (eg address at signup)
#316
new geo database
#317
location selector query logic
#318
script to port member geodata from old to new DB

Outline of the new system

  • reference systems: geonames, google, own database
  • hierarchy for administrative categorization
    • country
    • AdministrativeArea
    • SubAdministrativeArea
    • Locality
    • DependentLocality
    • Thoroughfare

regions

  • regions can be part of and contain one or more parent/child regions
  • cities are treated as regions
  • regions of interest (non administrative, as hiking resort, beach, ...) are treated the same way but won't be implemented at this point
  • regions can be defined by

location

  • a location is specified by its coordinates
  • it can be exact or approximate
  • it locates in on ore more regions and all the parent regions
  • can describe location of:
    • members home (address)
    • members location (while traveling)
    • waypoint (planned or traveled route)
    • related blog/forum/wiki/gallery/calendar entry
    • ...

address handling

An address gets associated with

  • administrative regions
  • location (coordinates)

by:

  • asking geonames, google, our database
  • choosing location from a map

security/privacy

  • address can be stored encrypted
  • address can be hidden
  • when address is hidden only approximate location (citylevel or picked by member) is shown

administration

members can:

  • create regions of interest
  • create locations
  • add locations/regions to regions

geo team can:

  • create/delete administrative regions
  • merge regions
  • change relation between regions, locations and regions
  • rebuild the database using the stored address/location data
  • ...

tags

regions / locations can be tagged by the members.

translation

  • English names are the default / reference
  • alternative names from google/geonames are used where appropriate
  • names of locations/regions/countries are associated with the translation system.

locations table

to be added later locations are used to link various things to a geograpical place and store additional info

locations.id : the primary key (the one to be used to refer to this locations, don't use the name !)

locations.StreetName? : the street address, no need to translate

locations.Zip : the zip if any

locations.HouseNumber? : the "houseNumber if any"

locations.geonamesid: the geoname identifier (it will provide the name)

locations.Latitude : a latitude refering to the region, a location has a latitude and only one

locations.Longitude : a latitude refering to the region, a location has a longitude and only one

locations.Type : no idea jy: please the who has said they are type of location, detail a bit more may be it is event , monument , famous place

locations.IdMember? : jy : non sense if a location can be for several members

locations.IdCity? : here it is a link to a region of with type=IsCity?, so the location will be inside all parent regions of this region (but not in the city sub regions, may be this is a problem)

Last modified 11 years ago Last modified on Aug 15, 2008, 10:47:54 AM