1

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):

enter image description here

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.
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).

1
  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.

6
  • The point of view is a workflow with data exchange: data is collected with GeoPackage/QField and xtf imported back to the master database (e.g. PostGIS) with --import or --update.
    – Nicolo
    Apr 18 at 16:09
  • your point 2: Changing the relation to T_Ili_Tid manually? It would make the data invalid against the original Geodata model (RoadTrafficAccidentLocation_V2.ili).
    – Nicolo
    Apr 18 at 16:17
  • Thanks for clarifying the use case. Taking a step back and looking at the data from a validator's perspective (e.g. ilivalidator): An object of the class RoadTrafficAccident will ultimately have a reference to the catalog in the form of <Reference REF="at2"/> when referencing an AccidentType with code = "at2", because the TID of this AccidentType is "at2" as well. So if you use the stable TID from the catalog in both your databases (PG and gpkg) you should be safe when exporting and validating the .xtf data against the predefinded catalog. Or am I missing something?
    – stehen
    Apr 19 at 10:53
  • Thanks @stehen for your answer and your comments. A reference like <Reference REF="at2"/> would refer to the 'stable' acode. But the exported xtf contains ...<AccidentUID>d2ea83ad8a5a48e59a12d9cf7ce4e9a1</AccidentUID><AccidentType><RoadTrafficAccidentLocation_V2.RoadTrafficAccidentCatalogs.AccidentTypeRef><Reference REF="12"></Reference>...
    – Nicolo
    Apr 20 at 8:18
  • No, a reference like Reference REF="at2"/> has to refer to the stable TID in the provided catalog which, in this special case, has the same value as the <code> attribute and therefore the acode field in the db. You can import the TID of the catalog by using --createTidCol and --importTid. This will fill the column T_Ili_Tid in table accidenttype above, so you won't have NULL values anymore.
    – stehen
    Apr 21 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.