the translation from french to english is the same for vanne and soupape which makes the legend difficult to understand

03_tdh_db_sia405.sql and 09_tdh_dictionaries.sql have the same content.
Which one should be used in the setup.sh file ?

You can check out the latest datamodel dump from this page:
https://github.com/teksi/district_heating/actions/workflows/datamodel-create-dumps.yml

Select the latest version from the Artifacts:

Download datamodel-dumps.zip

Use the tdh_dev_structure_and_demo_data.backup out of this zip file to restore with pgAdmin

note:
If your restore fails with: pg_restore: error: could not execute query: FEHLER: Rolle »tdh_viewer« existiert nicht
create the roles first:
https://github.com/teksi/district_heating/blob/main/datamodel/12_0_roles.sql (for database cluster) -> download as raw
https://github.com/teksi/district_heating/blob/main/datamodel/12_1_roles.sql (for database) -> download as raw

• HINWEIS: Bezeichner »pkey_tdh_vl_sia405pt_protective_tube_horizontal_positioning_code« wird auf »pkey_tdh_vl_sia405pt_protective_tube_horizontal_positioning_cod« gekürzt COMMIT Query returned successfully in 210 msec.

https://stackoverflow.com/questions/8213127/maximum-characters-in-labels-table-names-columns-etc max 63 Zeichen

          @sjib structure_line and trench_line also missing in the tdh_dictionary

Originally posted by @jpdupuy in #23 (comment)

The current defintion prevents FME to import geopackage data id the geometry constraint is based on a unique geometry type
ALTER TABLE tdh_od.pipe_section ADD COLUMN geometry3d_geometry geometry('COMPOUNDCURVEZ', :SRID);
ALTER TABLE tdh_od.structure ADD COLUMN geometry3d_geometry geometry('CURVEPOLYGONZ', :SRID);

Whenever the geopackage is made of strait lines (Linestring) or strait lines + arcs (compoundcurve) fme stops with a fatal error :
ERROR |Bulk copy failed on table 'tdh_od.pipe_section' using delimiter ':'. Error was 'ERROR: Geometry type (LineString) does not match column type (CompoundCurve)
CONTEXT: COPY pipe_section, line 1, column geometry3d_geometry: "01020000A008080000030000009AB114071E4843411E47ECD42A8F31...'
ERROR |POSTGIS writer: An error has occurred. Check the logfile above for details

I could not find a solution to solve this issue (The ArcStrocker would transform arcs into line segments, but you would have to change the constraint to a LINESTRINGZ in the datamodel).

I suggest to add the following constraints
ALTER TABLE tdh_od.pipe_section ADD COLUMN geometry3d_geometry geometry('GEOMETRYZ', :SRID);
ALTER TABLE tdh_od.pipe_section ADD CONSTRAINT enforce_pipe_section_geotype_geometry3d
CHECK (geometrytype(geometry3d_geometry) = 'LINESTRING'::TEXT OR geometrytype(geometry3d_geometry) = 'COMPOUNDCURVE'::TEXT
OR geometrytype(geometry3d_geometry) = 'LINESTRINGZ'::TEXT OR geometrytype(geometry3d_geometry) = 'COMPOUNDCURVEZ'::TEXT);

ALTER TABLE tdh_od.sia405cc_cable ADD COLUMN geometry3d_geometry geometry('GEOMETRYZ', :SRID);
ALTER TABLE tdh_od.sia405cc_cable ADD CONSTRAINT enforce_cable_geotype_geometry3d
CHECK (geometrytype(geometry3d_geometry) = 'LINESTRING'::TEXT OR geometrytype(geometry3d_geometry) = 'COMPOUNDCURVE'::TEXT
OR geometrytype(geometry3d_geometry) = 'LINESTRINGZ'::TEXT OR geometrytype(geometry3d_geometry) = 'COMPOUNDCURVEZ'::TEXT);

ALTER TABLE tdh_od.structure ADD COLUMN geometry3d_geometry geometry('GEOMETRYZ', :SRID);
ALTER TABLE tdh_od.structure ADD CONSTRAINT enforce_structure_geotype_geometry3d
CHECK (geometrytype(geometry3d_geometry) = 'POLYGON'::TEXT OR geometrytype(geometry3d_geometry) = 'CURVEPOLYGON'::TEXT
OR geometrytype(geometry3d_geometry) = 'POLYGONZ'::TEXT OR geometrytype(geometry3d_geometry) = 'CURVEPOLYGONZ'::TEXT);
Please find a zip file to reproduce the error
fme_scripts.zip

I have reviewed the classes of the official model and I can confirm :

• Ouvrage (surface)
• Tracé (line)
• Point du tracé (point)
• Conduite (line)
• Point de conduite (point)(normal et alimentation)
• Cable (line)
• Point de cable (point)

• HINWEIS: Bezeichner »pkey_tdh_vl_sia405pt_protective_tube_horizontal_positioning_code« wird auf »pkey_tdh_vl_sia405pt_protective_tube_horizontal_positioning_cod« gekürzt COMMIT Query returned successfully in 210 msec.

https://stackoverflow.com/questions/8213127/maximum-characters-in-labels-table-names-columns-etc max 63 Zeichen

INSERT INTO tdh_vl.pipe_section_status (code, vsacode, value_en, value_de, value_fr, value_it, value_ro, abbr_en, abbr_de, abbr_fr, abbr_it, abbr_ro, active) VALUES (8012,8012,'operational.operational','in_Betrieb.in_Betrieb','en_service.en_service', 'in_funzione.in_funzione', 'functionala.functionala', '', '', '', '', '', 'true');

INSERT INTO tdh_vl.structure_status (code, vsacode, value_en, value_de, value_fr, value_it, value_ro, abbr_en, abbr_de, abbr_fr, abbr_it, abbr_ro, active) VALUES (8209,8209,'inoperative.inoperative','ausser_Betrieb.ausser_Betrieb','hors_service.hors_service', 'fuori_servizio.fuori_servizio', 'rrr_ausser_Betrieb.ausser_Betrieb', '', '', '', '', '', 'true');

no subvalues for these values in INTERLIS

The SIA405 Fernwaerme Model is modelling geometries Surface and Ligne of OUVRAGE and TRACE with an extra class:

CLASS Ouvrage_Surface =
ATTRIBUTE
!!   Geometrie: MANDATORY SURFACE WITH (STRAIGHTS, ARCS) VERTEX Base_f_LV95.CoordNat
!!              WITHOUT OVERLAPS > 0.050;
   Geometrie: MANDATORY Base_f.Surface;
END Ouvrage_Surface;

ASSOCIATION Ouvrage_SurfaceAssoc =
   OuvrageRef  -<#> {1} Ouvrage;
   Surface --   {0 .. *} Ouvrage_Surface;
END Ouvrage_SurfaceAssoc;

CLASS Ouvrage_Ligne =
ATTRIBUTE
    Geometrie: MANDATORY Base_f.Polyligne;
END Ouvrage_Ligne;

ASSOCIATION Ouvrage_LigneAssoc =
   OuvrageRef  -<#> {1} Ouvrage;
   Ligne --   {0 .. *} Ouvrage_Ligne;
END Ouvrage_LigneAssoc;

Tthis would allow to have several surfaces and lines for the classes OUVRAGE / TRACE. This would allow to have an e.g. inner and outer surface and if needed lines for drawing details.

Should this be supported in this first version of the module?

We could also just start with one each and see if this is enough during the discussion of your prototype.

Do you want support for 3D geometry?

Currently geomtries are modelled like this:
2D

ALTER TABLE tdh_od.structure ADD COLUMN geometry_geometry geometry('CURVEPOLYGON', :SRID);
CREATE INDEX in_tdh_structure_geometry_geometry ON tdh_od.structure USING gist (geometry_geometry );
COMMENT ON COLUMN tdh_od.structure.geometry_geometry IS ' / offener oder geschlossener Linienzug, Stützpunkte in Landeskoordinaten / polyligne ouverte ou fermée avec des points d’appui en coordonnées nationales';

3D

ALTER TABLE tdh_od.structure ADD COLUMN geometry3d_geometry geometry('CURVEPOLYGONZ', :SRID);
CREATE INDEX in_tdh_structure_geometry3d_geometry ON tdh_od.structure USING gist (geometry3d_geometry );
COMMENT ON COLUMN tdh_od.structure.geometry3d_geometry IS 'yyyy_geschlossener Linienzug, Stützpunkte in Landeskoordinaten / geschlossener Linienzug, Stützpunkte in Landeskoordinaten / polyligne fermée avec points d’appui en coordonnées nationales';

In TEKSI Wastewater we have changed to 3D

value list pipe_point_normal_kind : typing mistake in french
. armatures.equipements.tuyeau_metallique should be replaced by armatures.equipements.tuyau_metallique

The attribut comment (mouse over) in QGIS does not seem to be correctly taken into account for special character (é, à, ä, ...)

In SIA405 Fernwärme Attributes as owner are defined as text attributes:

Eigentuemer: SIA405_Base_LV95.OrganisationBezeichnung;

It is suggested to create relations to the class organisation instead in TEKSI Distance heating.

Since Troncon_statique and troncon_hydraulique (nor noeud_statique and noeud_hydraulique) are not part of the first version, i believe that the value lists should set to inactive the related values armatures.point_statique. (ie: from 8136 to 8146)

It would be nice to improve the data model in order to import the points (XYZ and codes (ie classe + few attributes) and capture date) in the tdh_od database.

The QGIS operator could then easily snap to the imported points to create object in the classes (structure, pipe_section, pipe_point, cable, cable_point, trench, trench_point)

Name_Nummer (name_number) – in INTERLIS not MANDATORY – how to handle in TEKSI Distance heating?
I would suggest to set it MANDATORY

@jpdupuy
Here the code as discussed for your migration:

--- Add additional DEREFERABLE constraints to data model

ALTER TABLE tdh_od.pipe_point_feed ALTER CONSTRAINT oorel_od_pipe_point_feed_pipe_point DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE tdh_od.pipe_point_norma ALTER CONSTRAINT oorel_od_pipe_point_norma_pipe_point DEFERRABLE INITIALLY DEFERRED;

And maybe add in FME this call before the whole import or do it manually before running the import:
SET CONSTRAINTS ALL DEFERRED;

and then set back at the end

Hello,

There is a mismatch between the date type defined in the SIA-405 documentation and the implementation
start_year smallint,
reconstruction smallint,

Hoehe_Kanalsohle bei Trassepunkt - function necessary as with
Abwasserknoten.Lage und Sohlenkote - see CREATE OR REPLACE FUNCTION tww_app.synchronize_level_with_altitude_on_wastewater_node()
oder Deckel.Kote und Deckel.Lage (as they are now 3D geometries)

Whenever the data model includes 2 geometry columns (ie for 2D and for 3D) for a specific table :
ALTER TABLE tdh_od.structure ADD COLUMN geometry_geometry geometry('CURVEPOLYGON', :SRID);
ALTER TABLE tdh_od.structure ADD COLUMN geometry3d_geometry geometry('CURVEPOLYGONZ', :SRID);
the QGIS Data manager will propose 2 tables with the same name to import in the canvas

Therefor, only one geometry column should be specified in the datamodel to avoid confusion (to be discussed)

Dito for structure_line

• Add last_modifcation attributes in data model

psql:09_tdh_dictionaries.sql:52: NOTICE: type "tdh_od.plantype" does not exist

          If this is successful I can add DEFERRABLE INITIALLY DEFERRED to all relations as with tww

Originally posted by @sjib in #27 (comment)

All attribut names referencing value lists should be prefixed by "fk_" such as horizontal_positioning ->fk_horizontal_positioning

@sjib Do you know if the SVG symbol library has been published (refer to the SIA-405 cahier technique 2015)
If yes, can you please send it to me. It will help me to prepare the QGIS project file
I shall verify if this recommendation is made in the Development Guidelines.

Impossible de valider les changements pour la couche point de conduite

Erreurs : ERROR: 1 feature(s) not added.

Erreur du fournisseur de données :
Erreur PostGIS lors de l'ajout d'entité : ERROR: relation "tdh_sys.dictionary_od_table" does not exist
LINE 1: ...ELECT shortcut_en FROM tdh_sys.di...
^
QUERY: SELECT shortcut_en FROM tdh_sys.dictionary_od_table WHERE tablename = table_name
CONTEXT: PL/pgSQL function tdh_sys.generate_oid(text,text) line 22 at SQL statement

Please note that we work in a windows environment and I had to copy the setup.sh (for linux environment) to setup.cmd.
I do not know if this environment must be taken into account for TEKSI users according to the product strategy.
If not, this would be an advantage.

Originally posted by @sjib in #1 (comment)

Add or adapt the following tests datamodel\tests

• test_on_delete.py (add)
• test_geometry.py (adapt)
• test_removed_fields.py (add)
• test_schemas.py (check)
• test_symbology.py (add)
• test_triggers.py (add)
• test_typos.py (add if needed)
• test_views.py (add)

A minor attribute name typing mistake (lenght2 instead of length1)

0001_views.zip
I fixed the temporary views according to the changes in the datamodel
But I shall need the final editable views for the QGIS project file