DiversityGazetteer

The following objects are not included:

• Logging tables
• Enumeration tables
• System objects
• Objects marked as obsolete
• Previous versions of objects

TABLES

• ExternalDatabase
• GeoCache
• GeoName
• GeoNameLanguage
• GeoPlace
• GeoPlaceType
• GeoProject
• ProjectProxy
• ProjectUser
• UserProxy



VIEWS

• GazetteerNameIDCoordinates
• GeoPlaceName
• GeoPlaceNameCountryCoordinates
• GeoPlaceNameIDCountry
• ViewBaseURL
• ViewDiversityWorkbenchModule
• ViewGeoPlace
• ViewHierarchy
• ViewTK25



FUNCTIONS

• BaseURL
• DiversityWorkbenchModule
• HierarchyIncludeContinents
• HierarchySequenceTopDown
• NameSuperior
• NameSuperiorList
• PlaceSuperiorList
• PrivacyConsentInfo
• UserID
• UserName
• Version
• VersionClient



PROCEDURES

• RefreshGeoCache
• RefreshGeoCacheAddHierarchyList
• SetHierarchyCache
• SetHierarchyCacheAll
• SetUserProjects



ROLES

• DiversityWorkbenchAdministrator
• DiversityWorkbenchEditor
• DiversityWorkbenchUser




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	ntext	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	-





Table GeoName

The names of the geographical objects



Column	Data type	Description	Nullable	Relation
NameID	int	The ID of the name, PK	NO	-
Name	nvarchar (400)	The name of the place	NO	-
PlaceID	int	The ID of the place, refers to table GeoPlace	YES	Refers to table GeoPlace
LanguageCode	nvarchar (50)	The code of the language	YES	Refers to table LanguageCode_Enum
ExternalDatabaseID	int	The ID of the external database as stored in the table ExternalDatabase (foreign key)	YES	Refers to table ExternalDatabase
ExternalNameID	nvarchar (50)	The ID in the place in the foreign (source) database	YES	-
HierarchyCache	nvarchar (500)	The hierarchy of the place, cached value.	YES	-
Notes	nvarchar (MAX)	Notes about the geographic name	YES	-
HierarchyList	nvarchar (255)	A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID	YES	-

Depending on:

• ExternalDatabase
• GeoPlace
• LanguageCode_Enum




Table GeoNameLanguage





Column	Data type	Description	Nullable	Relation
NameID	int	-	NO	Refers to table GeoName
LanguageCode	nvarchar (50)	-	NO	Refers to table LanguageCode_Enum

Depending on:

• GeoName
• LanguageCode_Enum




Table GeoPlace

The geographical places



Column	Data type	Description	Nullable	Relation
PlaceID	int	ID of the place, PK	NO	-
Geography	geography	The geography of the place as WGS84 Koordinates and if available the altitude and the area	YES	-
SuperiorPlaceID	int	The ID of the superior place	YES	Refers to table GeoPlace
CountryPlaceID_Cache	int	The ID of the country a place belongs to	YES	Refers to table GeoPlace
RegionPlaceID_Cache	int	The ID of a region a place belongs to, e.g. a continent for a country	YES	Refers to table GeoPlace
ExternalDatabaseID	int	The ID of the external database as stored in the table ExternalDatabase (foreign key)	YES	Refers to table ExternalDatabase
ExternalNameID	nvarchar (50)	The ID in the place in the foreign (source) database	YES	-
PlaceType	nvarchar (50)	The type of the place, Foreign key, refers to table PlaceType_Enum	YES	Refers to table PlaceType_Enum
PreferredNameID	int	The NameID of the name preferred for this place. Foreign key refers to table GeoName	YES	Refers to table GeoName
Notes	nvarchar (MAX)	Notes about the geographic place	YES	-

Depending on:

• ExternalDatabase
• GeoName
• PlaceType_Enum




Table GeoPlaceType

The types of the geographical places



Column	Data type	Description	Nullable	Relation
PlaceID	int	ID of the place, part of PK	NO	Refers to table GeoPlace
PlaceType	nvarchar (50)	Type of the place, part of PK	NO	Refers to table PlaceType_Enum

Depending on:

• GeoPlace
• PlaceType_Enum




Table GeoProject

The projects the names are linked to



Column	Data type	Description	Nullable	Relation
ProjectID	int	The ID of the project, part of PK	NO	Refers to table ProjectProxy
NameID	int	The ID of the name, part of PK	NO	Refers to table GeoName

Depending on:

• GeoName
• ProjectProxy




Table ProjectProxy

The projects available within the database



Column	Data type	Description	Nullable	Relation
ProjectID	int	ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)	NO	-
Project	nvarchar (50)	The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects)	YES	-
ProjectURI	varchar (255)	The URI of the project, e.g. as provided by the module DiversityProjects.	YES	-





Table ProjectUser

The projects available for a user



Column	Data type	Description	Nullable	Relation
LoginName	nvarchar (50)	A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..	NO	Refers to table UserProxy
ProjectID	int	ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)	NO	Refers to table ProjectProxy

Depending on:

• ProjectProxy
• UserProxy




Table UserProxy

The users with access to the database



Column	Data type	Description	Nullable	Relation
LoginName	nvarchar (50)	A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..	NO	-
CombinedNameCache	nvarchar (255)	The short name of the user, e.g. P. Smith Default value: NULL	NO	-
AgentURI	varchar (255)	A link to a DiversityAgents module where further informations about the user is available.	YES	-
Queries	xml (MAX)	-	YES	-
CurrentProjectID	int	The current project selected by the user	YES	-
ID	int	ID of the user	NO	-
PrivacyConsent	bit	If the user consents the storage of his user name in the database	YES	-
PrivacyConsentDate	datetime	The time and date when the user consented or refused the storage of his user name in the database	YES	-





VIEWS

View GazetteerNameIDCoordinates





Column	Data type	Description	Nullable
NameID	int	The ID of the name, PK	NO
Latitude	float	-	YES
Longitude	float	-	YES
URL	varchar (255)	-	YES

Depending on:

• BaseURL
• GeoName
• GeoPlace




View GeoPlaceName





Column	Data type	Description	Nullable
Name	nvarchar (800)	-	NO
NameID	int	-	NO
PlaceName	nvarchar (122)	-	NO
Preferred_PlaceName	nvarchar (122)	-	YES
PlaceTypeID	smallint	-	YES
HierarchyList	nvarchar (255)	-	YES
HierarchyTopID	smallint	-	YES
Country	nvarchar (255)	-	YES
Continent	varchar (20)	-	YES





View GeoPlaceNameCountryCoordinates





Column	Data type	Description	Nullable
Name	nvarchar (379)	The name of the place	YES
NameID	int	The ID of the name, PK	NO
PlaceName	nvarchar (122)	-	NO
Preferred_PlaceName	nvarchar (122)	-	YES
PlaceTypeID	smallint	-	YES
HierarchyList	nvarchar (255)	A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID	YES
HierarchyTopID	smallint	-	YES
Continent	varchar (26)	-	NO
Country	nvarchar (255)	-	YES
Latitude	float	-	YES
Longitude	float	-	YES
URI	varchar (285)	-	YES

Depending on:

• BaseURL
• GazetteerNameIDCoordinates
• GeoPlaceName
• GeoPlaceNameIDCountry




View GeoPlaceNameIDCountry





Column	Data type	Description	Nullable
NameID	int	The ID of the name, PK	NO
Country	nvarchar (400)	-	NO

Depending on:

• GeoName
• GeoPlace




View ViewBaseURL

Content of function dbo.BaseURL() e.g. for linked servers



Column	Data type	Description	Nullable
BaseURL	varchar (255)	-	YES

Depending on:

• BaseURL




View ViewDiversityWorkbenchModule

Content of function dbo.DiversityWorkbenchModule() e.g. for linked servers



Column	Data type	Description	Nullable
DiversityWorkbenchModule	nvarchar (50)	-	YES

Depending on:

• DiversityWorkbenchModule




View ViewGeoPlace

Content of table GeoPlace providing geography as text



Column	Data type	Description	Nullable
PlaceID	int	ID of the place, PK	NO
Geography	nvarchar (MAX)	The geography of the place as WGS84 Koordinates and if available the altitude and the area	YES
Latitude	float	Latitude derived from the center of the geography	YES
Longitude	float	Longitude derived from the center of the geography	YES
Altitude	float	Altitude derived from the center of the geography if available	YES
SuperiorPlaceID	int	The ID of the superior place	YES
CountryPlaceID_Cache	int	The ID of the country a place belongs to	YES
RegionPlaceID_Cache	int	The ID of a region a place belongs to, e.g. a continent for a country	YES
ExternalDatabaseID	int	The ID of the external database as stored in the table ExternalDatabase (foreign key)	YES
ExternalNameID	nvarchar (50)	The ID in the place in the foreign (source) database	YES
PlaceType	nvarchar (50)	The type of the place, Foreign key, refers to table PlaceType_Enum	YES
PreferredNameID	int	The NameID of the name preferred for this place. Foreign key refers to table GeoName	YES
Notes	nvarchar (MAX)	Notes about the geographic place	YES

Depending on:

• GeoPlace




View ViewHierarchy





Column	Data type	Description	Nullable
NameID	int	The ID of the name, PK	NO
Name_0	nvarchar (400)	-	NO
PlaceType_0	nvarchar (50)	-	YES
LanguageCode_0	nvarchar (50)	-	YES
Name_1	nvarchar (400)	-	YES
PlaceType_1	nvarchar (50)	-	YES
LanguageCode_1	nvarchar (50)	-	YES
Name_2	nvarchar (400)	-	YES
PlaceType_2	nvarchar (50)	-	YES
LanguageCode_2	nvarchar (50)	-	YES
Name_3	nvarchar (400)	-	YES
PlaceType_3	nvarchar (50)	-	YES
LanguageCode_3	nvarchar (50)	-	YES
Name_4	nvarchar (400)	-	YES
PlaceType_4	nvarchar (50)	-	YES
LanguageCode_4	nvarchar (50)	-	YES
Name_5	nvarchar (400)	-	YES
PlaceType_5	nvarchar (50)	-	YES
LanguageCode_5	nvarchar (50)	-	YES
Name_6	nvarchar (400)	-	YES
PlaceType_6	nvarchar (50)	-	YES
LanguageCode_6	nvarchar (50)	-	YES
Name_7	nvarchar (400)	-	YES
PlaceType_7	nvarchar (50)	-	YES
LanguageCode_7	nvarchar (50)	-	YES

Depending on:

• GeoName
• GeoPlace




View ViewTK25

Geographical objects of type TK25 including their geographical outline extracted from the geography



Column	Data type	Description	Nullable
PlaceID	int	The ID of the place, refers to table GeoPlace	NO
SuperiorPlaceID	int	The ID of the superior place	YES
Polygon	nvarchar (MAX)	-	YES
TK25	nvarchar (4)	-	YES
TK25_Name	nvarchar (400)	-	NO
Point1	nvarchar (MAX)	-	YES
Point2	nvarchar (MAX)	-	YES
Point3	nvarchar (MAX)	-	YES
Point4	nvarchar (MAX)	-	YES
Lat1	float	-	YES
Lon1	float	-	YES
Lat2	float	-	YES
Lon2	float	-	YES
Lat3	float	-	YES
Lon3	float	-	YES
Lat4	float	-	YES
Lon4	float	-	YES

Depending on:

• GeoName
• GeoPlace




FUNCTIONS

Function BaseURL

Provides the basic address for accessing the database



DataType: varchar (255)







Function DiversityWorkbenchModule

The name of the DiversityWorkbench module



DataType: nvarchar (50)







Function HierarchyIncludeContinents

If the continents should be included in the hierarchy



DataType: bit







Function HierarchySequenceTopDown

If the hierarchy should be created starting at the top level downward



DataType: bit







Function NameSuperior

Returns a table that lists all the names above the given name



Parameter	DataType	Description
@NameID	int	The ID of the name, PK
@HierarchySequenceTopDown	bit	-
@HierarchySeparator	nvarchar (10)	-





Column	DataType	Description
NameID	int	The ID of the name, PK
Name	nvarchar (400)	The name of the place
PreferredName	nvarchar (400)	-
DisplayText	nvarchar (4000)	-
Country	nvarchar (400)	-
Continent	nvarchar (400)	-
Hierarchy	nvarchar (4000)	-
PlaceType	nvarchar (50)	The type of the place, Foreign key, refers to table PlaceType_Enum
HierarchyList	nvarchar (255)	A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID

Depending on:

• GeoName
• GeoPlace
• NameSuperiorList






Function NameSuperiorList

Returns a table that lists all the names above the given name



Parameter	DataType	Description
@NameID	int	The ID of the name, PK





Column	DataType	Description
NameID	int	The ID of the name, PK
Name	nvarchar (400)	The name of the place
PlaceID	int	The ID of the place, refers to table GeoPlace
SuperiorPlaceID	int	The ID of the superior place
CountryPlaceID	int	-
RegionPlaceID	int	-
ExternalDatabaseID	int	The ID of the external database as stored in the table ExternalDatabase (foreign key)
LanguageCode	nvarchar (50)	The code of the language
PlaceType	nvarchar (50)	The type of the place, Foreign key, refers to table PlaceType_Enum
DisplayOrder	smallint	-
HierarchyList	nvarchar (255)	A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID

Depending on:

• GeoName
• GeoPlace
• PlaceType_Enum






Function PlaceSuperiorList

Returns a table that lists all the places above the given place



Parameter	DataType	Description
@PlaceID	int	The ID of the place, refers to table GeoPlace
@Language	nvarchar (50)	-





Column	DataType	Description
PlaceID	int	The ID of the place, refers to table GeoPlace
SuperiorPlaceID	int	The ID of the superior place
Name	nvarchar (400)	The name of the place

Depending on:

• GeoName
• GeoPlace






Function PrivacyConsentInfo

Providing common information within the DiversityWorkbench



DataType: varchar (900)







Function UserID

ID of the User as stored in table UserProxy



DataType: int

Depending on:

• UserProxy






Function UserName

Name of the User as stored in table UserProxy



DataType: nvarchar (50)





Parameter	DataType	Description
@ID	varchar (10)	ID of the user

Depending on:

• UserProxy






Function Version

Version of the database



DataType: nvarchar (8)







Function VersionClient

Version of the client application compatible with the database



DataType: nvarchar (11)







PROCEDURES

Procedure RefreshGeoCache

Depending on:

• GeoCache
• GeoName
• GeoPlace
• HierarchyIncludeContinents






Procedure RefreshGeoCacheAddHierarchyList

Depending on:

• GeoCache
• GeoName
• GeoPlace






Procedure SetHierarchyCache





Parameter	DataType	Description
@HierarchyType	int	-
@LanguageCode	varchar (2)	The code of the language
@ClearCache	int	-
@Separator	char (3)	-
@NameID	int	The ID of the name, PK

Depending on:

• GeoName
• GeoPlace






Procedure SetHierarchyCacheAll





Parameter	DataType	Description
@Separator	char (3)	-

Depending on:

• GeoName
• GeoPlace
• HierarchyIncludeContinents
• HierarchySequenceTopDown






Procedure SetUserProjects

Create database user and assign training projects



Parameter	DataType	Description
@User	varchar (50)	-

Depending on:

• UserProxy






ROLES

DiversityWorkbenchAdministrator

Role with full access to all tables



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
ExternalDatabase	Diversity Workbench User					TABLE
GeoCache	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	Diversity Workbench Editor		TABLE
GeoName	Diversity Workbench User	•	•	•		TABLE
GeoName_log						TABLE
GeoNameLanguage	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor			TABLE
GeoPlace	Diversity Workbench User	•	•	•		TABLE
GeoPlace_log						TABLE
GeoPlaceType	Diversity Workbench User					TABLE
GeoProject	Diversity Workbench User	•	•	•		TABLE
LanguageCode_Enum	Diversity Workbench User					TABLE
PlaceType_Enum	Diversity Workbench User					TABLE
ProjectProxy	Diversity Workbench User					TABLE
ProjectUser	Diversity Workbench User	•	•	•		TABLE
UserProxy	Diversity Workbench User	•	•	•		TABLE
GazetteerNameIDCoordinates	Diversity Workbench User					VIEW
GeoPlaceName	Diversity Workbench User					VIEW
GeoPlaceNameCountryCoordinates						VIEW
GeoPlaceNameIDCountry	Diversity Workbench User					VIEW
ViewBaseURL	Diversity Workbench User					VIEW
ViewDiversityWorkbenchModule	Diversity Workbench User					VIEW
ViewGeoPlace	Diversity Workbench User					VIEW
ViewHierarchy	Diversity Workbench User					VIEW
ViewTK25	Diversity Workbench User					VIEW
BaseURL					Diversity Workbench User	FUNCTION
DiversityWorkbenchModule					Diversity Workbench User	FUNCTION
HierarchyIncludeContinents					Diversity Workbench User	FUNCTION
HierarchySequenceTopDown					Diversity Workbench User	FUNCTION
NameSuperior	Diversity Workbench User					FUNCTION
NameSuperiorList	Diversity Workbench User					FUNCTION
PlaceSuperiorList						FUNCTION
PrivacyConsentInfo					Diversity Workbench User	FUNCTION
UserID					Diversity Workbench User	FUNCTION
UserName					Diversity Workbench User	FUNCTION
Version					Diversity Workbench User	FUNCTION
VersionClient					Diversity Workbench User	FUNCTION
RefreshGeoCache					•	PROCEDURE
RefreshGeoCacheAddHierarchyList					•	PROCEDURE
SetHierarchyCache						PROCEDURE
SetHierarchyCacheAll					•	PROCEDURE
SetUserProjects						PROCEDURE

Inheriting from roles:

• DiversityWorkbenchEditor






DiversityWorkbenchEditor

Role for editing the data, no permission for deleting data in the main tables



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
ExternalDatabase	Diversity Workbench User					TABLE
GeoCache	Diversity Workbench User	•	•	•		TABLE
GeoName	Diversity Workbench User	•	•			TABLE
GeoName_log						TABLE
GeoNameLanguage	Diversity Workbench User	•	•			TABLE
GeoPlace	Diversity Workbench User	•	•			TABLE
GeoPlace_log						TABLE
GeoPlaceType	Diversity Workbench User					TABLE
GeoProject	Diversity Workbench User	•	•	•		TABLE
LanguageCode_Enum	Diversity Workbench User					TABLE
PlaceType_Enum	Diversity Workbench User					TABLE
ProjectProxy	Diversity Workbench User					TABLE
ProjectUser	Diversity Workbench User					TABLE
UserProxy	Diversity Workbench User		Diversity Workbench User			TABLE
GazetteerNameIDCoordinates	Diversity Workbench User					VIEW
GeoPlaceName	Diversity Workbench User					VIEW
GeoPlaceNameCountryCoordinates						VIEW
GeoPlaceNameIDCountry	Diversity Workbench User					VIEW
ViewBaseURL	Diversity Workbench User					VIEW
ViewDiversityWorkbenchModule	Diversity Workbench User					VIEW
ViewGeoPlace	Diversity Workbench User					VIEW
ViewHierarchy	Diversity Workbench User					VIEW
ViewTK25	Diversity Workbench User					VIEW
BaseURL					Diversity Workbench User	FUNCTION
DiversityWorkbenchModule					Diversity Workbench User	FUNCTION
HierarchyIncludeContinents					Diversity Workbench User	FUNCTION
HierarchySequenceTopDown					Diversity Workbench User	FUNCTION
NameSuperior	Diversity Workbench User					FUNCTION
NameSuperiorList	Diversity Workbench User					FUNCTION
PlaceSuperiorList						FUNCTION
PrivacyConsentInfo					Diversity Workbench User	FUNCTION
UserID					Diversity Workbench User	FUNCTION
UserName					Diversity Workbench User	FUNCTION
Version					Diversity Workbench User	FUNCTION
VersionClient					Diversity Workbench User	FUNCTION
RefreshGeoCache					•	PROCEDURE
RefreshGeoCacheAddHierarchyList						PROCEDURE
SetHierarchyCache						PROCEDURE
SetHierarchyCacheAll					•	PROCEDURE
SetUserProjects						PROCEDURE

Inheriting from roles:

• DiversityWorkbenchUser






DiversityWorkbenchUser

Role with read only permissions



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
ExternalDatabase	•					TABLE
GeoCache	•					TABLE
GeoName	•					TABLE
GeoName_log						TABLE
GeoNameLanguage	•					TABLE
GeoPlace	•					TABLE
GeoPlace_log						TABLE
GeoPlaceType	•					TABLE
GeoProject	•					TABLE
LanguageCode_Enum	•					TABLE
PlaceType_Enum	•					TABLE
ProjectProxy	•					TABLE
ProjectUser	•					TABLE
UserProxy	•		•			TABLE
GazetteerNameIDCoordinates	•					VIEW
GeoPlaceName	•					VIEW
GeoPlaceNameCountryCoordinates						VIEW
GeoPlaceNameIDCountry	•					VIEW
ViewBaseURL	•					VIEW
ViewDiversityWorkbenchModule	•					VIEW
ViewGeoPlace	•					VIEW
ViewHierarchy	•					VIEW
ViewTK25	•					VIEW
BaseURL					•	FUNCTION
DiversityWorkbenchModule					•	FUNCTION
HierarchyIncludeContinents					•	FUNCTION
HierarchySequenceTopDown					•	FUNCTION
NameSuperior	•					FUNCTION
NameSuperiorList	•					FUNCTION
PlaceSuperiorList						FUNCTION
PrivacyConsentInfo					•	FUNCTION
UserID					•	FUNCTION
UserName					•	FUNCTION
Version					•	FUNCTION
VersionClient					•	FUNCTION
RefreshGeoCache						PROCEDURE
RefreshGeoCacheAddHierarchyList						PROCEDURE
SetHierarchyCache						PROCEDURE
SetHierarchyCacheAll						PROCEDURE
SetUserProjects						PROCEDURE