There are 0 registered members and 8 guests currently viewing the site.
Register - Log In - Lost Password ?
Photographer's World Database of Countries, States, and Cities: Structure
This page lists the structure of each table in the 'photographer' World Database of Countries, States, and Cities. It also includes some comments about each of the columns within each of the eight tables, to help you understand the structure of the database.
TableName: country
PrimaryKey: CountryID
Column List: CountryID, CountryName, CountryAbbrev, CountryLon, CountryLat, GoogleZoom, DefaultPhotoID
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | CountryID | INT( 3 ) | NOT NULL | 3-digit number to reference each Country |
| 02 | CountryName | VARCHAR( 99 ) | NOT NULL | India |
| 03 | CountryAbbrev | CHAR( 3 ) | NOT NULL | IND |
| 04 | CountryLon | DECIMAL( 5,2 ) | NULL | Longitude of central point: 012.34 |
| 05 | CountryLat | DECIMAL( 4,2 ) | NULL | Latitude of central point: 01.23 |
| 06 | GoogleZoom | INT( 2 ) | NULL | 2-digit number used to store initial zoom-level for online maps |
| 07 | DefaultPhotoID | INT( 7 ) | NOT NULL | 7-digit number used to store the PhotoID of a default photo that you associate with this Country |
Notes: This table contains the master list of all countries, and provides a variety of information that can be displayed alongside your photographs. The central position can be used to create markers for online dynamic maps, such as Google Maps, and the GoogleZoom field can then store the initial value of zoom that the map should be displayed with.
TableName: state
PrimaryKey: StateID
Column List: StateID, StateName, StateAbbrev, CountryName, CountryAbbrev, StateLon, StateLat, StateTypeEnglish, StateTypeLocal, StatePop2002, GoogleZoom, DefaultPhotoID
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | StateID | INT( 4 ) | NOT NULL | 4-digit number to reference each State |
| 02 | StateName | VARCHAR( 42 ) | NOT NULL | Texas |
| 03 | StateAbbrev | CHAR( 3 ) | NULL | UTX |
| 04 | CountryName | VARCHAR( 40 ) | NOT NULL | United States |
| 05 | CountryAbbrev | CHAR( 3 ) | NOT NULL | USA |
| 06 | StateLon | DECIMAL( 5,2 ) | NULL | Longitude of central point: 012.34 |
| 07 | StateLat | DECIMAL( 4,2 ) | NULL | Latitude of central point: 01.23 |
| 08 | State_Type_English | CHAR( 26 ) | NULL | Emirate |
| 09 | State_Type_Local | CHAR( 50 ) | NULL | Imarah |
| 10 | State_Pop_2002 | DECIMAL( 9,0 ) | NULL | 861874 |
| 11 | GoogleZoom | INT( 2 ) | NULL | 2-digit number used to store initial zoom-level for online maps |
| 12 | DefaultPhotoID | INT( 7 ) | NOT NULL | 7-digit number used to store the PhotoID of a default photo that you associate with this State |
Notes: This table contains all known states, provinces, and other administration areas. Population unknown = '-99999'. Where no State_Abbrev exists, this has been filled with the Country_Abbrev. The central position can be used to create markers for online dynamic maps, such as Google Maps, and the GoogleZoom field can then store the initial value of zoom that the map should be displayed with.
TableName: city
PrimaryKey: CityID
Column List: CityID, CityName, CountryName, CountryAbbrev, StateName, StateAbbrev, CityArea, CityLon, CityLat, GoogleZoom, DefaultPhotoID
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | CityID | INT( 6 ) | NOT NULL | 6-digit number to reference each City |
| 02 | CityName | VARCHAR( 42 ) | NOT NULL | Patna |
| 03 | CountryName | VARCHAR( 40 ) | NULL | India |
| 04 | CountryAbbrev | CHAR( 3 ) | NULL | IND |
| 05 | StateName | VARCHAR( 42 ) | NULL | Bihar |
| 06 | StateAbbrev | CHAR( 3 ) | NULL | BIH |
| 07 | CityArea | FLOAT | NULL | |
| 08 | CityLon | DECIMAL( 5,2 ) | NULL | Longitude of central point: 012.34 |
| 09 | CityLat | DECIMAL( 4,2 ) | NULL | Latitude of central point: 01.23 |
| 10 | GoogleZoom | INT( 2 ) | NULL | 2-digit number used to store initial zoom-level for online maps |
| 11 | DefaultPhotoID | INT( 7 ) | NOT NULL | 7-digit number used to store the PhotoID of a default photo that you associate with this City |
Notes: Where a name has changed, or a more common spelling is now used, the database has been updated to the Lonely Planet spelling. The central position can be used to create markers for online dynamic maps, such as Google Maps, and the GoogleZoom field can then store the initial value of zoom that the map should be displayed with.
TableName: flag
PrimaryKey: FlagID
Column List: FlagID, FlagName, CountryAbbrev, StateAbbrev, FlagLon, FlagLat, LinkToFlag
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | FlagID | INT( 3 ) | NOT NULL | 3-digit number to reference each Flag |
| 02 | FlagName | VARCHAR( 99 ) | NOT NULL | India |
| 03 | CountryAbbrev | CHAR( 3 ) | NULL | IND |
| 04 | StateAbbrev | CHAR( 5 ) | NULL | BIH |
| 05 | FlagLon | DECIMAL( 5,2 ) | NULL | Longitude of central point: 012.34 |
| 06 | FlagLat | DECIMAL( 4,2 ) | NULL | Latitude of central point: 01.23 |
| 07 | LinkToFlag | CHAR( 7 ) | NOT NULL | IND |
Notes: This table lists all the 48x32 pixel mini-flag .gifs that are included with this database. The Link_To_Flag field is used to create a link to the flag image that you wish to display. Either Country_Abbrev or State_Abbrev may be present, but the Link_To_Flag always contains the correct file name (not including '.gif'). All my available mini-flags are listed.
TableName: type_of_place
PrimaryKey: TypeOfPlaceID
Column List: TypeOfPlaceID, ClassOfPlaceID, TypeOfPlace, WaypointSymbol
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | TypeOfPlaceID | INT( 4 ) | NOT NULL | 4-digit number to reference each Type_Of_Place |
| 02 | ClassOfPlaceID | INT( 2 ) | NOT NULL | 2-digit number to reference each Class_Of_Place |
| 03 | TypeOfPlace | VARCHAR( 25 ) | NOT NULL | Temple - Hindu |
| 04 | WaypointSymbol | INT( 2 ) | NULL | 2-digit number to reference required Waypoint Symbol |
Notes: The TypeOfPlace table is used to provide a way of identifying what type of a place each photograph was taken of, or in, and can thus provide an extra category that photographs can be searched under or grouped under. The types are sub-divided into categories called 'ClassOfPlace', which can be used when presenting a list for data-entry. These values are stored in the table 'ClassOfPlace'. The last field refers to the Waypoint Symbol used for each TypeOfPlace. These values are used when generating Waypoint lists, and dictate what type of Waypoint Symbol is displayed on a GPS receiver.
TableName: class_of_place
PrimaryKey: ClassOfPlaceID
Column List: ClassOfPlaceID, ClassName
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | ClassOfPlaceID | INT( 2 ) | NOT NULL | 2-digit number to reference each Class_Of_Place |
| 02 | ClassName | VARCHAR( 18 ) | NOT NULL | Name of the Class Of Place, i.e. Transportation |
Notes: The ClassOfPlace table is used to reduce redundancy in the TypeOfPlace table. It lists the categories that each TypeOfPlace is grouped under: 01-Worship, 02-Sleeping, 03-Eating, 04-Shopping, 05-Entertainment, 06-Transportation, 07-Essential Services, 08-Tourism, 09-Sites of Interest, 10-Sport, 11-Settlement, 12-Industry, 13-Natural, 14-Marine.
TableName: theme
PrimaryKey: ThemeID
Column List: ThemeID, ThemeName, SubThemeName, SubSubThemeName, DefaultPhotoID
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | ThemeID | INT( 5 ) | NOT NULL | 5-digit number to reference each Theme |
| 02 | ThemeName | VARCHAR( 24 ) | NOT NULL | Religion |
| 03 | SubThemeName | VARCHAR( 24 ) | NOT NULL | Hindu |
| 04 | SubSubThemeName | VARCHAR( 24 ) | NOT NULL | Temples |
| 05 | DefaultPhotoID | INT( 7 ) | NOT NULL | 7-digit number used to store the PhotoID of a default photo that you associate with this Theme |
Notes: The Themes table is used to associate a theme to each of your photographs. It thus provides an extra category that photographs can be searched under or grouped under. It also provides a simple method of creating themed-galleries and portfolios. Each Theme can have, optionally, 2 levels of sub-theme, providing a wide range of theme classification.
TableName: photo
PrimaryKey: PhotoID
Column List: PhotoID, LinkToPhoto, DateTaken, TimeTaken, CountryID, StateID, CityID, CountryName, StateName, CityName, TypeOfPlaceID, ThemeID, PhotoLat, PhotoLon, PhotoASL, Description, Keywords, Release, Orientation, Quality, HDR_Set, Status, Width, Height, Views
| Col_no | Column_contents | Column_type | Null? | My Comments |
|---|---|---|---|---|
| 01 | PhotoID | INT( 7 ) | NOT NULL | 7-digit number to reference each Photograph |
| 02 | LinkToPhoto | VARCHAR( 9 ) | NOT NULL | Filename of photo, excluding suffix. One photo = E3120, or for HDR could be E3120-124 |
| 03 | DateTaken | DATE | NOT NULL | 2009-09-28 - Local date taken - YYYY-MM-DD |
| 04 | TimeTaken | TIME | NOT NULL | 01:32 - Local time taken - HH:MM |
| 05 | CountryID | INT( 3 ) | NOT NULL | 3-digit number to reference each Country |
| 06 | StateID | INT( 4 ) | NULL | 4-digit number to reference each State |
| 07 | CityID | INT( 6 ) | NULL | 6-digit number to reference each City |
| 09 | CountryName | VARCHAR( 99 ) | NULL | India - can be omitted if you prefer to reference the 'country' table with the CountryID field |
| 10 | StateName | VARCHAR( 42 ) | NULL | Texas - can be omitted if you prefer to reference the 'state' table with the StateID field |
| 11 | CityName | VARCHAR( 42 ) | NULL | Patna - can be omitted if you prefer to reference the 'city' table with the CityID field |
| 12 | TypeOfPlaceID | INT( 4 ) | NULL | 4-digit number to reference each Type_Of_Place |
| 13 | ThemeID | INT( 5 ) | NULL | 5-digit number to reference each Theme |
| 14 | PhotoLat | DECIMAL( 9,7 ) | NULL | Latitude that photo was taken: 01.1234567 |
| 15 | PhotoLon | DECIMAL( 10,7 ) | NULL | Longitude that photo was taken: 012.1234567 |
| 16 | PhotoASL | INT( 4 ) | NULL | Height, in metres, that photo was taken at |
| 17 | Description | VARCHAR( 254 ) | NULL | Photo description, with initial capital and final period (.) |
| 18 | Keywords | VARCHAR( 254 ) | NULL | List of lower-case keywords, each separated by a space ( ) |
| 19 | Release | CHAR( 1 ) | NOT NULL | Model_Release=M, Property_Release=P, Both=B, Neither=N |
| 20 | Orientation | CHAR( 1 ) | NOT NULL | Image orientation: H=Horizontal, V=Vertical |
| 21 | Quality | INT( 1 ) | NOT NULL | 1=Bad photo, out of focus, 2=Poor composition, 3=Average, 4=Good, 5=Excellent |
| 22 | HDR_Set | CHAR( 1 ) | NOT NULL | N=not part of an HDR set, 1-9=the number in the set, Y=this is an HDR photo |
| 23 | Status | CHAR( 1 ) | NOT NULL | A=Available, S=Sold, O=Under offer, X=Not available |
| 24 | Width | INT( 4 ) | NULL | Width in pixels |
| 25 | Height | INT( 4 ) | NULL | Height in pixels |
| 26 | Views | INT( 5 ) | NULL | Total views of this photograph |
Notes: This is the table you will use to store details of each of your photographs. It has initially been populated with some sample entries, to give you an idea of the format that you should keep to. The 'NOT NULL' fields are required, and the 'NULL' fields are optional.
Stock Photograph Search
Advanced Search Page |
Google Profile
 | Tims Travel Biography Where is Tim? |
Tag Cloud
Popular searches on this site.
Latest Photos
 |  |
 |  |
 |  |
Random Photo
Gold Chinese characters on a black wooden background..
Web design by gnomeplanet.com :: All images and pages on this site are © 2008 - 2012 and remain the property of gnomeplanet.com :: All rights reserved