Geological Survey of Denmark and Greenland Bulletin 38, 2017, 69-72 69 Denmark has a long tradition for having central geologi- cal databases, including a systematic collection and stor- age of geological and hydrological information from all surficial boreholes which was initiated in 1926. Since the mid-1970s such data have been stored digitally. A large variety of users access a central Danish, geological data- base: the public, for information about their local drinking water quality, environmental employees in municipalities, regions and the state for using, entering and updating data as well as consultants and drilling companies working for public administration and local water works. The local Danish administrative system previously con- sisted of 14 counties and 248 municipalities. The counties were responsible for groundwater mapping, drinking water management and activities concerning contaminated soil, as well as for harmonisation and transfer of data to the central database. With effect from 1 January 2007, this ad- ministrative system was replaced by five regions, seven en- vironmental centres and 98 municipalities, which required major changes in the administrative handling of borehole data at the local and regional levels. For this, a public and shared central database was established and a countrywide harmonisation of data, transfer and storage was initiated and all geological, groundwater and drinking water data were transferred to this central database at Geological Sur- vey of Denmark and Greenland (GEUS). In an updated database system, public authorities were set up to access the central database to store their relevant borehole data and almost all data were made publicly avail- able. The database is maintained by GEUS. It is directly connected to other public databases at GEUS including the shallow geophysical database GERDA, where e.g. borehole loggings are stored, and to the Model Database where sim- ple geological models are stored (Fig. 1). An integrated public information system for geology, groundwater and drinking water in Denmark Martin Hansen and Charlotte Toftemann Thomsen Fig. 1. Diagram showing A: The access to data in the Jupiter database from different users and B: How data f low to and from the database. WS: web services. Privileged users Jupiter database PC Jupiter XL Read-only views Jupiter tables Unprivileged users Users at GEUS GEUS databases Drilling companies Municipalities State agencies Consultants Borehole data Ground- water data Borehole data Models Reports Borehole data Geophysics models Reports groundwater and drinking water data Laboratories Chemical analysis Regions B. Data flow A. Access to Jupiter data http – homepages, WS reading and download web forms Updating using WS and https - Borehole, © 2017 GEUS. Geological Survey of Denmark and Greenland Bulletin 38, 69–72 . Open access: www.geus.dk/publications/bull 7070 This updated system is used by the municipalities to manage their water supply data (e.g. water supply struc- ture, permits, groundwater and drinking water quality data), by state agencies to manage groundwater data from the groundwater mapping and by the regions to maintain their soil pollution data. The system has gradually been expanded since 2007 and now local authorities can store and maintain a wide range of their own data in the central database. The database system The database system currently consists of the following components: 1. A central database; 2. A public data model, agreed upon by a committee under the The Danish Natural Environment Portal; 3. A user-management system, managed by the The Danish Natural Environment Portal, providing direct access to the central database; 4. A suite of Simple Object Access Protocol (SOAP) web services – an interface that allows computer-to-computer communication. This enables local authorities to manage their own data in the central database through their own applications; 5. Applications that can access the database utilising the components above. The central database The central database is based on GEUS’ Jupiter database and run on an Oracle database. It has been under develop- ment during the last 40 years. The extensions made since 2007 include full public online access to read data and write access for public authorities to almost all data types. The public part of the database is made available through a view layer exposing the public data model. The public data model The public data model can handle: • Borehole data, including: localisation and administrative data, construction data, abandon data, geological descrip- tion using common methodology, hydraulic head meas- urements, samples and analyses from soil, water and air; • Surface soil and water sample data; • Water supply data for water plants, agriculture and indus- try, including: extractions wells, well fields, water treat- ment plants, extraction permits, water quality data, water extraction, water use data, exchange of water between wa- ter works, ownership and contact persons; • Soil pollution data: projects; soil, water and air chemis- try data from boreholes, surface samples and remediation plants. The data model is being developed and maintained by GEUS, but all extensions and alterations have to be agreed upon by the Groundwater Group under the Danish Natural Environment Portal (DNEP). This is a common public partnership between the Ministry of Environment and Food of Denmark (45%), the Danish municipalities (45%) and the Danish Regions (10%), and it acts as an in- dependent portal across boundaries of authority. Its major goal is to ensure continued access to harmonised, updated, natural environmental data. The Groundwater Group it- self consists of members appointed by the municipalities, Name of user role Privileges and use Laboratory For entering and editing samples and chemical analyses. For laboratories to add data to the system. Borehole write For entering and editing information about boreholes including location, geology/lithology and borehole construction. For consultants and local authorities to enter new boreholes. Borehole read For reading publicly available data about bore- holes, water, soil and air samples and analysis. Drinking water For approving new drinking water samples with approval analyses. For users from the authorities to qua- lity control new drinking water data and by approving the data, making them publicly available. Groundwater For approving new groundwater samples with approval analysis. For users from the authorities to quality control the new groundwater data and by approving the data, making them publicly available. Water level For entering and editing water-level measure- ments. For users from the authorities to enter and edit water-level measurements. Sample approval For approving new water, soil and air samples with analyses from surface samples. For users from the authorities to quality control the new water, soil and air analyses from surface samples and by approving the data, making them publicly available. Water resources For entering and updating information about water works. These data include water well fields, treatment plants, permits, annual volumes of extracted water, sampling sites and volumes of water shared between different water works. For the municipalities that survey the drinking water to create and update their drinking water structure. Table 1. User roles and privileges 71 the Danish Environmental Protection Agency , the Danish Regions, DNEP and GEUS. The public data model con- sists of more than 90 data tables. Data responsibility agreement The data responsibility agreement determines which or- ganisations are responsible for producing and maintain- ing which data, and for making the data available to the public. The various responsibilities are defined partly by legislation and partly through agreements signed by the participating partners and by voluntary reporting. At any time, any data set in the public database has one and only one responsible owner organisation. Data ownership The ownership of data can be defined either by the user who enters the data or by the location of the data point. In this way, the municipality or region in question, a state agency and GEUS can own water-level measurements in the same well. Each data owner is responsible for entering their own data and secure their quality. The analysing laboratory carrying out the quality con- trol of drinking water is responsible for entering its data, and it owns the data until the data entry and quality con- trol have been completed. After this step, the ownership is transferred to the municipality to which the water works belongs. The municipality has to release the data before it becomes publicly available. Apart from quality control of the data sets, the new owner cannot alter the data. If er- rors are found during the quality control, the municipality must reject the water sample and all of its analyses, and the laboratory has to resubmit a corrected data set. User management The Danish Natural Environment Portal has a central user-management system which enables the user to use the same login credentials to access and update data in differ- ent systems. The users and their rights are managed locally by user administrators who define the user rights through a set of roles. Each role defines to which part of the data- base the user shall have access. In this way it is the local administrator who decides who should be allowed to access the different public systems or obtain privileges to enter and edit data. The different roles in the Jupiter system are presented in Table 1. In addition to these roles, the system gives the users access to data according to their geographi- cal location. For example, all users can access water works but only users from the municipality, where the water works is situated, can update the information (provided the user has the right role). Data interface The data are available in several ways. Most of the data are available through: Jupiter’s homepage: This entry is read-only and mainly used by municipalities and members of the public to look up specific data (Fig. 2). SOAP web services: These services give read-and-write ac- cess to different parts of the data model and have been under development since 2007. With this interface pri- vate companies can write applications for administrative units for their data management. These services constitute Fig. 2. Screen shot from the web map interfaces to Jupiter showing bore- holes and water plants from central Sjælland. 7272 the main entry outside GEUS for update of data. See for example: http://webs.geus.dk/miljoeportal.groundwater.b- boring.2.0.0/B-Boring?wsdl for updating borehole data. WMS/WFS: Several of the data themes are exposed through web map services (WMS) and web feature services (WFS). Web map services deliver maps as bitmaps while web fea- ture services can deliver the same data as geographical ob- jects (point, lines and polygons) that can be used for spatial queries in a GIS. These are used to support the map inter- face on the Jupiter homepage, are available for end users, and can also be imported into local GIS. These services are used mainly in systems made for the different administrat- ing units. See for example the WMS publishing borehole information http://data.geus.dk/geusmap/ows/25832.jsp?SE RVICE=WMS&VERSION=1.1.1&REQUEST=GetCapabil ities&LAYERS=jupiter_boringer_ws%2cjupiter_anlaeg_ws Database download: Advanced users can download data as database exports. In this way it is possible to export all available data to a local hard drive (excluding water quality control data not yet approved by the data owner and infor- mation about owners and contact persons). This function is meant for advanced users for e.g. geological modelling or complex calculations on groundwater chemistry. It is even possible to install a scheduled application that keeps the lo- cal database updated on a nightly basis with changes made in the central database. Such local copies of the database are mainly used by consulting companies and large admin- istrative units. Discussion and conclusions Nearly all data in the database must be publicly available. Therefore, the access has been divided into two packages of services since the first version of the Web Services was de- veloped up to January 2007. One set contains all the read- only functionality without any user management systems, while the other package contains functions for updating the data. However, due to very frequent use the read-only services will have to be revised in the near future. Not all users comply with the rules set up for the use of the ser- vices, and since they are anonymous, it is difficult to iden- tify those who break the rules. For example, users are not allowed to use the services in batch mode or create a local copy of the database. We can, however, see from the logs, that one or more read-only users behind a single ip-address make up to tens of thousands of calls on a daily basis and thus obviously do not comply with the rules set-up for the services. An increase from c. 200  000 to 12  000  000 calls per month in the last few years causes a heavy and increasing system load. If a login with user name and password was to be re- quired to enter the read-only services, it would be possible to contact directly the users who use software that does not comply with the rules of use. The access would still be free of charge. A public, shared database like Jupiter gives access to a very broad use, where the data can be combined with other public data or with private, non-public data. Also the many different ways in which the data are available, such as web, web GIS, different types of web services or download in da- tabase format, make the data highly usable. The user gets a coherent dataset containing geology, groundwater and drinking water data, where the water can be followed all the way from the borehole to the water plant. In recent years, the database has been used for analysis of public health in combination with drinking water quality. The free access to the publicly available data has greatly increased the value of the data. The authors do not know of any other publicly available, combined geology – groundwater – drinking wa- ter database systems like Jupiter. As the system is based on a data model that has been agreed upon between different stakeholders from municipalities, regions, state agencies and the geological survey, the model can most probably be used as a good starting point for development of similar systems by other organisations and countries. References Gerda database: http://data.geus.dk/geusmap/?lang=en&mapname=gerd a#layers=gerda_ projects%2cgerda_data Jupiter database: http://data.geus.dk/geusmap/?mapname=jupiter&lang=en SAML 2.0: https://en.wikipedia.org/wiki/SAML_ 2.0 SOAP web services: https://en.wikipedia.org/wiki/SOAP The Danish Natural Environment Portal: http://www.miljoeportal.dk/ English/Sider/default.aspx The Model Database: http://data.geus.dk/geusmap/?lang=en&mapname =modeldb Authors’ address: Geological Survey of Denmark and Greenland, Øster Voldgade 10, DK-1350 Copenhagen K, Denmark. E-mail: mh@geus.dk