GO-DEPTH: gpkg template for the INSPIRE-extended go-depth-subsurfaceGE schema

[sMorrone]

IN GO-DEPTH we are delivering INSPIRE Geology data in geopackage format. It would be a great enhancement if it was possible to automatically create gpkg template from xsd in hale studio. In this case the go-depth-subsurfaceGE xsd schema is an INSPIRE extended one.

As a side note: We wanted to take further what produced by the NL, and available in https://github.com/INSPIRE-MIF/gp-geopackage-encodings/issues/10. (sql contained in uploaded zip file).

geology.gpkg.NL.2021.zip

[Claudia-WE]

Hi Stefania,

just for clarification. You mean format conversion not data model mapping, right ?

Or do you mean the following process:

1) Load XSD as Source Schema 2) Load Source Data 3) Export Source Data as GPKG

Could you provide the XSD and a dataset sample ?

[sMorrone]

Hi Claudia, I mean schema transformation (not data transformation /export in whatever format). I need derivation of an empty geopackage structure (i.e. template) from INSPIRE xsd, corresponding to a conceptual mapping from the INSPIRE UML model to the GeoPackage, according to INSPIRE alternative encoding rules.

Here come, in the zip file, INSPIRE-extended xsd, UML diagrams, and a sample file (true use case data) go-depth-XSD-UML-Sample file.zip

[sMorrone]

With Thorsten we could arrange a meeting to discuss about the specific UML to gpkg encoding rules (though I already anticipated to him that I would follow rules already developed for the Environmental Noise Directive Reporting, where we both are involved - however full suitability still to be checked).

[sMorrone]

With ISPRA stakeholder we updated (slightly modified) the data model /schema . Find uploaded the new version Schema & UML.zip

[thorsten-reitz]

Hi @sMorrone, thanks for the materials. I have reviewed them and started to draft the model transformation and structure for the GeoPackage. There are a couple of semantic questions that you might have explained already but that I don't fully understand right now:

1. Is there any relationship between the geometries of the Boundary, expressed as Points + thickness: Measure, and the geometries of the Isoline, expressed as GM_curve + isolineValue: Measure? E.g. are the Isolines effectively derived from the Boundary point field?
2. Semantically, I interpret the model that the BoundaryInfo points define a 2.5D surface that could be triangulized, like a DEM, that can, per point, also have a non-0 thickness (which might be caused by mixed materials at the boundary or the like). Is that interpretation correct?

In any case, the actual table structure could roughly look like this:

Table GeologicUnit has properties:

• id: Integer
• geologicUnitType: String
• (all the other non-nillable INSPIRE properties, could be simplified)

Table Boundary has properties:

• id: Integer
• geologicUnitId: Integer (Foreign Key)
• contactType: String
• referencedGeologicalUnit: String (necessary?)

Table BoundaryInfo has properties:

• id: Integer
• boundaryId (Foreign Key): Integer
• geometry: Point2d (?)
• depthASL: Float
• depthASL_uom: String
• thickness: Float
• thickness_uom: String

Table Isoline has properties:

• id: Integer
• boundaryId (Foreign Key): Integer
• geometry: Linestring2d (?)
• isolineType: String
• isolineValue: Float
• isolineValue_uom: String

Please let me know whether my assumptions in the structure outlined above need to be corrected; also, any insight into the semantics would be welcome.

[sMorrone]

Hi @thorsten-reitz Is there any relationship between the geometries of the Boundary, expressed as Points + thickness: Measure, and the geometries of the Isoline, expressed as GM_curve + isolineValue: Measure? E.g. are the Isolines effectively derived from the Boundary point field? NO, the (optional) association will usually link a boundary dataset and an Isoline dataset.

Semantically, I interpret the model that the BoundaryInfo points define a 2.5D surface that could be triangulized, like a DEM, that can, per point, also have a non-0 thickness (which might be caused by mixed materials at the boundary or the like). Is that interpretation correct? YES

Regarding the relashionship between the Boundary and the referenced GeologicUnit, the use case requirements do not foresee a mandatory association. We have only one optional association stratigraphicboundary from the GeologicUnit to the Boundary. What they need in the Boundary is the link to the description of the GeologicUnit (they have this description in the form of excel tables in the GeoMol and HotLime projects underpinning this use case, but we will make it available though the GO-PEG register). This is why we have the (mandatory) referencedGeologicUnit field defined as URL.

They don't have any pieces of information linking the Boundary to a specific geometry (geologicUnit). However, even if they didn't ask for this, we coud maybe foresee (as optional) this link to a specific geologicUnit? To me the data model would be more complete in this way. What do you think?

All the other table definitions are fine. I also attach a ppt with some semantic bits

concepts_Data_Model.pptx

[thorsten-reitz]

Hi @sMorrone, Thanks for the clarifications. I would thus keep referencedGeologicUnit in Boundary - or should I understand your explanation that it actually should go to GeologicUnit?

One more question: You probably saw that I used Point2d and Linestring2d. I did that because the 3rd dimension is implicit in isolineValue and in depthASL, according to my interpretation. Is that correct or not?

[sMorrone]

@thorsten-reitz YES, keep the referencedGeologicUnit in Boundary (mandatory field).

Regarding Point2d and Linestring2d: indeed the 3rd dimension is implicit in isolineValue and in depthASL, so 2d could suffice. However the source datasets are 3D (x,y,z), the depthASL and isolineValue fields are there for ease of visualisation in GIS environment (i.e. classification purposes) and for more straightforward queries. So please go for 3D geom tables.

[sMorrone]

@thorsten-reitz

Table GeologicUnit has properties:

    id: Integer
    geologicUnitType: String
    (all the other non-nillable INSPIRE properties, could be simplified) 

here we also need a stratigraficboundary field (foreign key to the id of the boundary - optional)

[sMorrone]

@thorsten-reitz regarding 2D vs 3D geometries. I just sent an email to the stakeholder. Let me ask again how they see it ... (usually they reply asap)

[thorsten-reitz]

here we also need a stratigraficboundary field (foreign key to the id of the boundary - optional)

This being a 1:n relationship, the tables establish that through BoundaryInfo.geologicUnitId.

If we were to model this as a forward relationship in a relational model, the property stratigraficboundary would need to be an array, for whch there is no native support in most GPKG supporting software.

[sMorrone]

OK. You are right !

[sMorrone]

@thorsten-reitz the stakeholder confirmed they need 3D geometries . Moreover there's now a request to add the optional field

• name: String

to the Boundary Table (I will then modify the UML accordingly).

[thorsten-reitz]

OK thanks. I will make a first verison of the GeoPackage template, only for these tables (not for all of Geology).

[thorsten-reitz]

Dear @sMorrone ,

please find the template as hsd and gpkg attached. I hoped to find time this week to test it with 3d data, but since I didn't have any readily available, I am afraid you have to be that guinea pig :).

All the best, Thorsten

Go-Depth.zip

[sMorrone]

@thorsten-reitz you left the EPSG:3035 in the template, we need EPSG:4326. Could you please update? Thx

[sMorrone]

@thorsten-reitz also there was a request to add a "name" property to the Boundary table (see https://github.com/gopeg/feedback/issues/11#issuecomment-963078943). Could you please add?

[sMorrone]

@thorsten-reitz Find uploaded an excel file with boundary coordinates for a specific unit (we have different input formats, one also being excel) PL_u.xlsx

[thorsten-reitz]

@sMorrone Thanks for the review. I can change the template, but at least the boundary coordinates data you sent doesn't seem to be in WGS84. Can you clarify what CRS that data set uses, please?

Meanwhile, find the corrected *.hsd.json attached: Go-Depth_Boundary.hsd.json.zip

[sMorrone]

@thorsten-reitz indeed I hadn't realised we have heterogeneous CRS for input data even for data coming from the same country! I uploaded a randomly chosen file from the very large folder, so good thing is that now I know. For the uploaded file, input CRS is EPSG:32632. DATA MODEL UPDATE: Just talked to ISPRA about the CRS question and there is a request for an update: while rethinking about most suitable output CRS and directly involving the data providers from other countries (so not just reusing the data, but asking for their feedback) they say having output files in EPSG:3034 would be better, because this is the EPSG used in different EU projects related to subsurface.

Sorry to re-ask, but could you please re-modify the gpkg template accordingly i.e. output CRS is EPSG:3034?

Thanks a lot, Thorsten!

[sMorrone]

@thorsten-reitz would it be possible to add a view to the geopackage template so that the user does not have to create it himself e.g. in QGIS? Does this make sense? any issue?

[sMorrone]

@thorsten-reitz we also need a CodelistProperties table, same as we have in the END

[thorsten-reitz]

@sMorrone Please find the updated hsd and geopackage template attached. I filled in what I could from the example data as a test. Go-Depth.zip

[sMorrone]

@thorsten-reitz when would it be possible to add the SheraDisplacement structure to the template? We would like to start the transformation of the Po Plain Faults ... I upload the extension of INSPIRE model (just few new properties) . Thank you

[thorsten-reitz]

@sMorrone I should be able to work on this tomorrow (Wednesday).

[thorsten-reitz]

@sMorrone Do you also need the Fold type, or only ShearDisplacementStructure?

[sMorrone]

@thorsten-reitz only ShearDisplacementStructure

[thorsten-reitz]

@sMorrone here is the updated modle from todays workshop: Go-Depth_Boundary.hsd.json.zip