INTERLIS - CatalogueReference, why reference to unstable T_Id?

Question

Example

Lot's of INTERLIS geodata models use catalogues to provide allowed values for properties (database fields).

Let's take the ASTRA model RoadTrafficAccidentLocation as example. The AccidentType property of class RoadTrafficAccident is based on the structure AccidentTypeRef:

MODEL RoadTrafficAccidentLocation_V2 (en)
  AT "https://models.geo.admin.ch/ASTRA/" VERSION "2018-12-01" =

  IMPORTS GeometryCHLV95_V1;
  IMPORTS CHAdminCodes_V1;
  IMPORTS CatalogueObjects_V1;
  IMPORTS LocalisationCH_V1;

  TOPIC RoadTrafficAccidentCatalogs
    EXTENDS CatalogueObjects_V1.Catalogues =

    CLASS AccidentType
    EXTENDS CatalogueObjects_V1.Catalogues.Item =
      Code  : MANDATORY TEXT*8;
      Text : MANDATORY LocalisationCH_V1.MultilingualText;
      UNIQUE Code;
    END AccidentType;

    STRUCTURE AccidentTypeRef
    EXTENDS CatalogueObjects_V1.Catalogues.CatalogueReference =
      Reference (EXTENDED) : MANDATORY REFERENCE TO (EXTERNAL) AccidentType;
    END AccidentTypeRef;

...

  END RoadTrafficAccidentCatalogs;

  TOPIC RoadTrafficAccident =
    OID AS INTERLIS.UUIDOID;
    DEPENDS ON RoadTrafficAccidentLocation_V2.RoadTrafficAccidentCatalogs;

    CLASS RoadTrafficAccident =
      AccidentUID : MANDATORY TEXT*32;
      AccidentType : MANDATORY RoadTrafficAccidentLocation_V2.RoadTrafficAccidentCatalogs.AccidentTypeRef; 
...
    END RoadTrafficAccident;

  END RoadTrafficAccident;

END RoadTrafficAccidentLocation_V2.

Problem

Generate a survey GeoPackage database and import the provided Catalogue values:

• Database: ili2gpkg --schemaimport --dbfile ./GIS/Data/RoadTrafficAccidentLocation_survey1.gpkg --createEnumTabs --createFk RoadTrafficAccidentLocation_V2.ili.
• Import xml Catalogue data: ili2gpkg --import --dbfile ./GIS/Data/RoadTrafficAccidentLocation_survey1.gpkg RoadTrafficAccidentLocation_Catalogs_V2.xml.

The accidenttype property is related to T_Id of accidenttype table (foreign key):

T_Id on parent side (Table accidenttype) is autogenerated, you have no control over it. It is not stable. This leads to problems like:

• If only the order of the Catalogue items within the xml would change, the T_Id's would be different.
• If you want to add or remove a Catalogue item, the T_Id's do change.

That is, changes on Catalogue items destroy the foreign key relationship and make existing data invalid.

Questions

1. Is there a mistake in the problem analysis above?
2. Wouldn't it be better to assure the integrity of AccidentType by EXISTENCE CONSTRAINT accidenttype REQUIRED IN RoadTrafficAccidentLocation_V2.RoadTrafficAccidentCatalogs.AccidentType:Code;, because Code is stable?

More Details

To illustrate the Problem I have added some data into RoadTrafficAccidentLocation_survey1.gpkg with QGIS (in a real workflow it could be QField).

Table roadtrafficaccident:

T_Id  T_basket  T_Ili_Tid                             accidentuid                       accidenttype  accidentseveritycategory
----- --------- ------------------------------------- --------------------------------- ------------- ------------------------
45    4         19e61097-ecfe-4fa5-bb23-d599db4834c8  d2ea83ad8a5a48e59a12d9cf7ce4e9a1  12            25                      
49    4         36e9d7fc-7048-484f-8181-77bb08294fca  79db6f3161c844c28b20e0c6a693b252  11            24                      

Table accidenttype:

T_Id  T_basket  T_Ili_Tid  acode  atext  atext_de                               
----- --------- ---------- ------ ------ ---------------------------------------
10    3         NULL       at0    NULL   Schleuder- oder Selbstunfall           
11    3         NULL       at1    NULL   Überholunfall oder Fahrstreifenwechsel 
12    3         NULL       at2    NULL   Auffahrunfall                          
13    3         NULL       at3    NULL   Abbiegeunfall                          
14    3         NULL       at4    NULL   Einbiegeunfall                         
15    3         NULL       at5    NULL   Überqueren der Fahrbahn                
16    3         NULL       at6    NULL   Frontalkollision                       
17    3         NULL       at7    NULL   Parkierunfall                          
18    3         NULL       at8    NULL   Fussgängerunfall                       
19    3         NULL       at9    NULL   Tierunfall                             
20    3         NULL       at00   NULL   Andere                                 

As you can see, Field accidenttype in Table roadtrafficaccident refers to T_Id (and not to acode) in Table accidenttype.

The validation of the survey data is correct:

• Validate Database content: ili2gpkg --validate --models RoadTrafficAccidentLocation_V2 --modeldir ./;http://models.interlis.ch --dbfile ./GIS/Data/RoadTrafficAccidentLocation_survey1.gpk.

---

• edited 2021-04-20: added chapter More Details.
• edited 2021-04-18: replace 'master' by 'survey' database to better reflect the workflow: master (e. g. PostGIS) → survey (GeoPackage), collect data locally → export xtf → import/update into master.

Answer 1

ili2db maps the references from the XML transferfile to PK/FK columns (as stated in the question). The motivation to do it this way (and not use the TID/REF from the XML), is to separate the implmentation from the external view use of it, and to get a homogenous implementation of PK/FKs, independent of the interlis models (they might have different OID definitions per CLASS).

Answer 2

1. I can't see any mistake in your problem analysis. It looks like you are focusing on recurring catalog imports and regular catalog changes. I don't expect catalogs to change very often but nevertheless updating a running application with a new catalog has to be addressed.

2. Not sure whether ExistenceConstraint clause is implemented in ili2gpkg at all. It also depends on whether you are the owner of the model and the catalog data and have the ability to change it. Obviously TIDs of an AccidentType object in the catalog data (.xml) are identical to the code attribute. You could use --createTidCol and --importTid and change the relation in gpkg to the new T_Ili_Tid.