/* SQL DDL: Create an implementation of the model using the SQL Data Definition Language */ /* Note: Comments such as this may not be supported by your system and may have to be removed */ /* You may want to change some field types to improve the implementation on your DBMS. */ /* Data type 'National Character' etc. indicates Unicode text. /* Note: Tested with MS SQL Server 2000 and PostgreSQL. Originally this file attempted to use */ /* Standard SQL for both. However, a common denominator could not be found. A special PostgreSQL file */ /* named '...SQL.Postgres' instead of '...SQL.txt' is now created in addition to the current file. */ /* The current file aims at SQL-Server dialects (but uses SQL92 whereever possible). The following changes may have to be made: */ /* Standard SQL 92: replace the data type BIT with SQL92 BOOLEAN */ /* MS SQL Server 2000: replace the SQL92 constant 'current_timestamp' with 'GETDATE()' (= date and time) */ /* MS SQL Server 2000: remove comments around the block of 'exec' statements at end of file to enable creation of column descriptions */ /* Note: CREATE INDEX statements (other than primary, foreign, or unique key constraints) are missing at the moment; to be added later */ /* Generated on 28.Sep.2003 9:54 by Diversity Information Model Documenter 2.5 (G. Hagedorn) */ /*=== Table: GeoPlaceName_Africa ===*/ /* Place name, preferred name, place type and hierarchy in several languages. The PlaceNameID can be used to further analyze the geographical place in GeoNameAnalysis and GeoPlaceAnalysis. */ /* NameID: ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!). */ /* PlaceName: A place name in one of the supported languages. */ /* Preferred_PlaceName: The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName. */ /* PlaceTypeID: Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID). */ /* HierarchyList: A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names. */ /* HierarchyTopID: The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key). */ CREATE TABLE GeoPlaceName_Africa ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceName NATIONAL CHARACTER VARYING(122) NOT NULL, Preferred_PlaceName NATIONAL CHARACTER VARYING(122) NULL, PlaceTypeID SMALLINT NULL, HierarchyList NATIONAL CHARACTER VARYING(255) NULL, HierarchyTopID SMALLINT NULL ); /*=== Table: GeoPlaceName_AmNC ===*/ /* ... for North and Central America. */ /* NameID: ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!). */ /* PlaceName: A place name in one of the supported languages. */ /* Preferred_PlaceName: The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName. */ /* PlaceTypeID: Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID). */ /* HierarchyList: A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names. */ /* HierarchyTopID: The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key). */ CREATE TABLE GeoPlaceName_AmNC ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceName NATIONAL CHARACTER VARYING(122) NOT NULL, Preferred_PlaceName NATIONAL CHARACTER VARYING(122) NULL, PlaceTypeID SMALLINT NULL, HierarchyList NATIONAL CHARACTER VARYING(255) NULL, HierarchyTopID SMALLINT NULL ); /*=== Table: GeoPlaceName_AmS ===*/ /* ... for South America. */ /* NameID: ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!). */ /* PlaceName: A place name in one of the supported languages. */ /* Preferred_PlaceName: The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName. */ /* PlaceTypeID: Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID). */ /* HierarchyList: A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names. */ /* HierarchyTopID: The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key). */ CREATE TABLE GeoPlaceName_AmS ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceName NATIONAL CHARACTER VARYING(122) NOT NULL, Preferred_PlaceName NATIONAL CHARACTER VARYING(122) NULL, PlaceTypeID SMALLINT NULL, HierarchyList NATIONAL CHARACTER VARYING(255) NULL, HierarchyTopID SMALLINT NULL ); /*=== Table: GeoPlaceName_Asia ===*/ /* ... for Asia. */ /* NameID: ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!). */ /* PlaceName: A place name in one of the supported languages. */ /* Preferred_PlaceName: The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName. */ /* PlaceTypeID: Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID). */ /* HierarchyList: A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names. */ /* HierarchyTopID: The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key). */ CREATE TABLE GeoPlaceName_Asia ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceName NATIONAL CHARACTER VARYING(122) NOT NULL, Preferred_PlaceName NATIONAL CHARACTER VARYING(122) NULL, PlaceTypeID SMALLINT NULL, HierarchyList NATIONAL CHARACTER VARYING(255) NULL, HierarchyTopID SMALLINT NULL ); /*=== Table: GeoPlaceName_Euro ===*/ /* ... for Europe. */ /* NameID: ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!). */ /* PlaceName: A place name in one of the supported languages. */ /* Preferred_PlaceName: The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName. */ /* PlaceTypeID: Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID). */ /* HierarchyList: A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names. */ /* HierarchyTopID: The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key). */ CREATE TABLE GeoPlaceName_Euro ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceName NATIONAL CHARACTER VARYING(122) NOT NULL, Preferred_PlaceName NATIONAL CHARACTER VARYING(122) NULL, PlaceTypeID SMALLINT NULL, HierarchyList NATIONAL CHARACTER VARYING(255) NULL, HierarchyTopID SMALLINT NULL ); /*=== Table: GeoPlaceName_OcAnt ===*/ /* ... for Oceania and Antarctica. */ /* NameID: ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!). */ /* PlaceName: A place name in one of the supported languages. */ /* Preferred_PlaceName: The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName. */ /* PlaceTypeID: Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID). */ /* HierarchyList: A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names. */ /* HierarchyTopID: The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key). */ CREATE TABLE GeoPlaceName_OcAnt ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceName NATIONAL CHARACTER VARYING(122) NOT NULL, Preferred_PlaceName NATIONAL CHARACTER VARYING(122) NULL, PlaceTypeID SMALLINT NULL, HierarchyList NATIONAL CHARACTER VARYING(255) NULL, HierarchyTopID SMALLINT NULL ); /*=== Table: GeoNameAnalysis ===*/ /* A translation of internal DiversityGazetteer name IDs to place IDs (each place may have several names) and to name IDs in external databases (compare GeoExternalDatabase). */ /* NameID: The ID of a name in the DiversityGazetteer (foreign key + part of primary key). */ /* PlaceID: The ID of a place in the DiversityGazetteer (foreign key). */ /* ExternalDatabaseID: The ID of an external geographical data collection as defined in GeoExternalDatabase (foreign key). */ /* ExternalNameID: An ID number for a place name as defined in the external database (foreign key to separate module). */ CREATE TABLE GeoNameAnalysis ( NameID INTEGER NOT NULL PRIMARY KEY, PlaceID INTEGER NOT NULL, ExternalDatabaseID TINYINT NOT NULL, ExternalNameID INTEGER NULL ); /*=== Table: GeoPlaceAnalysis ===*/ /* Geographical coordinates for places and place IDs in external databases (compare GeoExternalDatabase). */ /* PlaceID: ID within the DiversityGazetteer for a place (which may have several names). */ /* NumLat: Latitude as decimal number (double precision floating point, S negative, N positiv). */ /* NumLong: Longitude as decimal number (double precision floating point, W negative, E positive). */ /* ExternalDatabaseID: The ID of an external geographical data collection as defined in GeoExternalDatabase (foreign key + part of primary key). */ /* ExternalPlaceID: An ID number for geographical place (which may have several names) as defined in the external database. */ /* StateOrProvince: PlaceID of federal state or province (where available) // Currently a TopID, use GeoTopHierarchy table to find PlaceID! May later be changed, after checking associations in GeoTopHierarchy! */ /* Country: PlaceID of political country // Currently a TopID, see above! */ /* Region: PlaceID of region or continent // Currently a TopID, see above! */ CREATE TABLE GeoPlaceAnalysis ( PlaceID INTEGER NOT NULL PRIMARY KEY, NumLat FLOAT NULL, NumLong FLOAT NULL, ExternalDatabaseID TINYINT NOT NULL, ExternalPlaceID INTEGER NULL, StateOrProvince INTEGER NULL, Country INTEGER NULL, Region INTEGER NULL ); /*=== Table: GeoPlaceType ===*/ /* A list of place types used in the PlaceName table. Place types help to identify places correctly and distinguish between homonymous place names. */ /* PlaceTypeID: Internal ID for a place type (primary key). The ID has no meaning outside the DiversityGazetteer, i.e. it is not identical with an ID in a source data collection. */ /* PlaceType: Description or name of a place type. */ /* DisplayEnable: The place type name is displayed in the default picklist if marked here. */ CREATE TABLE GeoPlaceType ( PlaceTypeID SMALLINT NOT NULL PRIMARY KEY, PlaceType NATIONAL CHARACTER VARYING(64) NOT NULL UNIQUE, DisplayEnable BIT NOT NULL DEFAULT -1 ); /*=== Table: GeoTopHierarchy ===*/ /* The highest geographical elements are normalized into this separate table. All are present in the PlaceName table, but joining with that table would provide very bad performance. */ /* TopID: An ID for a place used in the geographical hierarchy of place names (primary key). */ /* NameOri: The name in the preferred (mostly local) language. */ /* NameEn: The name in English. */ /* CountryTopID: If TopID refers to a state within a federal state (e.g. for U.S. and Germany) this ID refers to the true CountryID. Field is Null if Name itself is a country! */ /* RegionTopID: The region as TopID (North & Central, South America, Europe, Asia, Africa, Oceania, Antarctica). */ /* PlaceID: GeoPlaceAnalysis.PlaceID corresponding to TopID (short integer). An association with the TGN place IDs is preferred; associations prefer nations over region. Historic problems may have to be checked, please inform us about problems you encounter! */ CREATE TABLE GeoTopHierarchy ( TopID SMALLINT NOT NULL PRIMARY KEY, NameOri NATIONAL CHARACTER VARYING(255) NOT NULL UNIQUE, NameEn NATIONAL CHARACTER VARYING(255) NOT NULL UNIQUE, CountryTopID SMALLINT NULL, RegionTopID SMALLINT NULL, PlaceID INTEGER NULL ); /*=== Table: GeoExternalDatabase ===*/ /* Description of data collections (Getty TGN, GN250, etc.) from which DiversityGazetteer names originate or which have been linked to these names. */ /* ExternalDatabaseID: An ID to identify an external data collection of geographical names, places, or information (primary key, the ID has no meaning outside of the DiversityGazetteer). */ /* ExternalDatabaseName: The name of the data collection that has been integrated or can be linked to for further analysis. */ /* ExternalDatabaseVersion: The version of this data collection (either official version number, or dates when the collection was integrated). */ /* ExternalAttribute_NameID: The table and field name to which GeoNameAnalysis.ExternalNameID refers in the external data collection. */ /* ExternalAttribute_PlaceID: The table and field name to which GeoPlaceAnalysis.ExternalPlaceID refers in the external data collection. */ /* ExternalCoordinatePrecision: Free-text description of the precision and method with which coordinates are provided by external database. */ /* InternalNotes: Additional notes concerning this data collection. */ CREATE TABLE GeoExternalDatabase ( ExternalDatabaseID TINYINT NOT NULL PRIMARY KEY, ExternalDatabaseName NATIONAL CHARACTER VARYING(60) NOT NULL, ExternalDatabaseVersion NATIONAL CHARACTER VARYING(255) NOT NULL, ExternalAttribute_NameID NATIONAL CHARACTER VARYING(255) NULL, ExternalAttribute_PlaceID NATIONAL CHARACTER VARYING(255) NULL, ExternalCoordinatePrecision NATIONAL CHARACTER VARYING(255) NULL, InternalNotes NATIONAL TEXT NULL ); /*=== Table: GeoString_Enums ===*/ /* Generic collection of enumerations and descriptions of objects which are identified through a text code. Such enumeration are frequently used to translate code values to user-readable text in reports and pick lists. */ /* Enumeration: A text string defining a group of values (= enumeration) for a specific use (part of primary key). */ /* Code: A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. */ /* Abbreviation: Short abbreviated description of the object, displayed in the user interface. */ /* Description: Description of enumerated object, displayed in the user interface. */ /* InternalNotes: Internal development notes about usage, definition, etc. of an enumerated object. */ /* DisplayOrder: The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. */ /* DisplayEnable: Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box). */ CREATE TABLE GeoString_Enums ( Enumeration NATIONAL CHARACTER VARYING(20) NOT NULL, Code NATIONAL CHARACTER VARYING(25) NOT NULL, Abbreviation NATIONAL CHARACTER VARYING(25) NOT NULL, Description NATIONAL CHARACTER VARYING(255) NOT NULL, InternalNotes NATIONAL CHARACTER VARYING(255) NULL, DisplayOrder INTEGER NOT NULL UNIQUE DEFAULT CLng(80000*(Now()-37225)), DisplayEnable BIT NOT NULL DEFAULT -1, PRIMARY KEY (Enumeration,Code) ); ALTER TABLE GeoPlaceName_Euro ADD FOREIGN KEY (NameID) REFERENCES GeoNameAnalysis (NameID), FOREIGN KEY (PlaceTypeID) REFERENCES GeoPlaceType (PlaceTypeID), FOREIGN KEY (HierarchyTopID) REFERENCES GeoTopHierarchy (TopID) ALTER TABLE GeoNameAnalysis ADD FOREIGN KEY (PlaceID) REFERENCES GeoPlaceAnalysis (PlaceID) ALTER TABLE GeoPlaceAnalysis ADD FOREIGN KEY (ExternalDatabaseID) REFERENCES GeoExternalDatabase (ExternalDatabaseID) /*** Remove this comment line to enable creation of column description for MS SQL Server 2000 *** exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!).',N'user',N'dbo',N'table',N'GeoPlaceName_Africa',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'A place name in one of the supported languages.',N'user',N'dbo',N'table',N'GeoPlaceName_Africa',N'column',N'PlaceName' exec sp_addextendedproperty N'MS_Description',N'The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName.',N'user',N'dbo',N'table',N'GeoPlaceName_Africa',N'column',N'Preferred_PlaceName' exec sp_addextendedproperty N'MS_Description',N'Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID).',N'user',N'dbo',N'table',N'GeoPlaceName_Africa',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names.',N'user',N'dbo',N'table',N'GeoPlaceName_Africa',N'column',N'HierarchyList' exec sp_addextendedproperty N'MS_Description',N'The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key).',N'user',N'dbo',N'table',N'GeoPlaceName_Africa',N'column',N'HierarchyTopID' exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!).',N'user',N'dbo',N'table',N'GeoPlaceName_AmNC',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'A place name in one of the supported languages.',N'user',N'dbo',N'table',N'GeoPlaceName_AmNC',N'column',N'PlaceName' exec sp_addextendedproperty N'MS_Description',N'The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName.',N'user',N'dbo',N'table',N'GeoPlaceName_AmNC',N'column',N'Preferred_PlaceName' exec sp_addextendedproperty N'MS_Description',N'Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID).',N'user',N'dbo',N'table',N'GeoPlaceName_AmNC',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names.',N'user',N'dbo',N'table',N'GeoPlaceName_AmNC',N'column',N'HierarchyList' exec sp_addextendedproperty N'MS_Description',N'The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key).',N'user',N'dbo',N'table',N'GeoPlaceName_AmNC',N'column',N'HierarchyTopID' exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!).',N'user',N'dbo',N'table',N'GeoPlaceName_AmS',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'A place name in one of the supported languages.',N'user',N'dbo',N'table',N'GeoPlaceName_AmS',N'column',N'PlaceName' exec sp_addextendedproperty N'MS_Description',N'The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName.',N'user',N'dbo',N'table',N'GeoPlaceName_AmS',N'column',N'Preferred_PlaceName' exec sp_addextendedproperty N'MS_Description',N'Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID).',N'user',N'dbo',N'table',N'GeoPlaceName_AmS',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names.',N'user',N'dbo',N'table',N'GeoPlaceName_AmS',N'column',N'HierarchyList' exec sp_addextendedproperty N'MS_Description',N'The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key).',N'user',N'dbo',N'table',N'GeoPlaceName_AmS',N'column',N'HierarchyTopID' exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!).',N'user',N'dbo',N'table',N'GeoPlaceName_Asia',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'A place name in one of the supported languages.',N'user',N'dbo',N'table',N'GeoPlaceName_Asia',N'column',N'PlaceName' exec sp_addextendedproperty N'MS_Description',N'The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName.',N'user',N'dbo',N'table',N'GeoPlaceName_Asia',N'column',N'Preferred_PlaceName' exec sp_addextendedproperty N'MS_Description',N'Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID).',N'user',N'dbo',N'table',N'GeoPlaceName_Asia',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names.',N'user',N'dbo',N'table',N'GeoPlaceName_Asia',N'column',N'HierarchyList' exec sp_addextendedproperty N'MS_Description',N'The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key).',N'user',N'dbo',N'table',N'GeoPlaceName_Asia',N'column',N'HierarchyTopID' exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!).',N'user',N'dbo',N'table',N'GeoPlaceName_Euro',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'A place name in one of the supported languages.',N'user',N'dbo',N'table',N'GeoPlaceName_Euro',N'column',N'PlaceName' exec sp_addextendedproperty N'MS_Description',N'The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName.',N'user',N'dbo',N'table',N'GeoPlaceName_Euro',N'column',N'Preferred_PlaceName' exec sp_addextendedproperty N'MS_Description',N'Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID).',N'user',N'dbo',N'table',N'GeoPlaceName_Euro',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names.',N'user',N'dbo',N'table',N'GeoPlaceName_Euro',N'column',N'HierarchyList' exec sp_addextendedproperty N'MS_Description',N'The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key).',N'user',N'dbo',N'table',N'GeoPlaceName_Euro',N'column',N'HierarchyTopID' exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer (only for the Getty TGN this is identical with the external ID, always use the analysis table to find coordinates, source, and original ID!).',N'user',N'dbo',N'table',N'GeoPlaceName_OcAnt',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'A place name in one of the supported languages.',N'user',N'dbo',N'table',N'GeoPlaceName_OcAnt',N'column',N'PlaceName' exec sp_addextendedproperty N'MS_Description',N'The preferred place name, usually in the local language (e.g. Deutschland instead of Germany). Empty if PlaceName = Preferred_PlaceName.',N'user',N'dbo',N'table',N'GeoPlaceName_OcAnt',N'column',N'Preferred_PlaceName' exec sp_addextendedproperty N'MS_Description',N'Number referring to a list of defined place types (foreign key, PlaceType.PlaceTypeID).',N'user',N'dbo',N'table',N'GeoPlaceName_OcAnt',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'A list defining the hierarchy of geographical places above the current place, necessary to distinguish synonymous place names.',N'user',N'dbo',N'table',N'GeoPlaceName_OcAnt',N'column',N'HierarchyList' exec sp_addextendedproperty N'MS_Description',N'The highest element of the hierarchy is identified through an ID to save storage space and allow simple analysis queries (foreign key).',N'user',N'dbo',N'table',N'GeoPlaceName_OcAnt',N'column',N'HierarchyTopID' exec sp_addextendedproperty N'MS_Description',N'The ID of a name in the DiversityGazetteer (foreign key + part of primary key).',N'user',N'dbo',N'table',N'GeoNameAnalysis',N'column',N'NameID' exec sp_addextendedproperty N'MS_Description',N'The ID of a place in the DiversityGazetteer (foreign key).',N'user',N'dbo',N'table',N'GeoNameAnalysis',N'column',N'PlaceID' exec sp_addextendedproperty N'MS_Description',N'The ID of an external geographical data collection as defined in GeoExternalDatabase (foreign key).',N'user',N'dbo',N'table',N'GeoNameAnalysis',N'column',N'ExternalDatabaseID' exec sp_addextendedproperty N'MS_Description',N'An ID number for a place name as defined in the external database (foreign key to separate module).',N'user',N'dbo',N'table',N'GeoNameAnalysis',N'column',N'ExternalNameID' exec sp_addextendedproperty N'MS_Description',N'ID within the DiversityGazetteer for a place (which may have several names).',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'PlaceID' exec sp_addextendedproperty N'MS_Description',N'Latitude as decimal number (double precision floating point, S negative, N positiv).',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'NumLat' exec sp_addextendedproperty N'MS_Description',N'Longitude as decimal number (double precision floating point, W negative, E positive).',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'NumLong' exec sp_addextendedproperty N'MS_Description',N'The ID of an external geographical data collection as defined in GeoExternalDatabase (foreign key + part of primary key).',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'ExternalDatabaseID' exec sp_addextendedproperty N'MS_Description',N'An ID number for geographical place (which may have several names) as defined in the external database.',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'ExternalPlaceID' exec sp_addextendedproperty N'MS_Description',N'PlaceID of federal state or province (where available) // Currently a TopID, use GeoTopHierarchy table to find PlaceID! May later be changed, after checking associations in GeoTopHierarchy!',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'StateOrProvince' exec sp_addextendedproperty N'MS_Description',N'PlaceID of political country // Currently a TopID, see above!',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'Country' exec sp_addextendedproperty N'MS_Description',N'PlaceID of region or continent // Currently a TopID, see above!',N'user',N'dbo',N'table',N'GeoPlaceAnalysis',N'column',N'Region' exec sp_addextendedproperty N'MS_Description',N'Internal ID for a place type (primary key). The ID has no meaning outside the DiversityGazetteer, i.e. it is not identical with an ID in a source data collection.',N'user',N'dbo',N'table',N'GeoPlaceType',N'column',N'PlaceTypeID' exec sp_addextendedproperty N'MS_Description',N'Description or name of a place type.',N'user',N'dbo',N'table',N'GeoPlaceType',N'column',N'PlaceType' exec sp_addextendedproperty N'MS_Description',N'The place type name is displayed in the default picklist if marked here.',N'user',N'dbo',N'table',N'GeoPlaceType',N'column',N'DisplayEnable' exec sp_addextendedproperty N'MS_Description',N'An ID for a place used in the geographical hierarchy of place names (primary key).',N'user',N'dbo',N'table',N'GeoTopHierarchy',N'column',N'TopID' exec sp_addextendedproperty N'MS_Description',N'The name in the preferred (mostly local) language.',N'user',N'dbo',N'table',N'GeoTopHierarchy',N'column',N'NameOri' exec sp_addextendedproperty N'MS_Description',N'The name in English.',N'user',N'dbo',N'table',N'GeoTopHierarchy',N'column',N'NameEn' exec sp_addextendedproperty N'MS_Description',N'If TopID refers to a state within a federal state (e.g. for U.S. and Germany) this ID refers to the true CountryID. Field is Null if Name itself is a country!',N'user',N'dbo',N'table',N'GeoTopHierarchy',N'column',N'CountryTopID' exec sp_addextendedproperty N'MS_Description',N'The region as TopID (North & Central, South America, Europe, Asia, Africa, Oceania, Antarctica).',N'user',N'dbo',N'table',N'GeoTopHierarchy',N'column',N'RegionTopID' exec sp_addextendedproperty N'MS_Description',N'GeoPlaceAnalysis.PlaceID corresponding to TopID (short integer). An association with the TGN place IDs is preferred; associations prefer nations over region. Historic problems may have to be checked, please inform us about problems you encounter!',N'user',N'dbo',N'table',N'GeoTopHierarchy',N'column',N'PlaceID' exec sp_addextendedproperty N'MS_Description',N'An ID to identify an external data collection of geographical names, places, or information (primary key, the ID has no meaning outside of the DiversityGazetteer).',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'ExternalDatabaseID' exec sp_addextendedproperty N'MS_Description',N'The name of the data collection that has been integrated or can be linked to for further analysis.',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'ExternalDatabaseName' exec sp_addextendedproperty N'MS_Description',N'The version of this data collection (either official version number, or dates when the collection was integrated).',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'ExternalDatabaseVersion' exec sp_addextendedproperty N'MS_Description',N'The table and field name to which GeoNameAnalysis.ExternalNameID refers in the external data collection.',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'ExternalAttribute_NameID' exec sp_addextendedproperty N'MS_Description',N'The table and field name to which GeoPlaceAnalysis.ExternalPlaceID refers in the external data collection.',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'ExternalAttribute_PlaceID' exec sp_addextendedproperty N'MS_Description',N'Free-text description of the precision and method with which coordinates are provided by external database.',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'ExternalCoordinatePrecision' exec sp_addextendedproperty N'MS_Description',N'Additional notes concerning this data collection.',N'user',N'dbo',N'table',N'GeoExternalDatabase',N'column',N'InternalNotes' exec sp_addextendedproperty N'MS_Description',N'A text string defining a group of values (= enumeration) for a specific use (part of primary key).',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'Enumeration' exec sp_addextendedproperty N'MS_Description',N'A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it.',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'Code' exec sp_addextendedproperty N'MS_Description',N'Short abbreviated description of the object, displayed in the user interface.',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'Abbreviation' exec sp_addextendedproperty N'MS_Description',N'Description of enumerated object, displayed in the user interface.',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'Description' exec sp_addextendedproperty N'MS_Description',N'Internal development notes about usage, definition, etc. of an enumerated object.',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'InternalNotes' exec sp_addextendedproperty N'MS_Description',N'The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'DisplayOrder' exec sp_addextendedproperty N'MS_Description',N'Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box).',N'user',N'dbo',N'table',N'GeoString_Enums',N'column',N'DisplayEnable' GO *** Remove this comment line to enable creation of column description for MS SQL Server 2000 ***/