/* 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./*   PostgreSQL: You should create the entire database as Unicode!  */
/*   PostgreSQL: For Postgres you should create the entire database as Unicode! The 'National' has been removed from the SQL code */
/*   PostgreSQL: the data type CHARACTER VARYING has been replaced with VARCHAR */
/*   PostgreSQL: the data type DATETIME has been replaced with TIMESTAMP */
/*   PostgreSQL: Table and field names have been quoted (= "Fieldname") to allow case-sensitivity */
/*   PostgreSQL: the default values for boolean fields have been replaced with 'true' and 'false' */



/*=== 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceName" 	varchar(122)	 NOT NULL,
    "Preferred_PlaceName" 	varchar(122)	 NULL,
    "PlaceTypeID" 	int2	 NULL,
    "HierarchyList" 	varchar(255)	 NULL,
    "HierarchyTopID" 	int2	 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceName" 	varchar(122)	 NOT NULL,
    "Preferred_PlaceName" 	varchar(122)	 NULL,
    "PlaceTypeID" 	int2	 NULL,
    "HierarchyList" 	varchar(255)	 NULL,
    "HierarchyTopID" 	int2	 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceName" 	varchar(122)	 NOT NULL,
    "Preferred_PlaceName" 	varchar(122)	 NULL,
    "PlaceTypeID" 	int2	 NULL,
    "HierarchyList" 	varchar(255)	 NULL,
    "HierarchyTopID" 	int2	 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceName" 	varchar(122)	 NOT NULL,
    "Preferred_PlaceName" 	varchar(122)	 NULL,
    "PlaceTypeID" 	int2	 NULL,
    "HierarchyList" 	varchar(255)	 NULL,
    "HierarchyTopID" 	int2	 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceName" 	varchar(122)	 NOT NULL,
    "Preferred_PlaceName" 	varchar(122)	 NULL,
    "PlaceTypeID" 	int2	 NULL,
    "HierarchyList" 	varchar(255)	 NULL,
    "HierarchyTopID" 	int2	 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceName" 	varchar(122)	 NOT NULL,
    "Preferred_PlaceName" 	varchar(122)	 NULL,
    "PlaceTypeID" 	int2	 NULL,
    "HierarchyList" 	varchar(255)	 NULL,
    "HierarchyTopID" 	int2	 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" 	int4	 NOT NULL PRIMARY KEY,
    "PlaceID" 	int4	 NOT NULL,
    "ExternalDatabaseID" 	int2	 NOT NULL,
    "ExternalNameID" 	int4	 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" 	int4	 NOT NULL PRIMARY KEY,
    "NumLat" 	float8	 NULL,
    "NumLong" 	float8	 NULL,
    "ExternalDatabaseID" 	int2	 NOT NULL,
    "ExternalPlaceID" 	int4	 NULL,
    "StateOrProvince" 	int4	 NULL,
    "Country" 	int4	 NULL,
    "Region" 	int4	 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" 	int2	 NOT NULL PRIMARY KEY,
    "PlaceType" 	varchar(64)	 NOT NULL UNIQUE,
    "DisplayEnable" 	bool	 NOT NULL DEFAULT 'true'
);




/*=== 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" 	int2	 NOT NULL PRIMARY KEY,
    "NameOri" 	varchar(255)	 NOT NULL UNIQUE,
    "NameEn" 	varchar(255)	 NOT NULL UNIQUE,
    "CountryTopID" 	int2	 NULL,
    "RegionTopID" 	int2	 NULL,
    "PlaceID" 	int4	 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" 	int2	 NOT NULL PRIMARY KEY,
    "ExternalDatabaseName" 	varchar(60)	 NOT NULL,
    "ExternalDatabaseVersion" 	varchar(255)	 NOT NULL,
    "ExternalAttribute_NameID" 	varchar(255)	 NULL,
    "ExternalAttribute_PlaceID" 	varchar(255)	 NULL,
    "ExternalCoordinatePrecision" 	varchar(255)	 NULL,
    "InternalNotes" 	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" 	varchar(20)	 NOT NULL,
    "Code" 	varchar(25)	 NOT NULL,
    "Abbreviation" 	varchar(25)	 NOT NULL,
    "Description" 	varchar(255)	 NOT NULL,
    "InternalNotes" 	varchar(255)	 NULL,
    "DisplayOrder" 	int4	 NOT NULL UNIQUE DEFAULT CLng(80000*(Now()-37225)),
    "DisplayEnable" 	bool	 NOT NULL DEFAULT 'true',
  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")