Database Diagram - danmarksmiljoeportal/DkJord GitHub Wiki
This page explains all data tables and columns in the DKJord database.
Table: [dbo].[BranchGroupCode]
Purpose: Specifies the type of industry that is occurring on the location.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [PollutionCauseID_SYS] | uniqueidentifier | No | x | x | Reference to the PollutionCause that the BranchGroupCode is set for |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [BranchGroupCodeValue] | char(10) | No | x | x | Options to set for the stated CodeType; e.g. "52.00.00 - Detailhandel undtagen med biler mv.; reparationsvirksomhed", "01.00.00 - Landbrug, jagt mv.", "51.00.00 - Engroshandel og agenturhandel undtagen med biler mv." |
| [BranchGroupCodeType] | int | No | x | Hardcoded to "25", which refers to "Branchekode [Intern-2]" | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[CodeTypes]
Purpose: List of different code (metadata) arrays used to define data in various other tables in the database. Many of these code arrays can also be found as Stancode/Standat lists.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [CodeType] | int | No | x | The unique identifier that is used by various objects to add a metadata value list to their records | |
| [Description] | nvarchar(255) | Yes | Textual description of the code type record | ||
| [ValidationPattern] | varchar(255) | Yes | Regex expression that can be used for a quick validation of input parameter |
Table: [dbo].[CodeValues]
Purpose: List of code (options) for each of the code arrays in the CodeTypes table.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [CodeID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [CodeType] | int | No | x | The code type that contains a set of possible metadata options | |
| [CodeValue] | char(10) | No | Options available to set for the stated CodeType | ||
| [Description] | nvarchar(max) | Yes | Textual description of the code value record | ||
| [ExpirationDate] | datetime | Yes | A date after which this code value can no longer be used | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ChangedDate] | datetime | No | Date that this record was last modified | ||
| [ChangedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ChangedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[CurrentTargetArea]
Purpose: The types of areas that are being focused on for the location. It uses a CodeTypes/CodeValues array to describe data that originates from Standat 208 list.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [LocationID_SYS] | uniqueidentifier | No | x | x | Reference to the location that this record belongs to |
| [CodeType] | int | No | x | x | Hardcoded to "9", which refers to "Indsatsområde [00208]" |
| [CodeValue] | char(10) | No | x | x | Options to set for the stated CodeType; e.g. "01 - Bolig", "02 - Børneinstitution", "03 - Offentlig legeplads" |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[Location]
Purpose: Holds the main entity of DKJord, which is Locations. Locations are basically geometric areas on which soil contamination analysis is undertaken, qualified, and evaluated.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [LocationID_SYS] | uniqueidentifier | No | x | Unique key of the location | |
| [LocationReference] | char(9) | No | Distinct Reference for a Location. Typical format e.g. {745-00084}. A Location is a Jord-specific term used to refer to a specific area within a Region. | ||
| [LocationName] | varchar(2000) | Yes | Userfriendly Name for a Location. Typical format e.g. {Kobbersmedje i Rebild}. A Location is a Jord-specific term used to refer to a specific area within a Region. | ||
| [RegionCode] | int | Yes | Reference to the Danish Region that the context record belongs to. Validated against the [Regions] table. | ||
| [MunicipalityCode] | int | Yes | A reference the the Danish Municipality to which the context record belongs. Validated against the [SYS_MunicipalityRegion] table. | ||
| [StreetCode] | int | Yes | All streets in Denmark have unique codes. When an address is identified in connection with the location, the code of the street is entered here. | ||
| [StreetBuildingIdentifier] | char(4) | Yes | All buildings in Denmark have unique house numbers. When an address is identified in connection with the location, the number of the house is entered here. | ||
| [AddressIdentifier] | uniqueidentifier | Yes | Not in use | ||
| [LocationDescriptionText] | nvarchar(2000) | Yes | A geographic description of the location, typically the address | ||
| [ReadyForReportingIndicator] | bit | Yes | When set to true, the location can be reported on | ||
| [PollutionStatusCodeValue] | char(10) | Yes | x | Options to set for the stated CodeType; e.g. "07 - V1 kortlagt", "08 - V2 kortlagt", "10 - Ryddet op inden kortlægning (registrering)" | |
| [PollutionStatusCodeType] | int | No | x | Hardcoded to "1", which refers to "Status for forurenede arealer [00159]" | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record | ||
| [IsBornholm] | bit | Yes | Set to True, when the location is on the island of Bornholm | ||
| [NotReadyForReportingMessages] | varchar(max) | Yes | Not in use |
Table: [dbo].[LocationTheme]
Purpose: Groups geometries into "themes", which are basically different soil contamination qualifications. Table is used by the two tables StatusDeclarationXGeometry and ProjectPhaseXGeometry.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ID] | int | No | x | Unique identifier of this record | |
| [ThemeCode] | smallint | No | A code from Danmarks Arealinfo that groups exposed geometries together in themes | ||
| [ThemeName] | varchar(128) | No | The name of the context theme | ||
| [Description] | nvarchar(128) | No | A description of the context theme |
Table: [dbo].[LocationXGeometryHistory]
Purpose: Array of current and historical areas (shapes/polygons) that Locations consist of.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [GeometryHistoryID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [LocationReference] | char(9) | No | Distinct Reference for a Location geometry. Typical format e.g. {745-00084}. A Location is a Jord-specific term used to refer to a specific area within a Region. | ||
| [RegionCode] | int | No | Reference to the Danish Region that the context record belongs to. Validated against the [Regions] table. | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record | ||
| [ArchivedDate] | datetime | No | Date that this record was archived | ||
| [ArchivedUser] | varchar(300) | No | Username of the user who archived this record | ||
| [ArchivedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last archived this record | ||
| [Shape] | geometry | Yes | The geometry (geospatial area) - typically a polygon |
Table: [dbo].[LocationXPollutantComponent]
Purpose: Holds information of the pollutant components that are found on a location.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [LocationID_SYS] | uniqueidentifier | No | x | x | PK/FK in the many to many relationship between Location and Pollutant Component |
| [PollutantComponentID_SYS] | uniqueidentifier | No | x | x | PK/FK in the many to many relationship between Location and Pollutant Component |
Table: [dbo].[Parcel]
Purpose: Contains information of soil contamination on Parcels. Parcels are identified by Cadastral District Identifier and Land Parcel Identifier.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ParcelID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [CadastralDistrictIdentifier] | int | Yes | A unique value for a cadastral district (a.k.a. ejerlavskode) | ||
| [LandParcelIdentifier] | nvarchar(50) | Yes | A unique parcel value within the cadastral district (a.k.a. matrikelnummer) | ||
| [HousingStatementIndicator] | bit | Yes | Bool setting indicating whether a building exists on the Parcel | ||
| [PollutionStatusCodeType] | int | Yes | x | Hardcoded to "1", which refers to "Status for forurenede arealer [00159]" | |
| [PollutionStatusCodeValue] | char(10) | Yes | x | Options to set for the stated CodeType; e.g. "07 - V1 kortlagt", "08 - V2 kortlagt", "10 - Ryddet op inden kortlægning (registrering)" | |
| [PollutionPartial] | bit | Yes | Boolean value explaning whether pollution covers the entire Parcel or only parts of it | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record | ||
| [PollutionNuanceStatus] | char(10) | Yes | Value referring to what the pollution nuance status of the location is. E.g. '01' refers to "F0 Nuanceret" | ||
| [RecalculationDate] | datetime | Yes | The date of when the responsible Region of the Parcel made the decision of the pollutionStatusCode and pollutionNuanceStatusCode which have been given to the Parcel | ||
| [RegionCode] | int | Yes | Reference to the Danish Region that the context record belongs to. Validated against the [Regions] table. | ||
| [MunicipalityCode] | int | Yes | A reference the the Danish Municipality to which the context record belongs. Validated against the [SYS_MunicipalityRegion] table. |
Table: [dbo].[ParcelHistory]
Purpose: Contains historical data originally added to the Parcel table. Once information of a Parcel changes, its earlier state is saved as a new record to the ParcelHistory table.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [Id] | uniqueidentifier | No | x | Unique identifier of this record | |
| [ParcelID_SYS] | uniqueidentifier | No | x | The original unique identifier of the Parcel | |
| [CadastralDistrictIdentifier] | int | Yes | A unique value for a cadastral district (a.k.a. ejerlavskode) | ||
| [LandParcelIdentifier] | nvarchar(50) | Yes | A unique parcel value within the cadastral district (a.k.a. matrikelnummer) | ||
| [HousingStatementIndicator] | bit | Yes | Bool setting indicating whether a building exists on the Parcel | ||
| [PollutionStatusCodeType] | int | No | x | Hardcoded to "1", which refers to "Status for forurenede arealer [00159]" | |
| [PollutionStatusCodeValue] | char(10) | Yes | x | Options to set for the stated CodeType; e.g. "07 - V1 kortlagt", "08 - V2 kortlagt", "10 - Ryddet op inden kortlægning (registrering)" | |
| [PollutionPartial] | bit | Yes | Boolean value explaning whether pollution covers the entire Parcel or only parts of it | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record | ||
| [PollutionNuanceStatus] | char(10) | Yes | Value referring to what the pollution nuance status of the location is. E.g. '01' refers to "F0 Nuanceret" | ||
| [ParcelXStatusDeclarations] | nvarchar(max) | Yes | The array of Status Declarations that the Parcel belonged to (by their unique references) | ||
| [RecalculationDate] | datetime | Yes | The date of when the responsible Region of the Parcel made the decision of the pollutionStatusCode and pollutionNuanceStatusCode which have been given to the Parcel | ||
| [RegionCode] | int | Yes | Reference to the Danish Region that the context record belongs to. Validated against the [Regions] table. | ||
| [MunicipalityCode] | int | Yes | A reference the the Danish Municipality to which the context record belongs. Validated against the [SYS_MunicipalityRegion] table. |
Table: [dbo].[ParcelXStatusDeclaration]
Purpose: Shows the relation between contaminated areas of locations and parcels and thus the reason why the Parcel has been qualified with a specific polution code. Notice that StatusDeclarationID_SYS is not in the database an actual Foreign Key, as it is allowed to hold "non-existing" references to the StatusDeclaration table. The reason for this is to accomodate situations where Parcels are committed to DKJord before their related Locations. The "inconsistency"of this reference is only ever assumed to last a few seconds at most.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ParcelXStatusDeclarationID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [ParcelID_SYS] | uniqueidentifier | No | x | Reference to the Parcel that we are connecting with a Status Declaration geometry | |
| [StatusDeclarationID_SYS] | uniqueidentifier | No | Reference to the location shape that was used to evaluate the pollution status of the Parcel |
Table: [dbo].[PlannedAction]
Purpose: Shows what planned actions exist for a location. Planned actions can be anything from monitoring to cleanup. It uses a CodeTypes/CodeValues array to describe data that originates from Standat 341 list.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [PlannedActionID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [CodeType] | int | No | x | Hardcoded to "22", which refers to "Planlagt indsats [00341]" | |
| [CodeValue] | char(10) | No | x | Options to set for the stated CodeType; e.g. "06 - Tilsyn, kommune","19 - Monitering, naturlig nedbrydning", "25 - Indsats, ingen offentlig, ikke omfattet" | |
| [PlannedActionEndDateTime] | datetime | Yes | Date on which the planned actions is expected to complete | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[PollutantComponent]
Purpose: Contains types and amounts of pollution as well as the mediums affected. It uses CodeTypes/CodeValues arrays to describe data that originates from Standat 19 and 158 lists. Records can be related directly to Locations or to Project Phases.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [PollutantComponentID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [PollutantComponentCodeType] | int | No | x | Hardcoded to "6", which refers to "Forureningskomponent [00019]" | |
| [PollutantComponentCodeValue] | char(10) | No | x | Options to set for the stated CodeType; e.g. "0006 - Støv", "9186 - E-coli", "9917 - PID" | |
| [ContaminatedMediaCodeType] | int | No | x | Hardcoded to "7", which refers to "Forurenet medie [00158]" | |
| [ContaminatedMediaCodeValue] | char(10) | No | x | Options to set for the stated CodeType; e.g. "01 - Jord", "02 - Grundvand", "06 - Indeklima" | |
| [PollutionConcentrationAmount] | decimal(12,2) | Yes | The amount registered of the pollutant component record | ||
| [PollutionConcentrationUnit] | varchar(20) | Yes | The unit used for the amount registered in the field PollutionConcentrationAmount | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[PollutionActivityCode]
Purpose: Information on the types of activities that occurred on a location. It uses a CodeTypes/CodeValues array to describe data that originates from Standat 342 list.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [PollutionCauseID_SYS] | uniqueidentifier | No | x | The pollution cause on which this pollution activity is registered | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [ActivityCodeValue] | char(10) | No | x | x | Options to set for the stated CodeType; e.g. "001 - Aktiviteter vedr. benzin-, olie-, gas-, kul og tjæreprodukter", "010 - Tjæreprodukter, aktiviteter vedr.", "023 - Asfalt, fremstilling af" |
| [ActivityCodeType] | int | No | x | Hardcoded to "26", which refers to "Aktivitetstype [00342]" | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[PollutionCause]
Purpose: Lists the types of industry/business that occurred on a location and which has caused pollution. It also delimits the time period of the pollution.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [PollutionCauseID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [PollutionCauseCodeType] | int | No | x | Hardcoded to "5", which refers to "Aktivitetstype [Intern-1]" | |
| [PollutionCauseCodeValue] | char(10) | No | x | Options to set for the stated CodeType; e.g. "00.00.20 - Børneinstitution", "00.00.60 - Kontor- og erhvervsejendom", "01.12.00 - Gartnerier og planteskoler" | |
| [PollutionStartDate] | varchar(20) | Yes | The date from which the pollution cause started | ||
| [PollutionEndDate] | datetime | Yes | The date on which the pollution cause ceased | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[Project]
Purpose: The projects (typically clean up or research) carried out on the location.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ProjectID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [ProjectIdentifier] | int | Yes | x | Unique number assigned to the project | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [ProjectDescriptionText] | nvarchar(2000) | Yes | A descriptive text of the project | ||
| [ProjectStartDate] | datetime | Yes | The date this project started | ||
| [ProjectEndDate] | datetime | Yes | The date this project ended | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[ProjectCode]
Purpose: Adds metadata to Projects and Projects Phases that elaborate on details of the Project, like the type of area where the Project is carried out, the legal basis for the Project, and who will pay for the Project. It uses CodeTypes/CodeValues arrays to describe data that originates from Standat 208, 113 and 148 lists.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ProjectCodeID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [ProjectID_SYS] | uniqueidentifier | No | x | Reference to the Project that this record belongs to | |
| [ProjectPhaseID_SYS] | uniqueidentifier | Yes | x | The Project Phase to which the Project Code was assigned | |
| [CodeType] | int | No | x | Set by code to either "9", "10" or "19", which refers to "Indsatsområde [00208]", "Lovgrundlag [00113]" and "Betaler [00148]" | |
| [CodeValue] | char(10) | No | x | Options to set for the stated CodeType; for "9" it could e.g. be "03 - Offentlig legeplads", "05 - Indvindingsopland til almen vandforsyning", "11 - Overfladevand", for "10" it could e.g. be "01 - Depotlovens paragraf 7","04 - Miljøbeskyttelsesloven (MBL)","14 - Olietankbekendtgørelsen"; for "19" it could e.g. be "01 - Amt","02 - Stat","04 - Grundejer" | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[ProjectPhase]
Purpose: Splits Projects up into Phases with Type of Phase and start and end dates.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ProjectPhaseID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [ProjectID_SYS] | uniqueidentifier | No | x | The Project to which the context Phase was assigned | |
| [ProjectPhaseCodeType] | int | No | x | Hardcoded to "11", which refers to "Projektfaser [00149]" | |
| [ProjectPhaseCodeValue] | char(10) | No | x | Options to set for the stated CodeType; e.g. "01 - Registreringsundersøgelse", "03 - Supplerende undersøgelse", "18 - Overvågning mhp naturlig nedbr" | |
| [ProjectPhaseStartDate] | datetime | Yes | The date the project phase started | ||
| [ProjectPhaseEndDate] | datetime | Yes | The date the project phase ended | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[ProjectPhaseXGeometry]
Purpose: Describes the area (geometry/shape) that an individual Project Phase has focused on.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [GeometryID_SYS] | uniqueidentifier | No | x | The unique identifier of this record | |
| [ProjectPhaseID_SYS] | uniqueidentifier | No | x | The Project Phase which the context geometry belong to | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record | ||
| [Shape] | geometry | Yes | The geometry (geospatial area) - typically a polygon |
Table: [dbo].[ProjectPhaseXPollutantComponent]
Purpose: Links a Project Phase with the types and amounts of pollution observed as well as the mediums affected.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [ProjectPhaseID_SYS] | uniqueidentifier | No | x | x | PK/FK in the many to many relationship between Project Phase and Pollutant Component |
| [PollutantComponentID_SYS] | uniqueidentifier | No | x | x | PK/FK in the many to many relationship between Project Phase and Pollutant Component |
Table: [dbo].[Regions]
Purpose: Contains list of all Danish Regions.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [RegionCode] | int | No | x | A unique number given to each Danish region | |
| [RegionName] | nvarchar(50) | No | The name of the context Danish region | ||
| [CVR] | varchar(10) | No | The unique CVR (Danish company registration number) of the Region office |
Table: [dbo].[Section8Authorization]
Purpose: Stores "§8 tilladelser" (approvals for building or digging on polluted land), the use of the land, and the background for its status as polluted. It uses CodeTypes/CodeValues arrays to describe data that originates from Standat 115 and 116 lists.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [Section8AuthorizationID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [AreaMeasure] | decimal(12,2) | Yes | Område i m2 som sektion 8 autorisationen gælder | ||
| [ApplicationDate] | datetime | Yes | Den dato hvor fra sektion 8 autorisationen gælder | ||
| [UseCodeType] | int | Yes | x | Hardcoded to "2", which refers to "Arealets anvendelse [00115]" | |
| [UseCodeValue] | char(10) | Yes | x | Options to set for the stated CodeType; e.g. "03 - Boligejendomme", "04 - Kontor- og erhverv", "05 - Industri, produktion" | |
| [JustificationCodeType] | int | Yes | x | Hardcoded to "3", which refers to "Baggrund for status for forurende arealer [00116]" | |
| [JustificationCodeValue] | char(10) | Yes | x | Options to set for the stated CodeType; e.g. "01 - Undersøgelse", "02 - Afværgeforanstaltninger", "03 - Revurdering af kortlægningsgrundlag" | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[StatusDeclaration]
Purpose: Shows information of the pollution status that has been declared on a Location.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [StatusDeclarationID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [LocationID_SYS] | uniqueidentifier | No | x | Reference to the location that this record belongs to | |
| [PollutionIdentificationDate] | datetime | Yes | Den dato hvor forureningsområdet blev identificeret | ||
| [PollutionQualificationDate] | datetime | Yes | Den dato hvor forureningsgraden blev bestemt | ||
| [RequirementForV2StudyDate] | datetime | Yes | Den dato hvor V2 analyse blev sat | ||
| [AreaMeasure] | decimal(12,2) | Yes | Område i m2 som statusdeklarationen gælder | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[StatusDeclarationCode]
Purpose: Shows the detailed pollution codes set on a StatusDeclaration object. It uses CodeTypes/CodeValues arrays to describe data that originates from Standat 159 and 148 lists.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [StatusDeclarationID_SYS] | uniqueidentifier | No | x | x | Unique identifier of this record |
| [CodeType] | int | No | x | x | Set by code to either "1", "3" or "20", which refers to "Status for forurenede arealer [00159]", "Baggrund for status for forurende arealer [00116]" and "Nuancering [00339]" |
| [CodeValue] | char(10) | No | x | x | Options to set for the stated CodeType; for "1" it could e.g. be "07 - V1 kortlagt", "08 - V2 kortlagt", "10 - Ryddet op inden kortlægning (registrering)"; for "3" it could e.g. be "01 - Undersøgelse","02 - Afværgeforanstaltninger","03 - Revurdering af kortlægningsgrundlag"; for "20" it could e.g. be "01 - F0 Nuanceret","02 - F1 Nuanceret","03 - F2 Nuanceret" |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[StatusDeclarationXGeometry]
Purpose: The actual area (geometry/shape) of a StatusDeclaration object.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [GeometryID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [StatusDeclarationID_SYS] | uniqueidentifier | No | x | A reference to the status declaration that the geometry belongs to | |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record | ||
| [Shape] | geometry | Yes | The geometry (geospatial area) - typically a polygon |
Table: [dbo].[Treatment]
Purpose: Shows detailed omformation about the type of treatment carried out on a Phase of a Project. It uses CodeTypes/CodeValues arrays to describe data that originates from Standat 150, 152, 153, 154, 155, 156 and 157 lists.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [TreatmentID_SYS] | uniqueidentifier | No | x | Unique identifier of this record | |
| [ProjectPhaseID_SYS] | uniqueidentifier | No | x | A reference to the Project Phase that this Treatment record belongs to | |
| [CodeType] | int | No | x | Set by code to either "12", "13","14","15","16","17" or "18", which refers to "Pumpning [00150]", "Deponering-Rensning [00152]","Rensemetode [00153]","Immobilisering [00154]","Foranstaltninger mod afdampning til indeklima [00155]","Foranstaltninger mod lossepladsgas [00156]" and "Monitering [00157]" | |
| [CodeValue] | char(10) | No | x | Options to set for the stated CodeType; for "12" it could e.g. be "01 - 0-5 år", "02 - 5-10 år", "03 - >10 år"; for "13" it could e.g. be "01 - Jord renset, herefter kontrolleret losseplads","04 - Jord renset til "fri anvendelse"","08 - Jord renset, herefter direkte til byggeprojekt ($19)"; for "14" it could e.g. be "01 - Ex site - biologisk","07 - On site - udvaskning","12 - In situ - andet"; for "15" it could e.g. be "01 - Befæstning med asfalt eller lign.","02 - Membran","03 - Lodret væg, impermeabel"; for "16" it could e.g. be "01 - Aktiv ventilering","02 - Passiv ventilering","03 - Ventileret dræn"; for "17" it could e.g. be "01 - Bygningsmæssige foranstaltninger","02 - Aktiv ventilering","05 - Aktivt ventileret dræn"; for "18" it could e.g. be "01 - Grundvandsforurening","02 - Recipient","03 - Indeklima" | |
| [TreatmentStartDate] | datetime | Yes | A date from when the treatment started | ||
| [TreatmentEndDate] | datetime | Yes | A date on which the treatment ended | ||
| [TonnesTreatedQuantity] | decimal(12,2) | Yes | Amount, in tonnes, of soil that was treated | ||
| [Comment] | nvarchar(255) | Yes | A comment on the treatment | ||
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |
Table: [dbo].[UseCode]
Purpose: Shows information of what a Location is being used for. It uses CodeType/CodeValues array to describe data that originates from Standat 115 lists.
| Column | Type | Nullable | IsPK | IsFK | Description |
|---|---|---|---|---|---|
| [LocationID_SYS] | uniqueidentifier | No | x | x | Reference to the location that this record belongs to |
| [CodeType] | int | No | x | x | Hardcoded to "2", which refers to "Arealets anvendelse [00115]" |
| [CodeValue] | char(10) | No | x | x | Options to set for the stated CodeType; e.g. "03 - Boligejendomme", "04 - Kontor- og erhverv", "05 - Industri, produktion" |
| [CreatedDate] | datetime | No | Date that this record was created | ||
| [CreatedUser] | varchar(300) | No | Username of the user who created this record | ||
| [CreatedUAK] | varchar(300) | No | Unique account key (typically email) of the user who created this record | ||
| [ModifiedDate] | datetime | No | Date that this record was last modified | ||
| [ModifiedUser] | varchar(300) | No | Username of the user who last modified this record | ||
| [ModifiedUAK] | varchar(300) | No | Unique account key (typically email) of the user who last modified this record |