3dcitydb / 3dcitydb Goto Github PK

This issue can be raised when the user try to create views for the use of PostgREST with the 3DCityDB Web Map client

Hello there,

I created database with the help of the Importer/Exporter for my Citygml data (LOD1 and LOD2).
But the other task of mine is how do we able to update our database e.g. importing new buildings or eliminating some.
Based on my search,I found that it is possible with VirtualcityDATABASE. But the problem is, it is commercial and almost not possible for me to buy and company as well.
Does anyone know if there is any trial or student version?
also has anyone any Idea how do we update without VCD?
Thanks in advance

1. Set up a new 3DCityDB 4.0 instance with SRID = 25832
2. Import this dataset: http://schemas.opengis.net/citygml/examples/2.0/building/Building_LOD2-EPSG25832.gml
3. Recreate all BBOXes with core:_CityObject as top-level feature
4. Export as CityGML dataset

In the exported CityGML dataset, the BBOX of the ReliefFeature will be as follows, which is wrong:

<gml:Envelope srsName="urn:ogc:def:crs:EPSG::25832" srsDimension="3">
  <gml:lowerCorner>458880.0 5438352.6 112.0</gml:lowerCorner>
  <gml:upperCorner>458880.0 5438352.6 112.0</gml:upperCorner>
</gml:Envelope>

If you repeat the above steps, but use a feature class filter in step 2 to just import the ReliefFeature without the Building, then the BBOX will be correctly calculated as follows:

<gml:Envelope srsName="urn:ogc:def:crs:EPSG::25832" srsDimension="3">
  <gml:lowerCorner>458868.0 5438343.0 112.0</gml:lowerCorner>
  <gml:upperCorner>458892.0 5438362.0 114.0</gml:upperCorner>
</gml:Envelope>

Hello, we created the database within Postgresql with all the tables, we want to know how to add data to them. We tried with the Importer/Exporter tool, but it throws us the following error "Database error while querying index status. Org.portgresql.util.PSQLException: ERROR: no existe la relacion <citydb.index_table>. Where: funcion pL/pgSQL citydb_pkg.status_spatial_indexes (text) en la linea 5 en EXECUTE."

Hi? I have developed a CityGML ADE for Cadastre(UML and XSD). I am look for help to figure out the roadmap for importing and exporting the Cadastre-ADE examples data into 3d city db.

I have searched the web, but seemly there is no evident infomation about integrating CityGML ADEs into 3d city db?

My superficial roadmap is:

• ADE(XSD, UML, some examples data)
• develop cadastre-ade module for citygml4j just like noise-ade
• let importer-exporter based on the newly devleoped citygml4j
• ？develop postgresql db schemas?( I am not sure for this step)

Can someone point out a reasonable way to accomplish my goal storing/exporting the ade data in postgresql?

Hi !
First of all thank you for this great product! I am using 3DcityDB and still learning a lot!
I wonder how to delete, edit or uppdate an objekt. The Impoter/exporter does´nt show this possibilities..
Is there a tool for editing objects on the fly ? Im am using CityEditor and is great, but still you have to export from 3DcityDB--- import in SketchUp-- edit-export and finally import to the 3DcityDB and then this does´n replace the existing object..
Regards
Abel

Another index issue I found. Courrently, we define an index on each foreign key column. While this makes sense in general, I think it is not so useful on the objectclass_id columns. The variety of different values in these columns is usually very low, think of building installations or traffic areas. Even in thematic surface often store only three different ids for grounds, walls and roofs. I think, we can agree that such indexes are not really useful for filtering. It could be that seq scans would be faster instead, although I haven't analyzed it thoroughly.

The only place where it I would keep an index on objectclass_id is in the cityobject table. But it would also be interesting to compare performance with or without an index ;) If somebody would start to generate many different ADEs in the 3DCityDB, at some point an index might become necessary again for some tables. But, I would leave this up to the user. He could use existing monitoring tools that provide hints for missing indexes.

Is there a maven repository or something similar available to use 3dcitydb with build-systems?

I have generated some TINRelief terrain models each one with a polygon extent and an appearance. They are validated as correct and imported by 3dcitydb v4.2.0 without error. I can see that some records in postgres tables (tin_relief, surface_geometry, tex_image, appear_to_surface_data...) have been created, but I have no record in relief_feature table. When I try to export relief features I don't get anything. I also have buildings that export properly. Is that a bug or have I done something very wrong?
I send you attached an example:
TIN_Alcover_9.zip

Talked to a user who wants to create 2D maps on top of the 3DCityDB showing all attributes incl. generic ones. We could provide a convenience function that pivots rows from the cityobject_genericattrib table to columns based of a given cityobject_id. Not so sure what to do if the function is applied for more than one row as number of generic attributes can be different. We could also use a semistructured data type like JSON. It's supported both by Oracle in Postgres.

During the development of the delete script generator I found that the schema of the 3DCityDB is missing some NOT NULL checks on several columns:

• objectclass_id: This field has to be set, so NOT NULL makes sense
• %root%: Any root column in tables where tree hierarchies are stored is as least set to same value as the ID, so NOT NULL makes also sense here (+ it is also important for the delete script generator to choose the parent column for self recursions, because it can be NULL in our case)
• Some feature classes can have different parents such as boundary surface with building, building_installation or room. At least one of the foreign key column has to be set. Therefore, we could add a CHECK constraint. Yet, I don't know how this would affect the performance of imports.

Hi,

this is not a bug, but rather an idea/suggestion for future releases.

Currently, the datatype of a generic attribute is set 1 to 10 (string, double, integer, etc.).
I feel that this might be replaced by analogous (to-be-added) entries in the objectclass table and get a corresponding column objectclass_id referencing it.
This would make the design and the logic of the table more similar to the other ones in the 3DCityDB.

Just an idea...

Upgrading 3DCityDB ...

Checking version of the 3DCityDB instance ...
4.0.1

DO_UPGRADE.sql


Upgrading schema 'citydb_pkg' ...
psql:../../CITYDB_PKG/CONSTRAINT/CONSTRAINT.sql:188: ERROR:  relation "surface_geometry" does not exist
LINE 30:   AND c.confrelid = 'surface_geometry'::regclass::oid
                             ^
Press any key to continue . . .

Source: PostgreSQL/SQLScripts/CITYDB_PKG/CONSTRAINT/CONSTRAINT.sql:188

Possible fixes:

'citydb.surface_geometry'::regclass::oid
(lower($2) || '.surface_geometry')::regclass::oid

Hi!

Thanks for a great software! I wonder how can I do to make the exported objects from the 3dcityDB to clamp to ground. I am using 3dcity web map and objects från det 3dcityDB get on the ground on the flat terrain, but as soon as I switsh to STK world Terrain, then my city-objects wont follow the elevations.

I am guessing that this is customisable on Importer/exporter application?

Thanks for your time!

Regards,
Abel

Hi,
I recently ran into the following problem (maybe I didn't understand the documentation):
Imagine a data set with e. g. TINRelief and GeoreferencedTextures. The textures and the related world files are stored in a rather fractured directory structure (depth > 1).
With only basenames of the imagefiles saved, how is it possible to determine the texture projection from the world files?
Or is there an option to "write" the world files into database on import?
Is there an example on how to retrieve the information?

Kind regards,
Daniel

The new version of the cleanup_schema function also truncates the index_table that has been moved from citydb_pkg to the data schema (see 5911550). This leads to an inconsistent state. To solve the issue, either the default index entries are inserted again after truncating the table or, even simpler, index_table does not get truncated at all.

This happens under PostgreSQL. I have not checked the behaviour under Oracle.

I have imported data into postgre using importer/exporter tools.

the next step, I want to display the data that I have imported to the web map

do I have to export my data first manually using the importer/exporter tools?

is there some kind of way to display data in a web map where the data is directly fetched from 3dcityDB(like using the API) ?

The UPGRADE-script DO_UPGRADE.sql currently uses the PostgreSQL's Object Identifer (OID) regnamespace which is only supported by PostgreSQL version 9.5 or later.
(https://www.postgresql.org/docs/9.5/datatype-oid.html).
In addition, the ? function is introduced by PostgreSQL starting from its version 9.6
We have to update the 'System Requirements' of 3DCityDB:
PostgreSQL DBMS >= 9.6 with PostGIS extension >= 2.3.
PostGIS 2.3 is encouraged to be used together with PostgreSQL 9.6. see detilas here: https://postgis.net/2016/09/26/postgis-2.3.0/

Hi,

In the 3DCityDB 3.x you could choose where to delete a CityObjectGroup with or without its CityObject members.

It seems to me that in the 3DCityDB 4.x the default and only option is that a CityObjectGroup is always deleted with all its members via del_cityobjectgroup(....).

Do you confirm? I am not sure if I am missing out something.

Best regards,

GA

For testing purposes, we want to import several different citygml's into 3dcitydb.
We always have to delete the oracle user, and create a new 3dcitydb user using create_db.sql script.
Is there any script to purge/truncate an existing 3dcitydb-user?
I tried a general oracle script to disable all constraints, truncate all tables, and enable back the constraints.
After doing so, I wasn't able to connect to the user via Importer/Exporter Tool anymore...
Any hints?

best regards, Chris.

It would be great to have a little function that would go through and cleanup any Orphaned tmp_* tables created by the imp-exp.

Sometimes when a connection is interrupted during an export the tables get left behind. Not a huge issue but it would be nice to have a little function. I just checked a database and it has ~50 or so of these tables.

I assume it's fairly easy to remove these tables with some SQL.

Hello,

we are currently facing a problem in our development with 3DCityDB.
Perhaps one of you might have a few ideas how we could
approach this issue.

We are working with a larger dataset (several dozen gigabytes). Let's
call it A. There are datasets which update a few CityObjects. Let's call them B and C. The problem is, that once A is imported, importing B or C obviously changes the database.
3DCityDB works great for importing CityGML and extending the database. But
how do I update objects with the same gmlid which already exist in the
database?
If I use the importer for updating there are duplicate entries with the
same gmlid. Creating a new copy of A each time would quickly eat a lot of
storage, since A is so large.

If I read the docs and code correctly, such 'versioning' support is only
available when using the Workspace Manager in Oracle RDBMS and
unavailable with PostgreSQL.

Do you have any suggestions on possible solutions to explore?

Sincerely
Michael

Currently, we add an index on each foreign key column in a link table. Both columns also represent the primary key, which is also an index. This index is sorted by the first column in the index. Thus another index on the first column of the primary key is useless. We might only need an index for the second column. In some cases the DB would choose to use an index only scan on the PK index anyway.

I've analysed some queries under Postgres and could see that it uses an bitmap index scan on the PK index if I drop the redundant index. On my machine it was slightly slower than the index scan. Still, one index less to maintain is preferable, especially when you think of write operations such as inserts or deletes.

I found different ressources ranging from "generally faster" to "neglectable" in comparison to NUMBER. We might try it out. Would this have side effects to other tools e.g. when ID_ARRAY is transferred to Java?

What is the purpose of the thematic_surface table when the building geometry can be represented using the building table's own geometry attributes?

I read the documentation but it's not clear to me. Is there a simple example of when one is used instead of the other?

Hello,
While running some tests on 3dcitydb I noticed a difference beetween the old (2.1) and new version (3.0) when importing appearances but not texture files
In the older version, under these settings, the texture file URIs were imported in table "surface data" (column "tex_image_uri")
In version 3.0, with the same preferences, the table "tex_image" is empty (URIs are not imported)
Having found no mention of this change in the documentation, I was wondering if it was known/intended

Is there a complete list of cities that are supported in this project? I tried looking through the documentation and can't seem to find anything.

Thank you.

I am quite new to 3DCityDB.

I have many gltf building models, and I have converted these models into 3D-Tiles(b3dm) using 3d-tiles-tools, but how should I to store these b3dm into 3DCityDB? And How can I query 3d-tiles data from 3DCityDB?

Many thanks

Hi,

I am using 3DCityDB 4.3. Besides the "usual" citydb schema, I create a number of other schema using the script delivered (CREATE_SCHEMA.bat). Everything works, but I have noticed that the geometry columns of all additional schemas have no explicit srs, unlike in citydb. The contents of table database.srs are correctly copied, but nothing happens to the geometry columns.

I also tested launching manually the
SELECT citydb_pkg.change_schema_srid(
28992,
'urn:ogc:def:crs,crs:EPSG::28992,crs:EPSG::5109',
0, -- or even 1
'citydb_test');

but nothing changes.

The importer/exporter works, as does the kml/collada one, but I wonder whether this is "discrepancy" intentional or not...

All the best!

GA

Querying by codespace attributes is slow. I suggest to add an index to every codespace column (e.g. building.function, building.roof_type, ...). Please note that I mean the actual data column ('function'), not the codespace definition column ('function_codespace').

Hi there,
I am wondering what is the correct value to put in the CITYOBJECT_ID column of the surface_geometry table, as I am a bit confused after reading the doc.

Let's say I have the following data (with their corresponding cityobject_id):

• buildingA (id=1)
• roofSurface (id=2)
• wallSurface (id=3)
• groundSurface (id=4)

With object 1 carrying no (solid) geometry, objects 2 to 4 composed of several polygons each and their aggregation representing buildingA.

What is the right way to store their info in the surface_geometry table, to retreive all the polygons representing buildingA efficiently:
Option 1 - give to all of them the same ROOT_ID (even if buildingA has no solid representation) and put the corresponding CITYOBJECT_ID of each?
Option2 - Store only objects 2 to 4 in the table, and give to all of their corresponding polygons the CITYOBJECT_ID of buildingA?
Option3 - either way?

Since Oracle 12c Release 1 (12.1.0.2) the GeoRaster feature is disabled after the Oracle Spatial and Graph is initially installed (see release changes).

When running the CREATE_DB.sql script with the Spatial option, creating the table GRID_COVERAGE consequently fails because the SDO_GEORASTER data type is not available (see lines 790ff in the TABLES.sql file). The screenshot below illustrates the error message.

To enable GeoRaster support on Oracle Spatial and Graph 12.1.0.2, two steps are required:

1. Connect to the database as SYS AS SYSDBA
2. Enter the following statement:
   EXECUTE MDSYS.enableGeoRaster;

After this, the GRID_COVERAGE table can be created and the CREATE_DB.sql script runs without error message.

The CREATE_DB.sql script should therefore be changed to automatically check whether GeoRaster support is enabled on Oracle Spatial and Graph 12.1.0.2. The raster relief tables may only be created if GeoRaster support is enabled. A warning message could be displayed to the user if GeoRaster is disabled.

Hi,
I'm new to 3dcitydb.I have set up the database according to the documentation,but when I try to import gml,I met the error:

ERROR] SQL error: ORA-04063: package body "WUN.CITYDB_IDX" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "WUN.CITYDB_IDX" 的程序单元

Can anybody help me? thanks.

The latest release of the Importer/Exporter (4.2.3) cannot connect to a database created with the latest release of the bash scripts, as the version 4.1.0 is not supported.

Hi,
I am trying to convert my data from cityGML 2.0 to cityGML 1.0 and I would like to use the software exclusively for this conversion. Is there any way to skip connecting to a database? I am having difficult time installing postgreSQL with postGIS on my linux machine.
Thanks in advance.

Hello, topic

Starting from Oracle 12c, the GeoRaster feature is disabled per-default and can be enabled for the entire database by executing the procedure MDSYS.enableGeoRaster. This behaviour has been changed since Oracle 19c, where the GeoRaster feature must be enabled for each schema that will be using GeoRaster.
https://docs.oracle.com/en/database/oracle/oracle-database/19/geors/georaster-database-creation-management.html#GUID-20119C51-6B07-4535-954E-7C55850F51F3
We should document this things in the 3DCityDB manual.

Hi everyone, really need help. I used FME to convert IFC to citygml. I imported a 3D citygml into 3Dcitymodel, and when I try to export it in KML or also in citygml again, it seems to be 2D! Why?

So basically, i'm here tryinh the tutorial that given by the 3dcitydb. i'm at the step to create DB. but, i'm stuck at this step because some error that i cannot understand. After i input the reference, i've got this message

psql:SCHEMA/SCHEMA.sql:1444: ERROR: type "raster" does not exist LINE 3: rasterproperty raster,

Hello 3dcitydb team,

first things first, thanks for the great work! I have enjoyed the tutorials and have been using it for sometime and haven't faced an issue so far.

This is, as well, not a bug but rather a question. I've been trying to figure out why in cityGML data that I recieve, the coordinates of the first point in a polygon, repeat itself at last again?

I'm thinking it might have to do with the validation process, as a security measure or maybe because of certain functions like "plotting", etc. which need the first point, at the end to close the polygon. I'm not sure though and wanted to ask for the reason out of curiosity. Can eliminating this not reduce the file size and make it more lean?

How to reproduce the issue:

1. Create a new 3dcitydb instance under PostgreSQL/PostGIS
2. Run the DROP_SCHEMA script to drop the initial schema citydb
3. Run the CREATE_SCHEMA script to create a new schema citydb2

Error displayed on the console:

psql:CREATE_SCHEMA.sql:81: FEHLER:  Relation »citydb.database_srs« existiert nicht
ZEILE 1: ...tydb2.DATABASE_SRS SELECT srid, gml_srs_name FROM citydb.dat...

Idea: Users are prohibited from dropping the initial citydb schema.

As we now store the table name of an objectclass in the objectclass table, it's time to change the body of the objectclass_id_to_table_name function. A simple SELECT statement is sufficient.

We should also provide a new function which does the opposite: table_name_to_objectclass_ids. As multiple IDs are possible, return type would be int[] for Postgres and ID_ARRAY for Oracle. A recursive query is necessary to get all objectclass IDs of nested types. The code does already exist in the delete script generator. Would like to call the new function from there.

I'm doing large fills (currently 250k buildings) of 3DCityDB from wavefront OBJ files in a custom reader into Postgres. As part of this, I drop all indexes ahead of time (except for the primary key ones).

It'd be great to have the CREATE_DB.sh script separate db and index building into different scripts so it would be easy to rebuild them on demand.

As the next major release of the 3DCityDB will allow for dynamically adding CityGML ADEs around the core schema, we need a way to create DELETE scripts in a generic fashion. A first version has been developed in january 2018 and is currently tested. It relies greatly on database constraints, such as NOT NULL and foreign keys. The ON DELETE configuration of a foreign key determines the code of a delete script, which is:

delete_A(id):
[A]---<[B:NO ACTION] = delete referencing entries in table B first
[A]---<[B:SET NULL] = do nothing
[A]---<[B:CASCADE] = do nothing
[A:NO ACTION & FK!=PK]>---[B] = do nothing
[A:NO ACTION & FK=PK]>---[B] = call delete_B(id)
[A:SET NULL]>---[B] = cleanup unreferenced entries in table B
[A:CASCADE]>---[B] = do nothing

In order to work also for ADEs extensions the constraints need to be set correctly (should be another ticket). So far, the delete script generator has only been tested against the core schema. There are still some issues.

I wrote an node.js wrapper for the command line interface to import citygml xml files into a postgres database. Everything seems to work, but the table doesn't contain any entries in the end.
When using the 3dcitydb-importer-exporter there is a popup during the import process which lists the building as they are imported. This step seems to missing in the command line version.
Do you have any ideas how to fix this?

$ cityviz import test/fixtures/cityGml/4-52-NOORD_KETHEL.xml
/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bin/java -Xms128m -Xmx768m -jar /Users/adrian/Projects/CityViz/CityViz/node_modules/3dcitydb-importer-exporter/lib/3dcitydb-impexp.jar -shell -config /var/folders/bj/0twmh2ws4tn_t2sxn5vgqvmm0000gn/T/115223-5801-82sq2y -import /Users/adrian/Projects/CityViz/CityViz/test/fixtures/cityGml/4-52-NOORD_KETHEL.xml
[21:53:08 INFO] Starting 3D City Database Importer/Exporter, version "1.6-postgis-b1"
[21:53:08 INFO] Loading plugins
[21:53:08 INFO] Initializing application environment
[21:53:13 INFO] Loading project settings
[21:53:13 INFO] Connecting to database profile 'cityviz'.
[21:53:14 INFO] Database connection established.
[21:53:14 INFO] PostgreSQL, 9.4.0
[21:53:14 INFO] SRID: 28992 (Projected)
[21:53:14 INFO] SRS: Amersfoort / RD New
[21:53:14 INFO] gml:srsName: 28992
[21:53:14 INFO] Initializing database import...
[21:53:14 INFO] Spatial indexes are enabled.
[21:53:14 INFO] Normal indexes are enabled.
[21:53:14 INFO] Creating list of CityGML files to be imported...
[21:53:14 INFO] List of import files successfully created.
[21:53:14 INFO] 1 file(s) will be imported.
[21:53:15 INFO] Importing file: /Users/adrian/Projects/CityViz/CityViz/test/fixtures/cityGml/4-52-NOORD_KETHEL.xml
[21:53:15 INFO] Resolving XLink references.
[21:53:15 INFO] Cleaning temporary cache.
[21:53:15 INFO] Database import successfully finished.

Right now, entries in the USER_SDO_GEOM_METADATA view are manually edited in the SPATIAL_INDEX file during setup. In fact, some columns are missing, because we thought only indexed columns require an entry in the system view.

To make this process a bit easier, it would be good to have a stored procedure that creates metadata for all spatial columns in one user schema. This functions could call another procedure which is designed for a single column with arguments table_name, column_name, dim, srid.

The schema must take care of a few exceptions, because parameters such as dimension and SRID can not be queried this far (maybe by adding constraints), e.g.

• 2D for EXTENT (relief_component), GT_REFERENCE_POINT (surface_data), TEXTURE_COORDINATES (textureparam)
• SRID = NULL for IMPLICIT_GEOMETRY (surface_geometry), RELATIVE_OTHER_GEOM (implicit_geometry), TEXTURE_COORDINATES (textureparam)

Because of our chosen setting for MDSYS.SDO_DIM_ELEMENTs, the column procedure needs to set session parameters NLS_TERRITORY and NLS_LANGUAGE to enable entries with bounding volume with 10000km extent for X, Y and 11km for Z.

It would be great if you could add version tags (at least for the last version)!

I were tried import and export features and successfully load from web map client.
I use sample cityJSON or cityGML file from internet,

Now, I want to try my own file, but my file is IFC format,

how to convert my IFC file to cityJSON or cityGML?
any suggestion software or library to produce cityGML or cityJSON file?

Is there a particular reason why the id column in the generic_cityobject table is not nextval incremented?