Using the synergy of QFIELD for collecting data on-site and QGIS for interactive map creation by ALKIS® data extraction and implementation in PostgreSQL for urban planning processes

The German law concerning Urban Development Promotion (Städtebauförderungsgesetz; StBauFG) is an important component of the Building Law (Baugesetzbuch). It enables municipalities to be financially supported by the federal and provincial governments for promoting urban development in downtown areas being in need of renovation, maintaining historical centers, or enhancing the value of socially imbalanced areas. Therefore, the law plays an important role for the economical, ecological, social and cultural status of cities. If an urban renovation area is formally declared the reconstruction measures taking place there lead to an upgrading of the real estate land values. The present work contributes on one side to the initial phase for the declaration of an urban renovation area and on the other side to the final phase comprising the legal accounting procedure. At first, the city planners must document the urban status concerning the structure and quality of buildings, vacancy rate for housing and industry, road condition and numerous other urban quality and structure deficits. To acquire these data, the open-source GIS plugin QField serves as an appropriate and easy to handle tool installed on a tablet for the urban planners to collect the necessary data on-site. The planners can then easily assign defined qualities and states of the objects on a map or edit and comment new objects and attributes. Through automatic updating of these data in the PostgreSQL-database, an interactive map in QGIS will then be automatically created in Python. For the last phase, the legal closure of a declared renovation area, our work supports the analysis and modelling of the relationships in the Authoritative Real Estate Cadastre Information System ALKIS® in order to identify the property owners being charged a so-called balance payment for the upgrading of the standard land values resulting from the applied renovation measures. The work shows the high potential of the ALKIS® data being processed with open-source software like PostgreSQL, QGIS, and QField towards a more effective urban planning.


Legal background of the renovation areas
Since the Urban Development Promotion Law (Städtebauförderungsgesetz StBauFG) was passed in 1971, more than thousand renovation areas in Germany were designated with the aim to maintain, modernize, and revitalize the urban structure and to improve the living conditions for their inhabitants. Different promotion programs focusing on e.g. social aspects, historical monuments conservation, or urban parks are financed by the German federation, the German states and the involved municipalities. To participate in such a promotion program, the municipality must apply for and to carry out urban investigations proving the necessity to be promoted ( Figure 1, Phase 1). In this phase, the city planners have to document the urban structure and its quality deficits. In this work, we present our approach for an automatic procedure to support this process by the integration, adaptation and extension of open-source software tools.
In the urban planning process, if it is proven that the renovation area will be financially supported, the ongoing renovation process however could take place over decades (Figure 1, Phase 2). If extensive renovation measures in these areas were carried out within the time, the German Building Law Baugesetzbuch ( § 154 BauGB, , see BauGB, 2018) requires to charge the owners of the affected land parcels a payment due to the upgrading of their property's real estate value (Figure 1, Phase 3). To draw up the accounts of this so-called compensation payments, either supporting private companies or the municipalities themselves could highly profit from a quick and accurate compilation of the required data e.g. names and addresses of the parcel owners and IDs of the land charge register. This information is stored in the German Authoritative Real Estate Cadastre Information System ALKIS® (AdV 2009). In the present work, ALKIS® data is used accordingly for an efficient and precise accounting procedure.

ALKIS data
ALKIS®, established in all federal states in 2015, combines and integrates data of the former cadastral map as well as the former property registry. In ALKIS®, for the first time, spatial and nonspatial related data were kept together systematically redundancies-free. The consortium of the Surveying Authorities of the States of the Federal Republic of Germany (AdV) developed a functional design to manage all basic geodata of the official surveying and mapping. All federal states agreed upon a unique data model using the UML standard (AdV, 2009, Seifert 2005. With the introduction of ALKIS® and its unique data management capabilities, Germany sticks to international standard specifications of ISO and OGC. ALKIS® data consists of various object types like land parcels, streets, buildings, owners of land parcels, and other owner's related data from the land charge register. The conceptual design of the ALKIS® data defines different kinds of relations between the object types encoded in GML format (Lake et al. 2004, Portele 2007, AdV 2009) and has been re-modeled in this project for the needs of the accounting procedure ( Figure 1, Phase 3).

Advantages of data acquisition on-site
The need for rapid and accurate data acquisition technology is getting more and more important not only for civil engineers or land surveyors but also for city planners: working on-site by having an appropriate device with entry capabilities as well as full access to all necessary data. Furthermore, there is a high demand for an easy editing tool to add new geometries and to modify attributes of existing geodata. Our work replaces the established methods for city planners, who have been manually drawing relevant features on-site for the documentation of urban structure and quality on analogue maps. In this existing procedure, a CAD or GIS-expert, not necessarily the same employee as the one working on-site, would process the new information later on a desktop computer, where the chance of failures or misunderstandings is high, so further checking is required. This procedure, for each documented measure status within the duration of a designated renovation area, demands the cooperation between different formats and different experts. For each project status, new maps have to be created, exported or printed and submitted to the involved stakeholders. Our approach however allows working with the QGIS Plugin QField on-site, which automatically updates the existing information in the PostgreSQL database. This enables interactive map output to represent all requested time stamps which substitutes the conventional fault-prone method.

METHODOLOGY
The workflow of the present project uses the official available ALKIS® data as basis for further geometric assigning and editing. The initial step consists of the automatic import of all spatial features into the open-source PostgreSQL database management system (see Figure 2).
Before the city planner can use these preselected pre-processed ALKIS® data on-site with the QGIS Plugin "QField" on an appropriate digital device such as a tablet, the layers have to be configured in a QGIS project for QField synchronization.
While editing and enhancing the cadastral base-data on-site with QField, the modified geodata will be automatically updated in the PostgreSQL database. A precondition for the update is an internet access on-site. The updating on the fly yields an immediate visualization of the geometrical and attributional changes made by the city planner. For the result reports where certain time stamps of the renovation measures have to be documented, a defined template in QGIS will be activated and presented to the stakeholders involved in the process. If the execution of a renovation area process is coming to its end, the relationships being defined in the original ALKIS data can be evaluated in the PostgreSQL database and exported for the use of the accounting procedure.
Figure 2: Workflow for the processing of the ALKIS data to support urban development monitoring.

ALKIS import into the PostgreSQL database
The first aim is the import of ALKIS data in the GML-based NAS format (norm-based data exchange interface) to the PostgreSQL database with the PostGIS extension. NAS, as a complex GML (Geography Markup Language) profile, needs specific 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). After correct setting of projection and coordinate system the data import needs approximately 9 minutes for a medium sized municipality. The ALKIS data consists of more than 200 object layers with different geometry types. However, for the present application "documenting and monitoring the ongoing renovation area" only 25 object layers are needed.
Before starting the layer preparation in the PostgreSQL database via SQL coding (see Figure 3), the relevant data layers for further processing have to be defined. One of the important layers is the land parcel layer containing the polygon geometry and, as attribute the unique identifier of each registered land parcel within a municipality, consisting of the counter and the denominator number. Another important layer is the address layer including street names and house numbers as well as the defined angle for labelling (Ostadabbas et al., 2019).

QField plugin configuration
QField allows to work efficiently on GIS data on-site. Its optimized user interface for Android devices hides the full power of QGIS under the hood (OPENGIS.ch 2019). This extension gives a chance to collect the relevant data related to the geometry of each building or land parcel according to the purpose of the city planner. By transferring the newly collected data into the PostgreSQL database, which has a connection to the QField layers, a Python script using the PYQGIS library and its extensions ensures the creation of a specific map layout on the fly.
QField serves as a platform and user interface, but most of the configurations must be done in QGIS desktop (see Figure 4). Figure 5 shows the configuration of the layer attributes afterwards presented on the QField interface, which then can be used by city planners for assigning and editing the data on-site. Furthermore, it offers the possibility to define certain widget types of the attributes. Its drop-down menu option is user friendly and easy to use. Some other presentation options such as check boxes will make the data collection process on-site more effective. For the monitoring process of the renovation measures, photos must be taken by the planners which will be saved in a defined directory according to the chosen widget type.
After specifying the QGIS project the "Package for QField" must be configured for the synchronization of the relevant geodata for the device used on-site (see Figure 4). After opening the QField application and loading the configuration file, the same map which was created before in QGIS desktop will be shown on the device. Now the QField application offers most of the wellknown functionalities of QGIS desktop, but simpler and more intuitive.   Figure 6 shows the QField application interface on a tablet. The table of content on the left site of the display lists all layers, their symbology, and legend description. By using the touch screen and selecting the geometry of a land parcel or a building, the drop-down menu opens and offers defined operations, i.e. adding attributes or editing the geometry of the features. The developed Python script running in the background renders the edits on the fly by changing the symbology or geometry.
The International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences, Volume XLIII-B4-2020, 2020 XXIV ISPRS Congress (2020 edition) Figure 6: QField interface of intuitive data acquisition on-site.
After finishing data acquisition and update on-site the new or updated geodata and its attributes will be synchronized via the QField plugin in QGIS desktop and finally in the PostgreSQL database. Hence, all data will be accessible from the PostgreSQL database, where acquired geometries can be checked afterwards using the visualization option ( Figure 7).
The storage of the new acquired data in PostgreSQL provides the possibility to be directly accessed through an accounting management system based on PostgreSQL tables as well. These new acquired data consists of information being of great importance to the accounting procedure. Figure 7: Updating process of the acquired data from QField in PostgreSQL database.

Python code development for map creation
Python, available under an open-source license approved by the Open Source Initiative (OSI), is freely accessible and distributable, even for commercial aspects (Python, 2018, Lawhead, 2017. The code developed in this project focuses on categorizing, labelling, and assigning the appropriate symbology for the layers of the maps in QGIS desktop. Figure 8 shows an example of a Python code categorizing spatial features according to different properties, i.e. usage of buildings.  Figure 6).

Supporting the compensation process by SQL coding
When the former described procedure of a renovation areawhich could last for a decadeis legally terminated, accounts for the compensation payments from the parcel owners have to be set up by the municipalities. Such payments become due if the renovation measures increased the real estate value of a land parcel. For efficient data proliferation a compilation of all relevant owners' data is performed. As the information from the ALKIS® data is stored in several relational tables, the relationships must be evaluated using SQL for final export of the owner data.
The SQL script in Figure 9 evaluates the relationship between six tables with owners' data. The relational join A, a one-to-one relationship, defines the connection of each parcel owner, his address and birthday and other personal data. Relational join B, a one-to-many relationship, evaluates the number of owners for each land parcel. The owners can be individuals, companies, or officials. The full join C (many-to-many) represents an intermediate step, because there is no direct connection between the owners and the land charge register (relational join D), therefore the script links the land charge register to the geocoded location stored in a so-called accounting sheet (Buchungsblatt). The last relational join E (many-to-many) evaluates the connection between the parcel number, consisting of a counter and denominator, and the results achieved so far. To put it in a nutshell, it means each land parcel owner can own more than one parcel as well as each land parcel can have more than one owner (Behr, 2011). The final compilation can be exported in CSV format for further processing. Figure 9: SQL code for automatic relational connection between the tables according to the specified relation types.

CONCLUSIONS
The described workflow follows the legal procedure of a renovation area, where city planners have to document and monitor the measures and their impacts on an urban structure and quality. With open-source software such as QGIS and QField for data acquisition on-site and the storage in PostgreSQL database for further accessibility of other management systems, we presented an effective workflow that could be established and proved in practice. The procedure is less time-consuming and less error-prone for city planners as working with analogue maps onsite, based on Further research and development could be done to insert the third dimension in terms of digital elevation models (DEM) and 3D building models (BIM) to get a better visualization of the renovation area throughout the time. With regard to climate protection measures in urban surroundings, the third dimension could help to calculate the potential of regenerative energy such as solar or photovoltaic on building or landscape surfaces and optimize the already installed facilities (Bruse et al. 2015, Coors et al. 2016).