Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    7

    Question Unanswered: Making (or faking) a geographical hierarchy of names?

    I'm not sure if this best belongs here, but I'm using MySQL. I want to make a 'list' of geographical names. This is for metadata describing pictures and articles. I did some power-googling, had trouble finding stuff. My idea for the list:

    -one table with all the names in it. (countries, prov's/states, districts/counties, municipalities, etc.)
    -each name type is assigned a level # according to its type, 1 for countries, 2 for prov/states, down the line to the smallest unit (could be street names/numbers or even individual addresses). geographical features/areas unclassifiable by political boundaries are assigned #0.
    -the database starts off with some practical names in it - the ones most likely to be needed by users. the user selects from level 1 and down to where they feel is specific enough. (if they're unsure about the exact location, they can stop at the province/state level, or skip the district/county level)
    -a level 0 name can be added anywhere in the tree
    -if a name is not in the list, which may often happen with city names or lesser levels, there is a box to type one in. This 'proposal' can then be approved by webmaster or whomever, and added to the database.
    -there is no actual heirarchy, it's implied by sorting items by level #'s
    -only one of each type may be selected for each entry, stored in another table, types comma-delimited, disparate locations semicolon-delimited.

    Is this practical and efficient? Any links about this type of database? Thoughts? Opinions? Fire at will!
    Last edited by trefrog; 02-23-06 at 17:45.

  2. #2
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Quote Originally Posted by trefrog
    I'm not sure if this best belongs here, but I'm using MySQL. I want to make a 'list' of geographical names. This is for metadata describing pictures and articles. I did some power-googling, had trouble finding stuff. My idea for the list:

    -one table with all the names in it. (countries, prov's/states, districts/counties, municipalities, etc.)
    -each name type is assigned a level # according to its type, 1 for countries, 2 for prov/states, down the line to the smallest unit (could be street names/numbers or even individual addresses). geographical features/areas unclassifiable by political boundaries are assigned #0.
    -the database starts off with some practical names in it - the ones most likely to be needed by users. the user selects from level 1 and down to where they feel is specific enough. (if they're unsure about the exact location, they can stop at the province/state level, or skip the district/county level)
    -a level 0 name can be added anywhere in the tree
    -if a name is not in the list, which may often happen with city names or lesser levels, there is a box to type one in. This 'proposal' can then be approved by webmaster or whomever, and added to the database.
    -there is no actual heirarchy, it's implied by sorting items by level #'s
    -only one of each type may be selected for each entry, stored in another table, types comma-delimited, disparate locations semicolon-delimited.

    Is this practical and efficient? Any links about this type of database? Thoughts? Opinions? Fire at will!
    This is not efficient , think abt it, if you want to list addresses from 3 countries, imagine the number of recursive joins to the same table to accomplish this?

    A better way is to design this relationally
    Create table countries, PK COUNTRY_ID
    Create Table prov's/states , PK STATE_ID, FK COUNTRY_ID
    Create table districts/counties, PK DISTRICT_ID, FK STATE_ID
    Create table municipalities, PK MUNCIPALITY_ID, FK DISTRICT_ID

    PK - Primary Key cannot be null
    FK - Foreign Key can be null
    BTREE index on all keys.

    The hierarchy that you want can be clearly obtained by this.
    Let me know if this design is plausible then we can discuss solutions to your query requirements .

  3. #3
    Join Date
    Jan 2006
    Posts
    7
    Edit: I don't know if you read the original post, but I'm catching up on foreign keys and BTREE. I'm new to this. Would this be using MyISAM or InnoDB?
    Last edited by trefrog; 02-24-06 at 12:26.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •