GazetteersModel 01.00.31
Contents
- 1 DiversityGazetteers Information Model (version 01.00.31, 04. March 2020)
- 1.1 dwbG Database Scheme
- 1.2 ER Diagrams
- 1.2.1 Overview over all entities and relations used in the database model
- 1.2.2 A short introduction:
- 1.2.3 Table: ExternalDatabase
- 1.2.4 Table: GeoCache
- 1.2.5 Table: GeoName
- 1.2.6 Table: GeoNameLanguage
- 1.2.7 Table: GeoPlace
- 1.2.8 Table: GeoPlaceType
- 1.2.9 Table: GeoProject
- 1.2.10 Table: LanguageCode_Enum
- 1.2.11 Table: PlaceType_Enum
- 1.2.12 Table: ProjectProxy
- 1.2.13 Table: ProjectUser
- 1.2.14 Table: UserProxy
DiversityGazetteers Information Model (version 01.00.31, 04. March 2020)
Authors | M. Weiss, W. Reichert & D. Triebel 2020 |
License | ![]() |
Suggested citation | M. Weiss, W. Reichert & D. Triebel (2020). DiversityGazetteers information model (version 01.00.31). http://diversityworkbench.net/Portal/GazetteersModel_01.00.31. |
Notes | The models currently reside in MS SQL Server, so knowledge of some SQL Server ER-diagram conventions will be helpful.
Besides the screen shots below, a Microsoft T-SQL-Script for the generation of the tables is provided. |
dwbG Database Scheme
This information model is available as dwbG database scheme with each single data table and data column referenced as term or concept by its own stable and persistent URL.
ER Diagrams
Overview over all entities and relations used in the database model
Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview or the detail diagrams.
A short introduction:
- “GeoPlace” is the central entity.
Entities ending with “_Enum” (“LanguageCode_Enum”) contain enumerated values or strings.
Table: ExternalDatabase
Column | Data type | Description |
---|---|---|
ExternalDatabaseID | int | |
ExternalDatabaseName | nvarchar (60) | |
ExternalDatabaseVersion | nvarchar (255) | |
ExternalAttribute_NameID | nvarchar (255) | |
ExternalAttribute_PlaceID | nvarchar (255) | |
ExternalCoordinatePrecision | nvarchar (255) | |
InternalNotes | ntext (1073741823) |
Table: GeoCache
Column | Data type | Description |
---|---|---|
PlaceID | int | Primary key as defined in table GeoPlace |
Country | nvarchar (100) | The cached country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID |
HierarchyPlaceToCountry | nvarchar (2000) | The cached hierarchy from the place to the country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID |
HierarchyCountryToPlace | nvarchar (2000) | The cached hierarchy from the country to the place derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID |
SuperiorPlaceID | int | Temporary used value for the superior place ID - empty after successful update of the cache values |
Table: GeoName
Column | Data type | Description |
---|---|---|
NameID | int | |
Name | nvarchar (400) | The name of the place |
PlaceID | int | The ID of the place, refers to table GeoPlace |
LanguageCode | nvarchar (50) | The code of the language |
ExternalDatabaseID | int | The ID of the external database as stored in the table ExternalDatabase (foreign key) |
ExternalNameID | nvarchar (50) | The ID in the place in the foreign (source) database |
HierarchyCache | nvarchar (500) | The hierarchy of the place, cached value. |
Notes | nvarchar (MAX) | Notes about the geographic name |
HierarchyList | nvarchar (255) | A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID |
Table: GeoNameLanguage
Column | Data type | Description |
---|---|---|
NameID | int | |
LanguageCode | nvarchar (50) |
Table: GeoPlace
Column | Data type | Description |
---|---|---|
PlaceID | int | ID of the place, PK |
Geography | geography | The geography of the place as WGS84 Koordinates and if available the altitude and the area |
SuperiorPlaceID | int | The ID of the superior place |
CountryPlaceID_Cache | int | The ID of the country a place belongs to |
RegionPlaceID_Cache | int | The ID of a region a place belongs to, e.g. a continent for a country |
ExternalDatabaseID | int | The ID of the external database as stored in the table ExternalDatabase (foreign key) |
ExternalNameID | nvarchar (50) | The ID in the place in the foreign (source) database |
PlaceType | nvarchar (50) | The type of the place, Foreign key, refers to table PlaceType_Enum |
PreferredNameID | int | The NameID of the name preferred for this place. Foreign key refers to table GeoName |
Notes | nvarchar (MAX) | Notes about the geographic place |
Table: GeoPlaceType
Column | Data type | Description |
---|---|---|
PlaceID | int | |
PlaceType | nvarchar (50) |
Table: GeoProject
Column | Data type | Description |
---|---|---|
ProjectID | int | |
NameID | int |
Table: LanguageCode_Enum
Column | Data type | Description |
---|---|---|
Code | nvarchar (50) | |
Description | nvarchar (500) | |
DisplayText | nvarchar (50) | |
DisplayOrder | smallint | |
DisplayEnable | bit | |
ParentCode | nvarchar (50) | |
InternalNotes | nvarchar (500) |
Table: PlaceType_Enum
Column | Data type | Description |
---|---|---|
Code | nvarchar (50) | |
Description | nvarchar (500) | |
DisplayText | nvarchar (50) | |
DisplayOrder | smallint | |
DisplayEnable | bit | |
ParentCode | nvarchar (50) | |
InternalNotes | nvarchar (500) |
Table: ProjectProxy
The projects available within the database
Column | Data type | Description |
---|---|---|
ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
Project | nvarchar (50) | The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) |
RowGUID | uniqueidentifier | DefaultValue: newsequentialid() |
ProjectURI | varchar (255) | The URI of the project, e.g. as provided by the module DiversityProjects. |
Table: ProjectUser
The projects available for a user
Column | Data type | Description |
---|---|---|
ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
RowGUID | uniqueidentifier | DefaultValue: newsequentialid() |
Table: UserProxy
The users with access to the database
Column | Data type | Description |
---|---|---|
CombinedNameCache | nvarchar (255) | The short name of the user, e.g. P. Smith |
AgentURI | varchar (255) | A link to a DiversityAgents module where further informations about the user is available. |
Queries | xml (MAX) | |
CurrentProjectID | int | The current project selected by the user |
RowGUID | uniqueidentifier | DefaultValue: newsequentialid() |
ID | int | ID of the user |
PrivacyConsent | bit | If the user consents the storage of his user name in the database |
PrivacyConsentDate | datetime | The time and date when the user consented or refused the storage of his user name in the database |
Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined