CityGML integration | Fabrice Lainard

Article published in January 2020, translated from French. The versions mentioned (PostgreSQL 12, PostGIS 3, 3DCityDB 4.2) are those of the time; the tools have moved on since.

CityGML is a standard for representing a city in XML. And in the age of IoT and other technologies, what could be nicer than having an entire city in 3D in our applications?

The goal of this article is to build a small CityGML processing pipeline: importing a city model, displaying it in 3D, and exploring the elegant data structure provided by the designers of this standard.

The pipeline: CityGML, 3DCityDB import into PostgreSQL / PostGIS, KML export, 3D display

For our integration, we will import the CityGML of the city of Lyon into a PostgreSQL / PostGIS database. This step is done with the open source tool 3DCityDB (3dcitydb.org). 3DCityDB automatically loads the city model into a semantically rich set of tables that third-party applications can use.

Installing PostgreSQL / PostGIS

We need to install a PostgreSQL 12 server on our machine (Windows for this article): enterprisedb.com. Don't panic if you have version 9, it should work.

Using pgAdmin, create one database called city3D_RGF93 and another called city3D_WGS84. Add the PostGIS extension to both with CREATE EXTENSION postgis;. If, like me, you installed PostGIS 3, you also need to add the raster extension by hand: CREATE EXTENSION postgis_raster;

The two databases city3D_RGF93 and city3D_WGS84 in pgAdmin

The first database will hold the city of Lyon in its native projection, RGF93. The CityGML file is indeed provided in this coordinate system:

Excerpt of the Lyon CityGML file, in RGF93 coordinates

The RGF93 SRID is defined here: epsg.io/3946.

The second database will hold the city in the WGS84 projection, suitable for display on the globe. I use two databases here as a quick way to convert the coordinate system from RGF93 to WGS84. There are other ways to do this, no doubt :)

Installing 3DCityDB

Now that our databases exist, we need to install the 3DCityDB tool to import and export CityGML. Download its installer here: 3dcitydb.org/3dcitydb/downloads.

Run 3DCityDB-Importer-Exporter-4.2.0-Setup.jar and install the application.

Then follow the procedure for initialising our PostgreSQL databases in chapter “3.3.4 Installation steps on PostgreSQL” of the user manual, found in the 3DCityDB-Importer-Exporter\manual folder. It is extremely simple: just edit the CONNECTION_DETAILS file in the 3DCityDB-Importer-Exporter\3dcitydb\postgresql\ShellScripts\Windows folder.

Note: these steps must be done for both databases, city3D_RGF93 and city3D_WGS84.

PGBIN=E:\Program Files\PostgreSQL\12\bin
PGHOST=localhost
PGPORT=5432
CITYDB=city3D_RGF93
PGUSER=postgres

Run the CREATE_DB script using SRID 3946.

Do the same with the city3D_WGS84 database, without forgetting to change the contents of the CONNECTION_DETAILS file. For this database, the SRID is 4326. And that's it, our two databases are ready to receive data!

Easy…

Importing the city of Lyon

Now download the city of Lyon from the following address: textured 3D model of Lyon’s 1st arrondissement (data.gouv.fr). Only the LYON_1ER_2015.zip file is needed for this article. Unzip the archive anywhere on your disk.

Launch the 3DCityDB importer / exporter found under \3DCityDB-Importer-Exporter\bin:

The 3DCityDB importer / exporter launcher

Then, in the Preferences → Database → Reference systems menu, add two reference systems as follows:

Declaring the RGF93 reference system (SRID 3946)

Declaring the WGS84 reference system (SRID 4326)

In the Database menu, add two connections to our databases:

Next, import your city into the city3D_RGF93 database.

Then export our RGF93 database as WGS84 CityGML, using the Export tab.

Finally, import the WGS84 CityGML into our WGS84 database.

Easy…

Exploring with SQL

The 3DCityDB database is very well designed. It may look complex at first, but reading chapter 2.3.3.2 Core Model is enough to quickly get a grip on its structure.

All objects live in the citydb.cityobject table. Each record in this table has a type, which tells you what kind of object you are dealing with. All the classes are defined by the OBJECTCLASS enumeration. For example, class number 26 is a building:

The 3DCityDB object class table: class 26 is Building

So, to list all the buildings, the following query is all it takes:

SELECT * FROM citydb.cityobject WHERE objectclass_id = 26;

To get the list of a building’s 3D polygons, we can query the citydb.surface_geometry table:

SELECT citydb.surface_geometry.id, ST_AsText(geometry)
FROM citydb.surface_geometry
WHERE cityobject_id = 85;

And so on. Note that the database handles, among other things, levels of detail (LOD), to optimise display in 3D rendering engines.

3D display

Display in your own 3D engine can be done by reading the data straight from the database, or by converting the model into a 3D file such as KML.

Export the contents of the city3D_WGS84 database as KML (KML/COLLADA/glTF tab).

Download and install Google Earth Pro on your machine (free): google.com/earth/versions.

Then open the exported KML file in Google Earth.

Easy…

We have only scratched the surface of what CityGML and the 3DCityDB platform can do. Even so, it is already clear that integrating a CityGML pipeline into our tools is not that hard, and that it can be a real competitive advantage!

For more information: LainardFabriceProgramming@outlook.com.