scrollbar |
---|
----
Section | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||||
| ||||||||||||||||||||||||||
Expand | ||||||||||||||||||||||||||
Click to view Table of contents for this step | Click to view Table of contents for this step | toc
|
...
Step 5.1 Check table structure of all worksheets in Data spreadsheet handed over
Go to #Step Step 5.2. Grids_loc
The starting condition of this step is a populated DataTemplate, which contains all relevant information for the model.
When running the NGMS_Locations_Generator locations-generator macro, it will pick up this spreadsheet, conduct a variety of checks and sorts all locations in the appropriate order. For this purpose, the Generator uses hard-coded references to named worksheets and specific columns within a worksheet. To prevent failure in this processing, the following checks the need to be conducted on the table structure of the populated DataTemplate.
The comparison is done against the Empty_DataTemplate spreadsheet.
Info | ||
---|---|---|
| ||
Please pay attention to the calculation mode specified in Excel. This mode should be set to Automatic. If this is not set to automatic it could result in a wrong interpretation of abstraction for child wells. |
The comparison is done against the Empty_DataTemplate spreadsheet.
- worksheet Model_info
- ensure that the geological layers record are filled, as the generator checks this list to assign the observation boreholes to model layers
- calculate the simulation length in days by subtracting end-date - start-date (B65-B64) and setting the cell format to general. this simulation length needs to be used in the runMF-spreadsheet to specify the export length
- worksheet GWabs_org
- ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
Note: If more than 4 runs are to be processed, use a second spreadsheet and
- ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
- worksheet GWabs_org
- ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
Note: If more than 4 runs are to be processed, use a second spreadsheet and fill column A-D with the line numbers of the wells in those runs. Please ensure that both spreadsheets contain the same locations. - ensure that LicenceNo starts in column F (i.e. C6)
- ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
- worksheet SWlat_org
- See above: ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
Note: If more than 4 runs are to be processed, use a second spreadsheet and fill column A-D with the line numbers of the wells in those runs. Please ensure that both spreadsheets contain the same locations. - ensure that ConsentNo/ LicenceNo starts in column I F (i.e. C9C6).
- See above: ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
- worksheet BranchesSWlat_org
- check if reachNr is in column C (C3); chainage is in column D (C4), OSRef is in column J (C10)
- if not, copy the worksheet from the Empty_DataTemplate and populate again
or - add column D and column J from the Empty_DataTemplate.xls
- worksheet GWabs_IDmap_hist; GWabs_IDmap_nat; GWabs_IDmap_recact; GWabs_IDmap_fullic
- if not available, copy all 4 from Empty_DataTemplate
- See above: ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
Note: If more than 4 runs are to be processed, use a second spreadsheet and fill column A-D with the line numbers of the wells in those runs. Please ensure that both spreadsheets contain the same locations. - ensure that ConsentNo/LicenceNo starts in column I (i.e. C9).
- See above: ensure that no more than 4 runs are listed (column A-D), as the IdMapping can currently handle 4 runs only
- worksheet Branches_org
- check if reachNr is in column C (C3); chainage is in column D (C4), OSRef is in column J (C10)
- if not, copy the worksheet from the Empty_DataTemplate and populate again
or - add column D and column J from the Empty_DataTemplate.xls
- worksheet GWabs_IDmap_hist; GWabs_IDmap_nat; GWabs_IDmap_recact; GWabsworksheet SWlat_IDmap_hist; SWlat_IDmap_nat; SWlat_IDmap_recact; SWlat_IDmap_fullic
- if not available, copy all 4 from Empty_DataTemplate
- worksheet SWlat_IDmap_hist; SWlat_IDmap_nat; SWlat_IDmap_recact; SWlat_IDmap_fullic
- if not available, copy all 4 from Empty_DataTemplate
The DataTemplate contains one macro, called 'CheckData' The DataTemplate contains one macro, called 'CheckData' which checks if the essential data is in place. While the details will be addressed in the various steps below, most errors are caused if abovementioned steps are not properly conducted or if the OSreference column is empty. An OSreference column with empty or 'na' is not acceptable as this column is the key for creating a unique locationId
...
The worksheet GWabs_org holds location information on the groundwater abstractions as defined in the model runs (WEL file). The worksheet GWabs_org is populated by the data provider. The information is used to generate the Locations.xml holding the XY-position of the abstraction as well as the tooltip shown on the Explorer.map. Multi-layer abstractions or multi-licence abstractions will be sorted by the macro and assigned a 'parent' location shown on the Explorer-map. Various grouping methods will be applied to assign the abstractions to different location sets. The macro will use this worksheet in combation combination with the GWabs_IDmap-worksheets to define the IdMapping between the location defined in the spreadsheet and the time series as held in the WEL file.
...
- Remove the example rows (if any) such that the row 40 is the first one containing the actual data
- Populate column I (OSreference) based on NGR (Easting, Northing) of borehole (column K and L).
- Check that column A,B,C,D are populated in correspondence to the provided runs (ModuleDataSets)
- Check that the number of locations/licences as listed in the spreadsheet corresponds (at least) to the number of wells in each WEL-file
- Define concatenate functions for cell B16, B17, B18, B19, B21, B22. The actual contents depends on data availability.
- Verify that the column(s) as used for the concatenate functions are properly populated with appropriate information (i.e. preferably no empty cells, 'unknowns, 'na' etc.). If necessary apply an 'IF-construction on the concatenate function. This verification is especially critical for columns being used in location names.
Info Expand Expand recommended concatenations with max. 4 WEL files Expand recommended concatenations with max. 4 WEL files cell
item
used in
recommended content
recommended concatenate function
B16
LocationName
Explorer (map and filter)
licence code, purpose code (if any), site name, layer
=IF(O40<>"";F40&""&G40&" - "&S40&" (L"&O40&")";F40&""&G40&" - "&S40)
B17
ShortName
Graph legends
if possible shorter version of above
B18
LocationDescription
Description in Explorer-tooltip
location type, GW-unit, Formation
=T40 & " modelled at ("&M40&", "&N40&") [R"&p40&", C"&q40&"] in GW-unit " & W40 & " ("&X40&") from " & Y40 &" formation."
B19
Tooltip on map
free text Explorer-tooltip
licence holder, licence details, NGR, model coordinates (XYZ and RC)
=IF(U40="";"";"Licence holder: " &U40 & " (" &F40 & " - "&G40&") \n located at NGR (")& "Easting: "&K40&", Northing: " &L40& " "&M40& ", Model-Y: "&N40&" ), Row: "&P40&", Column: "&Q40&", Layer: "&O40&" " \
B21
Caption
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
B22
Shortcut
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
...
- Remove the example rows (if any) such that the row 36 is the first one containing the actual data
- Populate column D (OSreference) based on XY-coordinate of the associated grid cell (column H and I).
- Define concatenate functions for cell B14, B15, B16, B17, B19, B20. The actual contents depends on data availability.
- Verify that the column(s) as used for the concatenate functions are properly populated with appropriate information (i.e. preferably no empty cells, 'unknowns, 'na' etc.). If necessary apply an 'IF-construction on the concatenate function. This verification is especially critical for columns being used in location names.
- Verify that observation locations do not refer to layer numbers which are not supported by the model. Data mapping is done on a model layer basis. Hence, an observation in layer 0 will not receive information if the model contains layer 1-3.
Info Expand Expand recommended concatenations Expand recommended concatenations cell
item
used in
recommended content
recommended concatenate function
B14
LocationName
Explorer (map and filter)
site name & site code (optional layer)
=N36&" - "&C36
B15
ShortName
Graph legends
if possible shorter version of above
B16
LocationDescription
Description in Explorer-tooltip
location type, GW-unit, Formation
=O36 & " modelled at ("&H36&", "&I36&") [R"&K36&", C"&L36&") in GW-unit " & P36 & " ("&Q36&") from " & R36 &" formation."
B17
Tooltip on map
free text Explorer-tooltip
borehole details (if any), NGR, model coordinates (XYZ and RC)
="Top of boreholeBH top: "&U36&" mAOD, Base of boreholebase: "&V36&" mAOD\n Easting: , located at NGR ("&F36&", Northing: " "&G36&""&H36& ", Model-Y), Row: "&I36&""&K36&", Column: "&L36&", Layer: "&J36&" " \
B19
Caption
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
B20
Shortcut
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
...
- Remove the example rows (if any) such that the row 35 is the first one containing the actual data
- Populate column B (OSreference) based on XY-coordinate of the associated grid cell(column F and G).
- Define concatenate functions for cell B12, B13, B14, B15, B17, B18. The actual contents depends on data availability.
- Verify that the column(s) as used for the location name are properly populated with appropriate information (i.e. preferably no 'unknowns, 'na' etc.)
- Verify that observations do not refer to layer numbers which are not supported by the model. Data mapping is done on a model layer basis. Hence, gaugeboards in layer 0 do not receive information if the model contains layer 1-3).
Info Expand Expand recommended concatenations Expand recommended concatenations cell
item
used in
recommended content
recommended concatenate function
B12
LocationName
Explorer (map and filter)
site name & site code
=L35&" - "&A35
B13
ShortName
Graph legends
if possible shorter version of above
B14
LocationDescription
Description in Explorer-tooltip
site description, river (if necessary)
=M35 & " " & N35
B15
Tooltip on map
free text Explorer-tooltip
NGR, model coordinates (XYZ and RC)
="Easting: "&D35&", Northing: " &E35& " Model-X: "&F35& ", Model-Y: "&G35&" \Row: "&I35&", Column: "&J35&", Layer: "&H35&" \"
B17
Caption
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
B18
Shortcut
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
...
- Remove the example rows (if any) such that the row 37 is the first one containing the actual data
- Populate column L (OSreference) based on the NGR-coordinate of the discharge or abstraction point (column N and O).
- Define concatenate functions for cell B14, B15, B16, B17, B19, B20. The actual contents depends on data availability.
- Verify that the column(s) as used for the concatenate functions are properly populated with appropriate information (i.e. preferably no empty cells, 'unknowns, 'na' etc.). If necessary apply an 'IF-construction on the concatenate function. This verification is especially critical for columns being used in location names. , 'na' etc.). If necessary apply an 'IF-construction on the concatenate function. This verification is especially critical for columns being used in location names.
Info Expand Expand recommended concatenations Expand recommended concatenations cell
item
used in
recommended content
recommended concatenate function
B14
LocationName
Explorer (map and filter)
consentNo, purpose code, sitename
=IF(I37="";X37;IF(J37<>"";I37&"_"&J37&" - "&X37;I37 &" - "&X37))
B15
ShortName
Graph legends
if possible shorter version of above
=IF(I37="";X37;IF(J37<>"";I37&"_"&J37;I37))
B16
LocationDescription
Description in Explorer-tooltip
site description, purpose, remark if any
=Y37&" for "&AA37 &" purposes. "&AE37
B17
Tooltip on map
free text Explorer-tooltip
licence holder (if any), NGR, model coordinates (XYZ and RC)
="Licenceholder: " &Z37&"\n Easting: "&N37&", Northing: " &M37& " Model-X: "&P37& ", Model-Y: "&Q37&" \Row: "&S37&", Column: "&T37&", Layer: "&R37&" \"
B19
Caption
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
B20
Shortcut
Shortcuts in GraphDisplay
see shortname or variations
see shortname or variations
Anchor | ||||
---|---|---|---|---|
|
...
- The Stream/rivername is normally used as unique identifier. Please do not use a slash (/) in the name.
- A new branch is identified by a changing name.
...
Step 5.10 Populate and verify worksheets for SW laterals: SWlat_IDMap (4 worksheets)
Go to #Step Step 5.11. Generate Location XML-files
...
Anchor | ||||
---|---|---|---|---|
|
Step 5.11 Generate Location
...
files
Go to #Step 5.12. Update Grids.xml file
If everything is ready, you can run the macros. Note that the macro version 5 assumes that you use FEWS-software build 20300 or higher.
- To run the macro, conduct the following steps:*
- Create a backup-file with name XXX_LocationsProcessed_originals_versionnumber.xls.
- Open the spreadsheet NGMS-locations_Generator_v5.00.xls.
- Activate the data-spreadsheet and run the macro 'SortLocationsAddParents' held in the NGMS_Locations_generator-spreadsheet.
- The macro asks what to items to process. Normally you would like to process allitems to process. Normally you would like to process all. Since file saving to DBF4 contains some layout issues, the macro can do this automatically. Note that it shows a message at the time of saving if the file already exists. Dependent on the model size it can take up to 2 hours (e.g. YNN).
- Check the results
- Are the newly generated worksheets properly filled ?
- Did the concatenate functions give proper results (especially for parents) ?
- Is the IDMapping OK ?
- If anything is incorrect:
- Fix the error in the _org worksheet
- Remove the associated worksheets generated by the macro.
- Save again under the same name (possibly with a version number)
- Run the macro 'SortLocationsAddParents' again and pick only the relevant location types.
- Check again.
...
To generate the XML-files, conduct the following steps:
- Create a backup with name XXX_LocationsResults_versionnumber.xls
- Run macro 'create_XMLfiles'. (NB at the end of the Sort-macro, it also asks if this next step should be conducted autiomatically).
- The macro creates XML-files in the directory of the data-spreadsheet.
- The macro overwrites existing files
- You can skip location types to be generated (e.g. if you want to update a branches file), but be aware:
If you skip any of the location types listed, your Locations.xml file might not be complete.
- Check the contents of the files. It might be wise to validate with an XML-validation tool.
- Copy the files which are OK to your configuration:
- Any DBF-file to \Config\MapLayerfiles
- Locations.XML, LocationSets.xml, Branches.xml, Polygons.xml --> region-root\Config\RegionConfigFiles
- IdMap files --> \Config\IdMapfiles
- Leave the Grids.xml file to be updated in the next step
...