UM Aquo CSV import
This is an import that makes use of the custom import mechanism described in Custom import formats. The UmAquo CSV import consists of a text parser, UmAquoCsvTimeSeriesParser.java, and a bin directory containing all dependant library files (UmAquo.zip ).
The UmAquo standard is being managed by Informatiehuis Water and is used to exchange data with the Online Omgevingsloket. Besides the CSV format there also exists an XML version of the UmAquo standard. Informatiehuis water is responsible for maintaining the Aquo-domaintables . These tables contain lists of standardised variable codes related to water management. These tables are meant to aid the exchange of data between organisations in the water-sector by means of a standard terminology.
The UmAquo CSV import validates the input variables against these domaintables.
TimeSeries CSV file format
The CSV import format is described in the document SpecsImporterenCSV.doc (which is in Dutch, as are the Domain tables). Here follows an example CSV import file:
Monster.identificatie;MeetPunt.identificatie;Grootheid.code;Typering.code;Parameter.groep;Parameter.code;Parameter.omschrijving;Eenheid.code;Hoedanigheid.code;Compartiment.code;Begindatum;Begintijd;Tijd_UTCoffset;Limietsymbool;Numeriekewaarde;Alfanumeriekewaarde;Kwaliteitsoordeel.code # Exporting timeseries with header: #528002 CONCTTE.Cl (mg/l) unknown.NVT.OW nonequidistant never main 0 # ;528002;CONCTTE;;ChemischeStof;Cl;;mg/l;NVT;OW;2012-02-11;11:00:00;0;;9.0;;00 ;528002;CONCTTE;;ChemischeStof;Cl;;mg/l;NVT;OW;2012-02-11;12:00:00;0;;8.0;;00 ;528002;CONCTTE;;ChemischeStof;Cl;;mg/l;NVT;OW;2012-02-11;13:00:00;0;;8.0;;00 ;528002;CONCTTE;;ChemischeStof;Cl;;mg/l;NVT;OW;2012-02-11;14:00:00;0;;8.0;;00 ;528002;CONCTTE;;ChemischeStof;Cl;;mg/l;NVT;OW;2012-02-11;15:00:00;0;;-999.0;;99 ;528002;CONCTTE;;ChemischeStof;Cl;;mg/l;NVT;OW;2012-02-11;16:00:00;0;;-999.0;;99 # Exporting timeseries with header: #528002 CONCTTE.P (mg/l) unknown.NVT.OW nonequidistant never main 0 # ;528002;CONCTTE;;ChemischeStof;P;;mg/l;NVT;OW;2012-02-11;11:00:00;0;;9.0;;00 ;528002;CONCTTE;;ChemischeStof;P;;mg/l;NVT;OW;2012-02-11;12:00:00;0;;9.0;;00 ;528002;CONCTTE;;ChemischeStof;P;;mg/l;NVT;OW;2012-02-11;13:00:00;0;;9.0;;00 ;528002;CONCTTE;;ChemischeStof;P;;mg/l;NVT;OW;2012-02-11;14:00:00;0;;9.0;;00 ;528002;CONCTTE;;ChemischeStof;P;;mg/l;NVT;OW;2012-02-11;15:00:00;0;;9.0;;00 ;528002;CONCTTE;;ChemischeStof;P;;mg/l;NVT;OW;2012-02-11;16:00:00;0;;9.0;;00 # Exporting timeseries with header: #528001 CONCTTE.N (mg/l) unknown.NVT.OW nonequidistant never main 0 # ;528001;CONCTTE;;ChemischeStof;N;;mg/l;NVT;OW;2012-02-11;11:00:00;0;;9.0;;00 ;528001;CONCTTE;;ChemischeStof;N;;mg/l;NVT;OW;2012-02-11;12:00:00;0;;9.0;;00 ;528001;CONCTTE;;ChemischeStof;N;;mg/l;NVT;OW;2012-02-11;13:00:00;0;;9.0;;00 ;528001;CONCTTE;;ChemischeStof;N;;mg/l;NVT;OW;2012-02-11;14:00:00;0;;9.0;;00 ;528001;CONCTTE;;ChemischeStof;N;;mg/l;NVT;OW;2012-02-11;15:00:00;0;;9.0;;00 ;528001;CONCTTE;;ChemischeStof;N;;mg/l;NVT;OW;2012-02-11;16:00:00;0;;9.0;;00 # Exporting timeseries with header: #528001 WATDTE (m) unknown.NVT.OW nonequidistant never main 0 # ;528001;WATDTE;;;;;m;NVT;OW;2012-02-11;11:00:00;0;;1.8;;00 ;528001;WATDTE;;;;;m;NVT;OW;2012-02-11;12:00:00;0;;2;;00 ;528001;WATDTE;;;;;m;NVT;OW;2012-02-11;13:00:00;0;;2.1;;00 ;528001;WATDTE;;;;;m;NVT;OW;2012-02-11;14:00:00;0;;1.9;;00 ;528001;WATDTE;;;;;m;NVT;OW;2012-02-11;15:00:00;0;;1.9;;00 ;528001;WATDTE;;;;;m;NVT;OW;2012-02-11;16:00:00;0;;1.8;;00 # Exporting timeseries with header: #528005 WATDTE (m) unknown.NVT.OW nonequidistant never main 0 # ;528005;WATDTE;;;;;m;NVT;OW;2012-02-11;11:00:00;0;;1.8;;00 ;528005;WATDTE;;;;;m;NVT;OW;2012-02-11;12:00:00;0;;2;;00 ;528005;WATDTE;;;;;m;NVT;OW;2012-02-11;13:00:00;0;;2.1;;00 ;528005;WATDTE;;;;;m;NVT;OW;2012-02-11;14:00:00;0;;1.9;;00 ;528005;WATDTE;;;;;m;NVT;OW;2012-02-11;15:00:00;0;;1.9;;00 ;528005;WATDTE;;;;;m;NVT;OW;2012-02-11;16:00:00;0;;1.8;;00 # Exporting timeseries with header: #528001 T (°C) unknown.NVT.OW nonequidistant never main 0 # ;528001;T;;;;;oC;NVT;OW;2012-02-11;11:00:00;0;;1.8;;00 ;528001;T;;;;;oC;NVT;OW;2012-02-11;12:00:00;0;;2;;00 ;528001;T;;;;;oC;NVT;OW;2012-02-11;13:00:00;0;;2.1;;00 ;528001;T;;;;;oC;NVT;OW;2012-02-11;14:00:00;0;;1.9;;00 ;528001;T;;;;;oC;NVT;OW;2012-02-11;15:00:00;0;;1.9;;00 ;528001;T;;;;;oC;NVT;OW;2012-02-11;16:00:00;0;;1.8;;00
Expected CSV headers
MeetPunt.identificatie: mapped to location id (required)
Grootheid.code: if Parameter.code not present then this column is mapped to parameter id (optional)
Parameter.code: mapped to parameter.id. If not present then Grootheid.code is used.
Eenheid.code: mapped to timeseries unit and to qualifier 0 (required)
Hoedanigheid.code: mapped to qualifier 1 (required)
Compartiment.code: mapped to qualifier 2 (required)
Waardebepalingsmethode.code: mapped to qualifier 3 (optional)
Begindatum: mapped to timestamp together with BeginTijd (required)
Begintijd: mapped to timestamp. If not present only Begindatum is used. (optional)
Limietsymbool: mapped to OutOfDetectionRangeFlag value (optional)
Numeriekewaarde: contains numeric value. If empty then Alfanumeriekewaarde field is parsed to numeric value.
Alfanumeriekewaarde: only used if Numeriekewaarde field is empty
Kwaliteitsoordeel.code: mapped to flag value
Date and time formats
Format Begindatum: yyyyMMdd
Format Begintijd: HH:mm:ss
Syntax
Commentline prefix: '#'
Column separator: ','
Decimal separator: '.'
Validation
Validation of the values in the UmAquo import files is done against the Aquo schemas . Instead of validating over the web, validation is done locally by validating against the schemas present in the resource file UmAquo_schemas.jar. This archive contains a subset of schemas from the Aquo schemas site. If the Aquo schemas are updated then a new version of the schemas jar must be distributed.
Fews configuration
In order to activate the UmAquo CSV import as a FEWS import, it is required to setup a TimeSeriesImportRun module configuration file and an accompanying IdMap file and FlagConversion file. Also the bin directory containing all UmAquo resources must be placed in a location that can be accessed by the FEWS system.
Here is an example import module configuration file:
<?xml version="1.0" encoding="UTF-8"?>
<timeSeriesImportRun xmlns="http://www.wldelft.nl/fews"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.wldelft.nl/fews http://fews.wldelft.nl/schemas/version1.0/timeSeriesImportRun.xsd">
<!-- This is an example import configuration file for importing UmAquo CSV data from an import directory -->
<import>
<general>
<!-- Class name of UmAquo CSV parser -->
<parserClassName>nl.wldelft.webservice.umaquo.timeseriesparsers.UmAquoCsvTimeSeriesParser</parserClassName>
<!-- Path to directory containing UmAquo libraries and schemas. If omitted then the content of the umaquo-bin
can be placed in the FEWS-bin dir -->
<binDir>%REGION_HOME%/Modules/umaquo-bin</binDir>
<!-- Directory from which CSV files are to be imported -->
<folder>$IMPORT_FOLDER$/UmAquo/CSV</folder>
<failedFolder>$IMPORT_FAILED_FOLDER$/UmAquo/CSV</failedFolder>
<backupFolder>$IMPORT_BACKUP_FOLDER$/UmAquo/CSV</backupFolder>
<idMapId>IdImportUmAquo</idMapId>
<importTimeZone>
<timeZoneOffset>+00:00</timeZoneOffset>
</importTimeZone>
</general>
<properties>
<!-- Optional: comment line prefix character. Default = # -->
<string key="COMMENT_PREFIX" value="#"/>
<!-- Optional: column separator character. Default = ; -->
<string key="COLUMN_SEPARATOR" value=";"/>
<!-- Optional: decimal character. Default = . -->
<string key="DECIMAL_SEPARATOR" value="."/>
<!-- Optional: Regular expression for date value. Default = yyyy-MM-dd -->
<string key="CSV_DATEPATTERN" value=";"/>
<!-- Optional: Regular expression for time value. Default = HH:mm:ss -->
<string key="CSV_TIMEPATTERN" value="HH:mm:ss"/>
<!-- Optional: select UmAquo schema version 2009 or 2011. Default = 2009 -->
<int key="SCHEMA_VERSION" value="2009"/>
<!-- Optional: allow lenient validation. Default = false -->
<bool key="LENIENT" value="true"/>
<!-- Optional: allow import to skip the first number of lines -->
<int key="FIRST_LINE" value="1"/>
</properties>
<!-- It is also possible to not configure timeSeriesSets. In this case all offered data will be imported as long as it can be mapped using the ID Mapping -->
<timeSeriesSet>
<moduleInstanceId>ImportUmAquo</moduleInstanceId>
<valueType>scalar</valueType>
<parameterId>MyPar</parameterId>
<locationSetId>MyLocSet</locationSetId>
<timeSeriesType>external historical</timeSeriesType>
<timeStep unit="nonequidistant"/>
<readWriteMode>add originals</readWriteMode>
<synchLevel>1</synchLevel>
</timeSeriesSet>
</import>
</timeSeriesImportRun>
Here is an example 1 id-map file:
<?xml version="1.0" encoding="UTF-8"?>
<idMap version="1.1" xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews http://fews.wldelft.nl/schemas/version1.0/idMap.xsd">
<!-- Parameter id mapping can be done in three ways -->
<!-- 1st: Concatentate GrootheidCode and ParameterCode with '.' in between -->
<map internalLocation="LOC-001" internalParameter="PAR-001" externalLocation="Meetpunt"
externalParameter="GrootheidCode.ParameterCode" externalQualifier="EenheidCode" externalQualifier1="HoedanigheidCode" externalQualifier2="CompartimentCode" />
<!-- in the case of Biotaxon use ParameterName instead of ParameterCode-->
<map internalLocation="LOC-001" internalParameter="PAR-001" externalLocation="Meetpunt"
externalParameter="GrootheidCode.ParameterName" externalQualifier="EenheidCode" externalQualifier1="HoedanigheidCode" externalQualifier2="CompartimentCode" />
<!-- 2nd: If only GrootheidCode then use this as external parameter -->
<map internalLocation="LOC-001" internalParameter="PAR-002" externalLocation="Meetpunt"
externalParameter="GrootheidCode" externalQualifier="EenheidCode" externalQualifier1="HoedanigheidCode" externalQualifier2="CompartimentCode" />
<!-- 3rd: If only TyperingCode then use this as external parameter -->
<map internalLocation="LOC-001" internalParameter="PAR-003" externalLocation="Meetpunt"
externalParameter="TyperingCode" externalQualifier="EenheidCode" externalQualifier1="HoedanigheidCode" externalQualifier2="CompartimentCode" />
<!-- 4th: If WaardeBepalingsMethode used then use this as external parameter -->
<map internalLocation="LOC-001" internalParameter="PAR-003" externalLocation="Meetpunt"
externalParameter="TyperingCode" externalQualifier="EenheidCode" externalQualifier1="HoedanigheidCode" externalQualifier2="CompartimentCode"
externalQualifier3="WaardeBepalingsMethodeCode"/>
</idMap>
Id-Mapping goes as follows:
Internal timeseries location and parameter combinations are mapped to external location, parameter and qualifier sets as follows:
- externalLocation values are read from the Meetpunt column
- externalParameter values are read from the Parameter column
- 1st externalQualifier values are read from the Eenheid column
- 2nd externalQualifier values are read from the Hoedanigheid column
- 3rd externalQualifier values are read from the Compartiement column
- 4th externalQualifier values are read from the WaardeBepalingsMethode column
If one of the externalQualifier codes is not present in the import then in the IdMap the value for the externalQualifier must be set to 'unknown'. As shown in the following example:
<parameter internal="NKj" external="CONCTTE.NKJ" externalQualifier="mg/l" externalQualifier1="N" externalQualifier2="OW" externalQualifier3="unknown"/>
Here is an other example 2 id-map file:
<?xml version="1.0" encoding="UTF-8"?>
<idMap xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews [http://fews.wldelft.nl/schemas/version1.0/idMap.xsd]" version="1.1">
<parameterIdFunction externalParameterFunction="@ATTR_PARAM@" externalQualifierFunction="@ATTR_UNIT@" externalQualifierFunction1="@ATTR_HOEDANIGHEID@" externalQualifierFunction2="@ATTR_COMPARTIMENT@" />
<locationIdFunction externalLocationFunction="@ATTRLOCID@" internalLocationSet="LocationSetId"/>
<!-- <enableOneToOneMapping/> -->
</idMap>
In this id-map it is shown how to use attribute functions to map external values to internal values.
The attribute values between the '@' in the parameter id function line refer to textAttributes that can be configured in the Parameters configuration file.
The attribute values between the '@' in the location id function line refer to attributes that can be configured in the LocationSets configuration file.
Here is an example flag conversion file:
<?xml version="1.0" encoding="UTF-8"?> <flagConversions xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews http://fews.wldelft.nl/schemas/version1.0/flagConversions.xsd"> <flagConversion> <inputFlag> <value>0</value></inputFlag> <outputFlag> <value>0</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>3</value></inputFlag> <outputFlag> <value>3</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>4</value></inputFlag> <outputFlag> <value>0</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>5</value></inputFlag> <outputFlag> <value>0</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>6</value></inputFlag> <outputFlag> <value>0</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>7</value></inputFlag> <outputFlag> <value>0</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>10</value></inputFlag> <outputFlag> <value>2</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>20</value></inputFlag> <outputFlag> <value>2</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>25</value></inputFlag> <outputFlag> <value>2</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>30</value></inputFlag> <outputFlag> <value>1</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>50</value></inputFlag> <outputFlag> <value>6</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>98</value></inputFlag> <outputFlag> <value>6</value></outputFlag> </flagConversion> <flagConversion> <inputFlag> <value>99</value></inputFlag> <outputFlag> <value>9</value></outputFlag> </flagConversion> <defaultOuputFlag><value>0</value></defaultOuputFlag> <missingValueFlag><value>9</value></missingValueFlag> </flagConversions>
