DATABASE TRANSFORMATION, CADASTRE AUTOMATIC DATA PROCESSING IN QGIS AND IMPLEMENTATION IN WEB GIS

On the judicial basis of § 196 Baugesetzbuch (BauGB, 2018) every municipality in the Federal Republic of Germany must publish standard land values in €/m2 for its whole district area in a biennial cycle. The standard land values have to be derived comprehensively for different land use categories within this district. With the help of Geographic Information Systems (GIS) all the necessary data for the derivation of the standard land values can be analyzed. This data consists of cadastral information (ALKIS) (AdV, 2008) for each municipality district like streets, buildings, land parcels, areas of application for special building law as well as geocoded data about purchasing prices within the district’s real estate transactions. The following work contributes to both requirements, the statutory determination of the standard land values answering the legal necessity and the setting up of the generated geo data in the Web according to the transparency demanded by the European INSPIRE (Infrastructure for Spatial Information in Europe) directive. This report describes the implementation of the automated process of the setup of the relevant data layers based on ALKIS data in an Open Source GIS (QGIS) using SQL and Python scripts. The standard land values are visualized and presented in a Web GIS. After the evaluation of different alternatives, QGIS Server and Lizmap, an Open Source QGIS Web Client, were used for the web based presentation and accordingly configured.


Legal background
The German law "Baugesetzbuch" (officially abbreviated BauGB) with its predecessor, the Federal Building Law (BBauG), is the significant law and toolset regarding planning and building in Germany. Its definitions have a great influence on the shape, structure and development of municipalities.
It is coming into force in the year 1960 gave birth to the institutions "Real Estate Value Advisory Committees" (Gutachterausschüsse) in the Federal State of Germany. The aim of these institutions is to bring transparency to the real estate market (Höhn & Schaar, 2011). One of the central tasks of these institutions is to derive standard land values according to § 10 of the ImmoWertV (Real estate value inquiry act, ImmoWertV 2010). The standard land values will be derived mainly out of the purchasing prices. This means, that the institutions are authorized to receive the contracts of purchase from the notaries. As in the state of Baden-Württemberg there are more than 900 Real Estate Value Advisory Committees, some of them are not able to fulfil the target to derive the standard land values because of lack of knowledge or human capacity. The other states of Germany chose bigger administrative levels for assigning their real estate advisory committees.
Dr. Koch Immobilienbewertung GmbH has been working on the derivation of the standard land values since 2012. The competence of the company lies in the field of real estate valuation. Its clients are public administrations, municipal and state owned companies, investors, private companies or private individuals. For geo-data analysis and map creation the company used a proprietary GIS.
All the necessary working steps to obtain standard land values were performed iteratively in this GIS, starting with the conversion of object classes from the ALKIS data, encoded in the GML-based NAS format (Normbasierte Austausch Schnittstelle) (Seifert, 2005, AdV, 2008, saving them into a geodatabase, selecting, aggregating and assigning defined symbology to certain object classes and preparing the map output. For the derivation of the standard land values different additional geospatial and attribute data had to be inserted and analysed, such as urban land use plans and the collection of all purchasing prices. The results are spatial zones with unique standard land values given in €/m² (Bundesministerium für Verkehr, Bau und Stadtentwicklung, 2011) as depicted in Figure

Land Standard Values
According to § 196 BauGB the standard land values have to be derived and published by the Real Estate Value Advisory Committees on the basis of the collection of purchasing prices in a municipality or on a larger spatial administrative level. The directive "Bodenrichtwertrichtlinie" (RW-RL , see Bundesministerium für Verkehr, Bau und Stadtentwicklung, 2011) gives detailed information how to derive the values, which data basis to use and how to publish the standard land values.
Standard land values should be comprehensively derived for all land use within a municipality, i.e., for residential and industrial as well as for agricultural and forest areas. The areal properties influencing the values should be quite homogenous within a standard land values zone. A new residential area, for example, which is dominated by one-family dwellings, a floor-space index of 0.5 (defines the ratio between floor-space and space of the whole parcel) and similar location qualities (e.g. slope exposition, wide roads with sidewalks) will be assigned with one unique standard land value for this specific zone.
A border has to be drawn around these zones with an appropriate and unique standard land value; either derived from real purchasing prices in this residential area or if there are no prices by means of other applicable methods described in the BRW-RL, such as the hierarchical goal tree method, based on weighted means.

Necessity of automatization
The described process to determine standard land value zones and to create the maps was carried out with the help of a commercial GIS. Numerous manual steps had to be performed to select, aggregate, assign and categorize the relevant data layers which are part of the Database Tranformation, Cadastre Data Processing in QGIS and Implementation in Web GIS (10324) Hamidreza Ostadabbas, Heike Weippert and Behr Franz-Josef (Germany)

FIG Working
Week 2020 Smart surveyors for land and water management Amsterdam, the Netherlands, 10-14 May 2020 cadastre land register (ALKIS). As this procedure is vulnerable, time and cost intensive (licence fees) the need to automize the main functionalities arise. The concept was developed to migrate the process completely from the proprietary GIS to an Open Source GIS (QGIS), database (PostgreSQL) and Web GIS (QGIS server and Lizmap as Web client) to be financially independent and to adapt possibly the tools to specific requirements by programming individual plugins. The overall goal is a continuous supply geospatial chain (see Schmitz 2007) encompassing all the steps and integrating all data sources to derive the desired output according to the law.

METHODOLOGY
In the following subsections this semi-automated process for the creation of the standard land value map will be described. The schema in Figure 2 explains which datasets are necessary for the data procession. Besides the cadastral land register (ALKIS) all kinds of urban development plans, additional geo data if available, like orthophotos, digital elevation models, ad as the most important source, the collection of the purchasing prices for the whole municipality are needed.
The datasets are analysed, processed and evaluated. The result will be shown in a Web GIS application where the standard land values and its important attributes could be retrieved.

Importing layers into the PostgreSQL database
A first aim is to import the ALKIS data in NAS format to the PostgreSQL database without using a GIS. NAS, as a complex GML profile, is not easy to import with general GML drivers. Therefore ALKIS-Import 2.1-24 from norBIT (norBit n.d.) has to be installed using OSGeo4W, a "binary distribution of a broad set of Open-Source Geospatial software for Windows environments" (OSGeo4W, 2018). The correct projection and coordinate system have to be specified.
The ALKIS data consists of more than 200 layers with different geometry types. Hence data loading even for smaller municipalities up to 10,000 inhabitants takes approximately 9 minutes. However for the determination of standard land values only 25 layers provide important information and are extracted.

Layers preparation in the database by means of SQL-coding
Before starting the layer preparation in the PostgreSQL via SQL coding the data layers on which procession should be taken out have to be defined. One of the important layers is the land parcel layer containing the polygon geometry and, as attributes, the counter and the denominator numbers, which combined form the unique identifier of each registered land parcel within a municipality. The land parcels represent the smallest geometry level. Another important layer is the address layer including street names and house numbers as well as the defined angle for labelling.
Additionally a blank layer representing the standard land value zones has to be compiled. The geometry type is defined as polygon using the PostGIS extension and the attributes have specified abbreviations and type formats given by the BRW-RL. The digitization has to be carried out later manually on screen according to the delineated zones defined by experts in the company.

Python Code development process
Python is available under an open source license approved by the Open Source Initiative (OSI). Hence it is freely accessible and distributable, even for commercial aspects (Python, 2018). The code developed in this project focuses on categorizing, labelling and assigning the appropriate symbology for the layers of the standard land value map on QGIS desktop. Figure  4, as an example, shows the Python code for labelling street names with specific rotation angles. Before starting the code a connection between the database and QGIS desktop has to be set up by using the setConnection function. By running the automatic part of the script the map will be created. This mapping process takes only just 20 second which is approx. thousand times faster than the performance of the manual working steps, even for an experienced GIS user.

FIG Working
Week 2020 Smart surveyors for land and water management Amsterdam, the Netherlands, 10-14 May 2020 The described method is robust, less time intensive and less vulnerable for careless mistakes. Figure 5 shows the final mapping result with labels and land use zoning coloured according to legal regulations.

DATA VISUALIZATION IN WEB GIS
As requested by several municipalities the created mapping result should be accessible for the public in the Web. Therefore the QGIS Web server was chosen. QGIS server utilizes QGIS as the backend for GIS operations and map rendering. In addition, the Qt library is being used for graphics. Unlike other WMS programs, the QGIS server uses cartographic policies as a setup language, both for server configuration and for user-defined cartographic guidelines (QGIS community, 2018).
Since QGIS desktop and QGIS server are taking advantage of the same graphical libraries, maps that are published on the Web look identical to their presentation on desktop GIS (QGIS community, 2018).

Lizmap Web client
A precondition for the Web application was the possibility to query on the smallest geometry, the land parcels, and to get back all the necessary parcel properties for the user like the standard land value in €/m², floor-space index, development condition of the parcel etc. The Web client Lizmap provides the requested functionalities like navigating, printing, lay outing the client surface and querying on a feature layer.
Lizmap is Open Source software designed by 3Liz. The Lizmap software must be installed on both sides, client and server. As a requirement on client side, Lizmap is installed in QGIS desktop as a plugin. It provides the possibility to add tools which are necessary in the Web map user interface. On server side the visualization is based on the JavaScript library OpenLayers which handles the WMS requests to the QGIS server. Specifically the "Map" option dialog on client-side provides important functionalities for Web mapping. Map tools such as printing, measure tools, zoom history and address searching are selectable. Figure 6 shows one of the results. The popup dialog displays relevant attributes of a parcel, retrieved by WFS requests, when the user is searching for a specific address of a land parcel.

CONCLUSIONS
The developed procedure with the semi-automatic creation of the standard land value map represents an example of how Open Source technology can help to improve and fasten up processes of implementing, aggregating, categorizing and symbolizing geo data for effective mapping. Further research and development should be done to progress the concept according to connect the programming codes (SQL and Python) for importing the ALKIS-data into the database and preparing and configuring the relevant layers according to the layout specifications. Further research can be done by extending the capabilities by developing client side functionalities for the Lizmap Web client or as well for the QGIS Web client. Such further achievement can be adding height data from digital elevation model (DEM) to have a three-dimensional map presentation and also 3D building models rendered with their facades for better visualisation.