Diversity Gazetteer
Internals
For users that need more detailed information about the software including database design etc.
Subsections of Internals
Diversity Gazetteer
Database
DiversityGazetteer - Database
The diagram of for the main tables of the database is shown below.
The description is available in the databaseobjects and enumeration table chapters .
Subsections of Database
Diversity Gazetteer
Data Access
Access to the data
To get access to the data, you have to take several hurdles. In DiversityGazetteers you must be a member of one of the usergroups. You have only access to those data, that are listed in the projects you have access to.
Diversity Gazetteer
User groups
A user can be in 3 groups with diverse rights in the database where certain higher groups have all rights of lower groups in addition to special rights for this group, e.g. the group DiversityWorkbenchUser can only read the data of certain tables while DiversityWorkbenchEditor has the rights of DiversityWorkbenchUser and additionally can edit the data in certain tables - see overview below.
Summarized overview of the permissions of the groups
| Role | Permissions in addition to lower role resp. user group | Inculded rights |
| DiversityWorkbenchAdministrator | Delete data, edit user permissions | DiversityWorkbenchEditor |
| DiversityWorkbenchEditor | Create new entries and delete details (not whole datasets) | DiversityWorkbenchUser |
| DiversityWorkbenchUser | See the data of the data tables, add annotations |
If you are a DiversityWorkbenchAdministrator you can add a user to one of these Logins.
Any user may have access to several Projects.
Diversity Gazetteer enumeration tables
Enumeration tables
The following objects are not included:
-
Logging tables
-
System objects
-
Objects marked as obsolete
-
Previous versions of objects
| Column | Data type | Description |
|---|---|---|
| Code | nvarchar (50) | - |
| Description | nvarchar (500) | - |
| DisplayText | nvarchar (50) | - |
| DisplayOrder | smallint | - |
| DisplayEnable | bit | - |
| ParentCode | nvarchar (50) | - |
| InternalNotes | nvarchar (500) | - |
| HierarchyPrefixUp | nvarchar (50) | - |
| HierarchyPrefixDown | nvarchar (50) | - |
| HierarchyPostfixUp | nvarchar (50) | - |
| HierarchyPostfixDown | nvarchar (50) | - |
Table LanguageCode_Enum
Dependent tables:
- GeoName
- GeoNameLanguage
Table PlaceType_Enum
Dependent tables:
- GeoPlace
- GeoPlaceType
Diversity Gazetteer
The following objects are not included:
- Logging tables
- Enumeration tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
Tables, …
Table ExternalDatabase
External datasource of the data
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExternalDatabaseID | int | ID of the source, PK | NO | - |
| ExternalDatabaseName | nvarchar (60) | Name of the external datasource | NO | - |
| ExternalDatabaseVersion | nvarchar (255) | Version of the external datasource | NO | - |
| ExternalAttribute_NameID | nvarchar (255) | Attribute of the extarnal source for mapping the data in table GeoName | YES | - |
| ExternalAttribute_PlaceID | nvarchar (255) | Attribute of the extarnal source for mapping the data in table GeoPlace | YES | - |
| ExternalCoordinatePrecision | nvarchar (255) | Description of the coordinate precision in the external source | YES | - |
| InternalNotes | nvarchar (MAX) | Notes concerning the import of the data | YES | - |
Table GeoCache
Cached values for the hierarchy of places
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlaceID | int | Primary key as defined in table GeoPlace | NO | - |
| Country | nvarchar (100) | The cached country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID | YES | - |
| HierarchyPlaceToCountry | nvarchar (2000) | The cached hierarchy from the place to the country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID | YES | - |
| HierarchyCountryToPlace | nvarchar (2000) | The cached hierarchy from the country to the place derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID | YES | - |
| SuperiorPlaceID | int | Temporary used value for the superior place ID - empty after successful update of the cache values | YES | - |
Diversity Gazetteer
Hierarchy
The hierarchy of the places is stored in table GeoPlace via the columns PlaceID and SuperiorPlaceID referring the PlaceID. As the calculation of the hierarchy is slow, the results are cached in the table GeoCache.
To include the continent in the hierarchy string that will be added to the links from other modules choose → 
Hierarchy: include continent from the menu. As next step choose → 
Update DB Hierarchy Cache from the menu to calculate the hierarchy and store it in the cache table. A short video 
shows details.