# Linking Data Between the Worksheets and GIS Databases for Effective Management of the Environmental Projects

Polina Lemenkova

► To cite this version:

Polina Lemenkova. Linking Data Between the Worksheets and GIS Databases for Effective Management of the Environmental Projects. 3rd International Farabi Readings. 'Green Bridge Through Generations', Al-Farabi Kazakh National University, Apr 2016, Almaty, Kazakhstan. pp.349-353, 10.6084/m9 figshare.7210238. hal-01973112

HAL Id: hal-01973112

https://hal.science/hal-01973112

Submitted on 11 Jan 2019

HAL is a multi-disciplinary open access
archive for the deposit and dissemination of sci-
entific research documents, whether they are pub-
lished or not. The documents may come from
teaching and research institutions in France or
abroad, or from public or private research centers.

L'archive ouverte pluridisciplinaire HAL, est
destinée au dépôt et à la diffusion de documents
scientifiques de niveau recherche, publiés ou non,
émanant des établissements d'enseignement et de
recherche français ou étrangers, des laboratoires
publics ou privés.

<figure><img src="image_1.png" /></figure>
Kazakhstan's concept for transition to Green Economy is another policy tool that aims to achieve economic growth on the basis of the transition to the green economy. According to this concept, by 2050 the implementation of a Green Economy will contribute to the following, the GDP rise to 3%, over 500,000 new workplaces, development of new industries and services and high living standards.<fnref n="8" /> Undoubtedly, investment plays an important role in implementation such projects. Under the concept of Green Economy, the transition will be about 1% of GDP per annum, which is equivalent to USD 3 to 4 billion.<fnref n="9" />

<table><caption>**Table 2** – Goals and target indicators of the Green Economy. Source: Concept for transition of the Republic of Kazakhstan to Green Economy. Approved by Decree of the President of the Republic of Kazakhstan on May 30, 2013 #557.p.7</caption><thead><tr><th>Sector</th><th>Target description</th><th>2020</th><th>2030</th><th>2050</th></tr></thead><tbody><tr><td rowspan="2">Water resources</td><td>Eliminate water gap on national level</td><td>Provide all population with access to water</td><td>Provide agriculture with water (by 2040)</td><td>Solve water resources problem forever</td></tr><tr><td>Elimination of water gap on basin level</td><td>Fastest closure of water gap by basins all together (by 2025)</td><td>Elimination of water gap in each basin by 2030</td><td></td></tr><tr><td rowspan="3">Agriculture</td><td>Labor efficiency in agriculture</td><td>3-fold increase</td><td></td><td></td></tr><tr><td>Wheat yields (ton/ha)</td><td>1.4</td><td>2.0</td><td></td></tr><tr><td>Water spent on irrigation (m/ton)</td><td>450</td><td>330</td><td></td></tr><tr><td>Energy efficiency</td><td>Reduction of energy intensity of GDP from levels of 2008</td><td>25% (10% by 2015)</td><td>30%</td><td>50%</td></tr><tr><td rowspan="2">Power sector</td><td>Share of alternative sources¹ in electricity production</td><td>Solar and wind: not less than 3% by 2020</td><td>30%</td><td>50%</td></tr><tr><td>Share of gas power plants in electricity production</td><td>20%²</td><td>25%²</td><td>30%</td></tr><tr><td></td><td>Gasification of regions</td><td>Akmola and Karaganda regions</td><td>Northern and Eastern regions</td><td></td></tr><tr><td></td><td>Reduction of current CO₂ emissions in electricity production</td><td>Levels of 2012</td><td>-15%</td><td>-40%</td></tr><tr><td>Air pollution</td><td>SOx, NOx emissions into environment</td><td></td><td>European levels of emissions</td><td></td></tr><tr><td rowspan="3">Waste recycling</td><td>Municipal solid waste (MSW) coverage</td><td></td><td>100%</td><td></td></tr><tr><td>Sanitary utilization of waste</td><td></td><td>95%</td><td></td></tr><tr><td>Share of recycled waste</td><td></td><td>40%</td><td>50%</td></tr></tbody></table>

The table 2 illustrates the main goals and targets of the concept, such as water resources, agriculture, energy efficiency, power sector, air pollution and waste recycling in order to decrease the adverse impact on environment and natural resources. These goals will be achieved, through investments in modern production technology and infrastructure in selected sectors. The EXPO exhibition 2017 is may be seen as another event in implementing green economy concept, and which has a potential to facilitate Kazakhstan's transition to the 'greening' its economy by attracting foreign investments as well as by supporting national industries and businesses.

To sum up, the green economy is a contemporary world's reality, and as soon as international community will increase its commitment to preserve the nature for the next generation, it may come into reality. Some may argue that economic growth will inevitably bring to the environmental footprints and deterioration of natural resources, however it is also true that efficient policy implementation by national as well as international community will greatly contribute to the fulfillment of this task.

Lemenkova P.A.
GIS Researcher, Moscow, Russia

LINKING DATA BETWEEN THE WORKSHEETS AND GIS DATABASES FOR EFFECTIVE MANAGEMENT OF THE ENVIRONMENTAL PROJECTS

<footnote n="8">⁸ Concept for transition of the Republic of Kazakhstan to Green Economy. Approved by Decree of the President of the Republic of Kazakhstan on May 30, 2013 #557.</footnote>

<footnote n="9">⁹ Concept for transition of the Republic of Kazakhstan to Green Economy. Approved by Decree of the President of the Republic of Kazakhstan on May 30, 2013 #557.</footnote>
**Introduction.** The effective organization of the data and linking various formats for smart environmental data management is the aim of this work. The research has been done during author's research stay at the Biogeochemistry Group of the Faculty of Natural and Environmental Sciences, University of Kuopio (Eastern Finland). Updating and structuring geodatabase, and data linking from different projects is the essential part of the environmental data management.

**1. Data compilation and preparing for the database.** During the first period of the work a lot of available thematic tables were compiled and stored on a computer, dedicated for database management. These data are mostly MS Excel thematic tables containing several different data: climatic, soil, chemical elements, relevant emissions or flux data (CO₂, N₂O and CH₄). The data are saved in their initial form and used for the main table 'Measurements.xls' containing all information in a standard form. All tables contain their metadata on the projects (coordinates, place and date of the measurements, researcher ID, publications from previous projects etc). At the next work step I set up the standards for the main table (number, contain and form of the thematic fields), in according with their requirements and suggestions about the intended database structure. After analyzing data content, the table 'Metadata_Finland.xls' containing common following fields with the explanation of their meaning was created.

<figure><img src="image_2.png" /><figcaption>**Figure 1-** Query of table of Selected_Attributes_of_Final_Measurements (left). Metadata content in the ArcCatalog showing full list of tables related to the main map (right).</figcaption></figure>

The data contained site code: index that is used for identification of the place where the measurements were taken. Second part of the measurements is that of chemical compositions fluxes (CO₂, N₂O and CH₄), their content measured at the different depths (5 and 15 cm), ground water level and type of chamber (the instrument which is used for measurements). A group of meteorological measurements: T° of chamber, T° of surface, T° of air <2 m above the surface and T° of soil at different depths (3, 5, 10, 15, 20, 25 and 35 respectively). Some tables contain additional data on further calculations using fluxes data, that is e.g.: log value of fluxes, intercepts calculated from slope for CO₂, N₂O and CH₄, ambient concentration of CO₂, N₂O and CH₄ from air samples, square values of CO₂, N₂O and CH₄, and also measurements of snow depth made during the winter measurements.

**2. Data processing and organizing.** The standard for the main table containing all the measurements made so far was set up. The essential fields representing information stored in all tables necessary for further research were filled out, and the metadata table Metadata_Finland.xls was created. The relative fields from the initial Excel tables were copied into the main table Measurements.xls. The table Metadata_Finland.xls contains the most important information on the fieldwork results, e.g. site code, place names, coordinates, relevant articles. etc. The core information is the measurements of fluxes of chemical compound: CO₂, N₂O, CH₄ indicating state of the soil structure. Implicitly it shows environmental situation of this part of the ecosystem, a so-called biotone. Secondary values can be calculated using these data for further investigations. The T° of air and T° surface are also important values showing the conditions of measurements taken. The data, coordinates and ID place code are primary information placed in the first rows of the metadata table. To make use of data more effective, from a lot of information I used only common for all to standardize the information warehouse.

**3. Database management.** The next working step included design of content of the main database. The logical concept of the database includes thematic blocks of the information e.g. Contributor details, Flux
data, Meteorological data, Normal climatic, Vegetation (physical and chemical values), Soil data. Ecosystem elements should be included into the content as well and also some technical details concerning the level of minuteness of the database. All coordinates were rewritten from geographic form into the decimal degrees. Thus, the geo coordinates of each location were converted into the decimal degrees, so that the metadata could be attached to the ArcGIS map. I used the convert calculations function that transforms coordinates from the geographic form (e.g. 63°35' N 23°53' E into 6358333, 23883333 degrees).

<figure><img src="image_3.png" /><figcaption>**Figure 2** – Main database table Measurements.xls unites all available values of the environmental data (upper left). Coordinates conversion into the decimal degree (below left). ArcCatalog: values stored in the geodatabase (upper right). Data stored in the MS Access (below right).</figcaption></figure>

To attach the existing database table to the GIS project I redrawn it so that the separate column containing the coordinates could exist. Using the columns “xCoord” and “yCoord” as the key columns it is possible to place attribute table at the map environment. For that purpose I modified the existing table, the metadata -“Metadata_Finland.xls”. The operation “past special” of Microsoft Excel was used for conversion rows into columns. Now I have separate columns containing x and y geographic coordinates, which can now be used for table placement into GIS.

Now the table contains all necessary information on measurements, coordinates and geographic location of the places where fieldwork were taken, reference information about the data (initial table), persons responsible for the measurements and their contact details. In some cases the data on ecosystem characteristics are also given, e.g.: vegetation and soil types, average temperatures, climate data, altitude, years and dates of fieldwork, snow depth, average temperature, relevant descriptions and pictures. The table “Metadata_Finland.xls” can be now used as a reference for the main “Measurements.xls” since the last one is very huge: it contains 47.451 rows and 23 columns, having as total more than a million of cells. Referring to the “Metadata_Finland.xls” for a quick look we can receive brief information on the measurements available for 19**-20** period of time and for any places studied. After that the columns containing the coordinates were also added to the main table “Measurements.xls”. These columns were imported from the
Metadata_Finland.xls and can now be used as indicator for placements in case if the placement of this table must be made as well.

<figure><img src="image_4.png" /><figcaption>**Figure 3** – Detailed properties of the attribute tables of the metadata converted into the Arc Map (left). Linked tables of the attributed metadata and table of measurement values (right).</figcaption></figure>

The next problem was dates in the measurements were not every time written in the same format: some of the data were in the Julian day format while others – in the classic calendar form (like 15.08.2001, for example). So that we had to have two columns of data: that one of Julian day for the reasons of proper calculating, and the calendar dates for understanding when the measurements were taken place. To calculate data from the Julian date into the calendar form the mathematical formula set of Excel was used which allowed to recalculate the necessary ciphers. As all the dates in Excel are referring to the 1st of January 1900 year, I had to extract the Julian days of all the previous Julian days from the meaning of the current day. After that I have got two columns containing calculations of both Julian and calendar days for every measurement.

After optimization logic and structure of the database measurements, improvements in the names and positions of the columns were made. Some columns containing measurements that are not common for each row and are individual for some measurement sets were deleted. I tried to make the structure of the database as clear and understandable as possible so that it can be used by others easily. From the main database I have set apart two additional thematic tables containing, meteorological measurements (soil and water temperatures) and, fluxes data (CO₂, N₂O and CH₄ measurements). After restructuring table, it got easier and logical view. Now I had to add data to the ArcGIS project to make them easily integrated into the GIS environment. In Arc Catalog I made storage of the geographically referenced data that can be displayed on the ArcMap and used for queries building (i.e., request all measurements made on the given day or year). To add data to the ArcGIS I first converted them from Excel into the MS Access format through data import to avoid any lost of data format: numbers, decimal degree, text format.

After all data are stored in the MS Access, a structured database finally contains: 1) Measurement data: file containing preliminary values of measurements before final revision 2) final values of measurements: main table where data are stored structurally (calendar days) 3) Metadata with explanations about origin of data and additional information 4) GDB Feature classes: part of logical structure of ArcGIS, data divided by their type-points, lines and polygons (only necessary for the ArcGIS). 5) Medatada indexes (for points, line and polygon objects): additional information for all data in the ArcGIS – their number and coordinates.

Afterwards, the data were imported into the Arc Catalog where all multiple data sources (datasets, relational databases, file types and schemas) can be stored. At the left side of the Fig.26 the workspace can be seen – a directory where all other sub-directories were stored. In turn, they hold numerous GIS datasets and a series of ArcGIS documents related to the FINLAND.mxd map. Our workspace contains now external Excel and Access datasets of measurement data in the .xls and .mdb file formats, as well as references to the geodatabases. For example, it is now possible to use file geodatabases and folders containing numerous data files-e.g. ESRI shape files, coverages, jpg images, dxf CAD files, dBase tables, Excel spreadsheets, etc. Once the tables are added to the ArcCatalog environment and projected the same way as the Finland map, now it is possible to set any logical SQL queries. One example of ArcGIS-based query is searching all data in one place. Now it is possible to make queries in the ArcGIS, as all tables are spatially projected and their
content can be visualized on the map: exact geographical location, value and type of measurements, additional information, incl. paper published, etc). Any new data can be easily added to the project via the Editor Function by the ArcGIS.

Once data are organized in Geodatabase by the workspace, it can provide a way to the logical and spatial operations: controlling access to datasets with measurements; providing transaction framework for updating and sharing datasets when new data received at the fieldwork; organizing, documentation and storing in a catalog geographic datasets. Final content in the Geodatabase workspace includes: ArcGIS files, e.g. FINLAND.mxd ArcMap with the Finland shape files (.shp) and maplayer files (.lyr), External datasets of Excel tables; Subfolders containing file geodatabases in different coordinate systems (Finnish national and world ArcGIS coordinate system), Microsoft Access files (.mdb) containing personal geodatabase.

**Conclusion.** Current research demonstrated effective data management for environmental research. So far, the data were stored by individual researchers responsible for their own projects. Now the data are collected together in a very structured system. Thus, it is possible to add all further information (other relevant data) into this database systematically and to use them for the thematic research. The necessary amendments or additions of the information can be done as new data from the field expeditions become available. The data of both terrestrial and aquatic ecosystems of Finland with the key information of fluxes measurements of chemical elements (CO₂, N₂O, CH₄) are now collected in one main Table Measurements.xls with a separate catalogue of all this data: Metadata_Finland.xls containing essencial information and site codes. The Database can now be used for environmental analysis, e.g. detecting changes in the state of the ecosystems, predicting their behavior in the future. Therefore, it is now possible to make the environmental prognosis of the Finnish ecosystems, to analyze their conditions, behavior and sustainability. During the research the software ArcGIS 9.1 (including its cartographic module ArcMap and the ArcCatalogue) and Microsoft products (Word, Excel and Access) were used.

Zhumagaliyeva A.N., Tauassarov E. K., Doszhanov Ye.O.

Al-Farabi Kazakh National University,
Almaty, Kazakhstan

PROSPECTS OF SHUNGITE AS A LOW-COST SORBENTS FOR WASTEWATER TREATMENT, BIOREMEDIATION OF SOILS AND VARIOUS WASTE OF PETROLEUM

Shungite is a black, lustrous, non-crystalline mineraloid consisting of more than 98 weight percent of carbon. It was first described from a deposit near Shunga village, in Karelia, Russia, from where it gets its name. Shungite has been reported to contain fullerenes. There are currently explored "Koksu field" in Kazakhstan with confirmed reserve of 49 million tons of shungite. It comprises both solid shungite carbon, and substantial amounts of silica; both of these components are presented in it is very reactive forms. In this regard, it can be used as a reducing agent in metallurgy and – simultaneously – as a SiO₂-containing flux and a source of silicon[1-3].

Shungite has been regarded as an example of abiogenic petroleum formation, but its biological origin has now been confirmed. Non-migrated shungite is found directly stratigraphically above deposits that were formed in a shallow water carbonate shelf to non-marine evaporitic environment. The shungite bearing sequence is thought to have been deposited during active rifting, consistent with the alkaline volcanic rocks that are found within the sequence. The organic-rich sediments were probably deposited in a brackish lagoonal setting. The concentration of carbon indicates elevated biological productivity levels, possibly due to high levels of nutrients available from interbedded volcanic material.

The stratified shungite-bearing deposits that retain sedimentary structures are interpreted as metamorphosed oil source rocks. Some diapiric mushroom-shaped structures have been identified, which are interpreted as possible mud volcanoes. Layer and vein shungite varieties, and shungite filling vesicles and forming the matrix to breccias, are interpreted as migrated petroleum, now in the form of metamorphosed bitumen. Shungite formed from organic sediment – sapropel. These organic sediments covered up on top of all the new layers of gradually compacted, dried and immersed in the depths of the earth. Under the influence of compression and heat went slow process of metamorphism. As a result of this process formed sputtered in the mineral matrix of amorphous carbon in the form typical for shungit's globules. Globules confirm spherical or ellipsoidal formation of carbon on the average size of 10nm, which was found in the presence of