Error while populating layer in layer loader.

[behjani]

Thank you for your efforts and developing this plugin to process CityGML data.

I have tried applying and the following error message appears several times when updating the layer:

CRITICAL Refreshing layers ERROR at citydb-tools\cdb4\gui_loader\functions\threads.py>refresh_all_gviews_thread
ERROR: FEHLER: Typ »geometry« existiert nicht
LINE 5: snapped_poly geometry(PolygonZ);
^
QUERY:
DECLARE
dec_prec numeric;
srid_id integer;
snapped_poly geometry(PolygonZ);
num_geoms integer;
is_empty_geom boolean;
ring geometry(LinestringZ);
o_ring geometry(LinestringZ);
i_ring geometry(LinestringZ);
i_rings geometry(LinestringZ)[];
n_int_rings integer;
i integer; r RECORD;
area_poly numeric;
new_polygon geometry(PolygonZ);

         BEGIN
         
         CASE 
          WHEN perform_snapping = 0 THEN
          RETURN polygon;
          WHEN perform_snapping = 1 THEN
          dec_prec := 10^(-digits);
          srid_id := ST_SRID(polygon);
          snapped_poly := ST_SnapToGrid(polygon, ST_GeomFromText('Point(0 0 0)'), dec_prec, dec_prec, dec_prec, 0);
          is_empty_geom := ST_IsEmpty(snapped_poly);
         
          IF is_empty_geom IS TRUE THEN
          RETURN NULL;
          ELSE -- there is a geometry from the resulting snap to grid process
          num_geoms := ST_NumGeometries(snapped_poly);
          IF num_geoms > 1 THEN
          RAISE NOTICE 'Number of geometries resulting from the snapped polygon is %', num_geoms;
          END IF;
         
          ring := ST_ExteriorRing(snapped_poly);
          o_ring := ring;
          FOR r IN SELECT
          foo.path[1]-1 AS zero_based_index,
          round(ST_x(foo.geom)::numeric,digits)::double precision AS px,
          round(ST_y(foo.geom)::numeric,digits)::double precision AS py,
          round(ST_z(foo.geom)::numeric,digits)::double precision AS pz
          FROM ST_DumpPoints(ring) AS foo
          LOOP
          o_ring := ST_SetPoint(o_ring, r.zero_based_index, ST_MakePoint(r.px, r.py, r.pz));
          END LOOP;
          o_ring := ST_SetSRID(o_ring, srid_id);
         
          n_int_rings := ST_NumInteriorRings(snapped_poly);
          IF n_int_rings > 0 THEN
          FOR i IN 1..n_int_rings LOOP
          ring := ST_InteriorRingN(snapped_poly, i);
          i_ring := ring;
          FOR r IN SELECT
          foo.path[1]-1 AS zero_based_index,
          round(ST_x(foo.geom)::numeric,digits)::double precision AS px,
          round(ST_y(foo.geom)::numeric,digits)::double precision AS py,
          round(ST_z(foo.geom)::numeric,digits)::double precision AS pz
          FROM ST_DumpPoints(ring) AS foo
          LOOP
          i_ring := ST_SetPoint(i_ring, r.zero_based_index, ST_MakePoint(r.px, r.py, r.pz));
          END LOOP; 
          i_rings := array_append(i_rings, i_ring);
          END LOOP;
          END IF;
          END IF;
         ELSE
          RAISE EXCEPTION 'Value of "perform_snapping" input parameter is invalid. It must be either 0 or 1'; 
         END CASE;
         
         IF n_int_rings = 0 THEN
          new_polygon := ST_MakePolygon(o_ring);
         ELSE
          new_polygon := ST_MakePolygon(o_ring, i_rings);
         END IF;
         
         area_poly := qgis_pkg.ST_3DArea_poly(new_polygon);
         
         IF (area_poly IS NULL) OR (area_poly               RETURN NULL;
         ELSE
          RETURN new_polygon;
         END IF;
         
         EXCEPTION
          WHEN QUERY_CANCELED THEN
          RAISE EXCEPTION 'qgis_pkg.st_snap_poly_to_grid(): Error QUERY_CANCELED';
          WHEN OTHERS THEN
          RAISE EXCEPTION 'qgis_pkg.st_snap_poly_to_grid(): %', SQLERRM;
         END;
         
         CONTEXT: Kompilierung der PL/pgSQL-Funktion »st_snap_poly_to_grid« nahe Zeile 5

As a result, I am unable to complete the task of populating the layer and am stuck at this point.
I am using PostgreSQL 17, PostGIS 3.5.0, QGIS 3.34.13, 3DCityDB Tools 0.8.9 and 3D City Database Importer/Exporter 5.4.0. The data contains the building model of the city of Hamburg in LOD 2 (Link: https://suche.transparenz.hamburg.de/dataset/3d-gebaeudemodell-lod2-de-hamburg2)

Thank you for any help. Keep up the good work.

[pancost]

Hello @behjani,
"geometry" is a PostGIS construct meaning that something goes wrong there.

Can you triple check that the "postgis" extension is enabled, not only for the default schema but also for the additional schemas that the plugin installs in the db?

Sometimes depending on how PostGIS is installed or loaded, "postgis" may not be accessed in schemas outside of the search path.

In psql you can use to see what versions you have installed and also what schema they are installed.

\connect mygisdb
\x
\dx postgis*

from: https://postgis.net/docs/postgis_installation.html

Let us know :)

[behjani]

Hello @pancost :)

Thank you very much for the quick response. All schemas are included in search_path and all users have full access rights.
In psql the command \dx postgis* returns the following:

List of installed extensions
-[ RECORD 1 ]+-----------------------------------------------------------
Name | postgis
Version | 3.5.0
Schema | public
Description | PostGIS geometry and geography spatial types and functions
-[ RECORD 2 ]+-----------------------------------------------------------
Name | postgis_raster
Version | 3.5.0
Schema | public
Description | PostGIS raster types and functions
-[ RECORD 3 ]+-----------------------------------------------------------
Name | postgis_sfcgal
Version | 3.5.0
Schema | public
Description | PostGIS SFCGAL functions
-[ RECORD 4 ]+-----------------------------------------------------------
Name | postgis_topology
Version | 3.5.0
Schema | topology
Description | PostGIS topology spatial types and functions

However, the error still exists.
As I understand it, the extension applies to the entire database. Using ALTER EXTENSION postgis SET SCHEMA citydb to move the extension to another schema did not help.
Used SELECT typname FROM pg_type WHERE typname = 'geometry'; to check that geometry is available in the database and SELECT f_table_schema, f_table_name, f_geometry_column, srid, type FROM geometry_columns; shows that the columns with geometry types exist.

[pancost]

Thanks for checking, you're correct.
still it's weird

Did you already try to reinstall the QGIS Package to your DB?
If not, please do (slides 30-36:install and 106-109:uninstall of the 3DCityDB-Tools_UserGuide.pdf)
Maybe that will set things straight.

Sorry for the "IT101" type of support, I am not able to reproduce the issue.

@gioagu maybe you stumbled upon this issue before?

[gioagu]

Dear @behjani,

sorry for the belated answer. I am now first of all trying to understand your issue. I may need some further details (read later on).

FYI: I am using Windows 11, 3DCityDB 4.4.1, Importer/Exporter 5.5.0, PostgreSQL 16, PostGIS 3.5, and the current dev version of the plugin (soon to be 0.8.10). Please be aware that no changes were done to any SQL function for the building module so far, so you can still use 0.8.9 for testing!

1. I have downloaded the 3D city model of Hamburg for 2024. For the moment, I have loaded into the 3DcityDB only tile LoD2_32_563_5935_1_HH.xml (I will upload the whole city model at a later point).

2. I have generated the building layers correctly, and I can load them into QGIS. See screenshot. Please note: I am using the default settings, so I am NOT trying to simplify geometry (the option is in the "Settings" tab of the Loader GUI).
   However, it seems that you are trying to simplify geometries. Is it the case, or not? This is not clear to me. Please let me know!

3. I have also tried simplifying the geometries with the default values (3 decimal places, 0.0001 m2 minimum area). I still can generate and load the layers. See screenshot.

Am I doing anything different from what you did (besides having just one tile instead of the whole city)?

[gioagu]

Addendum 1:

1. I loaded the whole city model into the 3DCityDB. Again, no problems generating the layers (no geometry simplification).

2. I generated the layers of the whole city also performing a geometry simplification (using the standard values: precision 3, min area 0.0001 m2). The process took (as expected) very long time (circa 10 hours in total), but it was successful.

Now, I could not reproduce your issue. At the moment, it sounds like a configuration or installation issue and not a bug in the plugin. If not, let me know.

Merry Christmas!

[behjani]

Hello @gioagu

thanks for your help. I will redo it from scratch and let u know if its based on a configuration error after Christmas. Exporting the data as .json using the importer/exporter and importing it into QGIS with the JSONLoader Plugin worked just fine, so the data itself seems alright.

Merry Christmas