What to use for PostGIS Data Warehouse?

[SeppBerkner]

Hello everyone 👋

I am working on building a Data Warehouse for my company. As we use extract many different spatial datasets of hundreds of different sources the selection of the data storage engine was Postgres + PostGIS. Now I have already build several data products in my data warehouse and I am currently working on the task of adding CityGML files from governmental websites into the data warehouse. As I am only using Open SOurce software (dlt for data retrieval, dbt for data transformation and dagster for orchestration) I am wondering how to integrate the data of CityGML into my data warehouse without using the Importer-Exporter GUI of 3DCityDB. Mainly I am interested in data that I can retrieve from the 3D models than the actual 3D model itself. So for instance, I want to get the slope angle and slope direction of the roof as well as the height or foot print area of the building. On my research I found the CityJSON project and I think your appraoch for a more efficient parsing of the cityGML files through a JSON format is exactly what I am searching for. So here are my actual questions:

1. How active is the development of CityJSON. Does it have traction and can I assume the project will not be archived? Will you implement representation of CityGML 3.0 in a near future?
2. As I usually do my transformation in the database is it possible to import the raw CityJSON file into the PostGIS database and run my transformations / data retrival in SQL? Or would you recommend to do these steps before loading it into the data warehouse and only load the statistics in it once it is retrieved from the JSON file using Python? (I would prefer the former to stay conceptially consistent in my warehouse
3. Would you actually recommend me using CityGML directly and the CLI tool of the Importer-Experter of 3dCityDB and installing 3DCityDB on my Postgres?

Would be happy to hear your thoughts. And thanks for creating such a great tool!

Cheers from Germany,
Sepp

[balazsdukai]

Hello,

welcome to the CityJSON project :-)!

1. How active is the development of CityJSON. Does it have traction and can I assume the project will not be archived? Will you implement representation of CityGML 3.0 in a near future?

It is active and will stay so. It is quite widely known and used already. CityJSON is an OGC community standard (if that tells you anything). The latest version, CityJSON 2 implements CityGML 3.0.

2. As I usually do my transformation in the database is it possible to import the raw CityJSON file into the PostGIS database and run my transformations / data retrival in SQL? Or would you recommend to do these steps before loading it into the data warehouse and only load the statistics in it once it is retrieved from the JSON file using Python? (I would prefer the former to stay conceptially consistent in my warehouse

If you need to compute the required geometry statistics from the 3D model, as opposed to getting it from an existing attribute, then you need to calculate them from the model before loading it to postgres (eg. in Python as you said).
For simple storage-retrieval, basic attribute and location queries, there is cjdb, although it is still experimental. It stores most of the cityjson document as jsonb in postgres, so you would need to get the attribute values from the json blobs. But for geometry processing, cjdb won't help you, because it doesn't create postgis geometries of the 3D models. It only converts the 3D models ground surface to support 2D spatial queries. See queries and schema.

3. Would you actually recommend me using CityGML directly and the CLI tool of the Importer-Experter of 3dCityDB and installing 3DCityDB on my Postgres?

No. 3DCityDB create very heavy databases, as it (over-)normalizes everything in the citymodel. So you'll end up lot's of tables and lots of joins to do simple operations.
Personally, I would preprocess the models and only load the relevant result to postgres. Without knowing more about your setup, this seems to me the more efficient approach.

In case you haven't seen it, there may be some software here that could help you.

[clausnagel]

No. 3DCityDB create very heavy databases, as it (over-)normalizes everything in the citymodel.

Obviously, I cannot agree to this at all :-) The 3DCityDB v4 uses a simpe feature-to-table mapping approach which follows standard relational modelling practices and is really easy once you understood it. And in contrast to cjdb, every attribute and geometry is directly available. So you could easily use database functionality for your spatial analysis and calculations. The schema for the upcoming 3DCityDB v5 (under development) is even more simple.

Whether the 3DCityDB is a choice in your development is another question. If it's just about collecting statistics, it might be easier to do this file-based (either CityJSON or CityGML).

[balazsdukai]

Hahaha sorry @clausnagel , I thought I'm the only checking GH issues on a Saturday morning.
Of course, I cannot argue with you on the matters of 3DCityDB. And it is true, I haven't kept up with the newer versions of 3DCityDB.

[clausnagel]

All good, @balazsdukai. cjdb and 3DCityDB are both valid solutions just following a different paradigm. Sorry, @SeppBerkner, didn't want to distract from your original questions. You can drop questions regarding 3DCityDB here if you think it might be an option for you.

[SeppBerkner]

@clausnagel @balazsdukai Wow, thanks for your replies. Didn't expect getting them on a Saturday morning 😄

I studied Geoinformation Science at the TU Berlin (written my Master THesis with Mr. Adolphi :) ), so I was quite versatile with the CityGML format, but that was almost a decade ago. 😅 And since then, I haven't worked with CityGML anymore. And now that I have both of you here I use my chance and explain you my use case in a little more detail:

I am working in a consulting company and am responsible to modernize the data management. One task is to create a centralized data warehouse which is then used by business analysts for BI, ML scientist for building models and developers to integrate into our software products. One important feature is our building model, which includes several statistics of a building for things like heating potential, solar potential or broadband connectivity. Now the scripts that currently create these statistics (and which I did not look through yet) use (among other data) the LoD2 data provided by German states. However, these scripts are pretty error prone and unorganized and one of my tasks is to automate the procedure (from data retrieval to the final data product) including clean up the scripts and integrate the results into the DWH. So I researched online a bit and came across the CityJSON format, which seems to solve the issue of cluttered XML parsing scripts and a more efficient way of getting my statisics out of the geometry data. For instance, for solar potential the roof slope and roof direction or for heating potential the height of the building. This then will be integrated into the DWH.

However, there could be more use cases coming up, where other features may need to be derived from the geometries. For this scenario, I would like to have the geometries stored in a centralized place as well, preferably the already existent PostGIS data warehouse, so that people could take these geometries and derive their information directly from there. And as we have colleagues who may be more versatile in only one language (SQL or Python) it would be great to a able them to work with both languages.

So based on your replies I would go with the following strategy and would love to hear your opinion about it.

I retrieve the data automatically with my orchestration tool dagster and use the CLI tool of the importer/exporter of 3DCityDB to ingest it into the 3DCityDB extention installed in my data warehouse. In parallel I convert the CityGML into CityJSON and derive statistics out of it and integrate it as a normal table into my data warehouse by joining it with the 3DCityDB instance of the dataset. Then running final data tests and serve the final data product to end users. They can use the importer/exporter again to get the data into a JupyterNotebook or Python script or I convert it again back to CityJSON to serve it in a API for our developers to retrieve it into our software products. My question is if I make it more complicated that I should with this setup. Perhaps you have some tips for me :) Because, as @clausnagel mentioned, if I can just integrate the CityGML directly and derive my statistics with SQL code like PostGIS form the 3DCityDB directly it would be the best scenario for me, as I can keep my current DWH logic.

[balazsdukai]

In case you do store you data in 3DCityDB, I would try to avoid the back-and-forth conversion to CityJSON and compute everything in postgres. That will save you a good amount of i/o and processing. This assumes that you and your colleagues are comfortable with implementing all geometry processing in SQL and the PostGIS functions.
In a jupyter notebook could connect to postgres and write SQL too, no need to leave the database.

[SeppBerkner]

@balazsdukai Thanks again for the reply. After more consideration about our requirements I came to the conclusion that there is no need for us to store the 3 dimensional geometry in the PostGIS database. Thus, I would not need 3DCityDB for now. What we do need are two 2-dimensional tables. The first for the building which stores building statistics such as height, number of stories or year of construction and the GroundSurface as its geometry. And the second for each roof surface geometry with information about its slope, direction and area and a foreign key to its building. My issue now is that I couldn't create a Multipolygon geometry for these two tables using the boundaries (along with the vertices and transfrom information). I tried to convert the boundary coordinates into a valid GeoJSON to push this into the PostGIS database, but failed until now. So I was wondering, if there is already some tool / library / function that can create a valid GeoJSON for specific CityJSON geometry types (namely GroundSurface and RoofSurface).

[balazsdukai]

Hm, technically GeoJSON doesn't have 3D geometries, but you could try JSON-FG, which has a GeoJSON-like representation for 3D. Cityjson2jsonfg can convert it for you, give an idea how to write the converter yourself.

[SeppBerkner]

Yeah, and for my use case I can drop the third dimension and only keep x and y. So I can keep traditional GeoJSON. I already managed to retrieve the Ground Surface from the CityJSON for my building table with correct 2D-coordinates and transform it into GeoJSON. But I still struggle with edge cases such as interior rings / shells. But if there is no common tool I will try to keep digging into my own converter.