PSHA input model postgis database schema

[al-maisan]

We need a database schema for the PSHA input model

Problem description

We require a database schema for storing PSHA input model entities like: fault, rupture and seismic source.

The equivalent NRML (XML) schema exists already and the database schema must be interoperable with the former i.e. we must be able to convert as follows:

NRML file -> PSHA input database -> NRML file

Solution outline

Define a postgres/postgis database schema that is capable of storing faults, ruptures and seismic sources. Very early work on the database schema has already started and it looks like this.

Problems and questions identified

What needs to be done?

1. Is it sufficient for the database schema to support the simple/complex faults, seismic sources and ruptures and the data associated with these?
2. What earthquake catalog data needs to be stored in the database? What are the entities and the relationships between these?

   High complexity

The NRML schema is very complex and it is not obvious at this point what the database schema should look like. Figuring this out will require time as well as quite a bit of interaction with domain experts e.g. D. Monelli and/or F. Euchner.

Multiple spatial reference systems in NRML files

It is possible to define a spatial reference system (SRS) per geometry in a NRML file i.e. in the most complex case we could have a number of fault/source geometries with different spatial reference systems in the same file.

PostGIS databases on the other hand have a fixed SRS per geometry column. Hence the following questions arise:

1. What is a suitable SRS for the geometry columns in the PSHA inputs database schema (epsg:4326)?
2. Assuming that we use some SRS SRS_X for the geometry columns in the PSHA inputs database:
   1. Do we need to perform SRS transformations for all geometries encountered in an NRML file that use an SRS other than SRS_X (probably yes)?
   2. Is there such an SRS (that we can use for database geometry columns and) that facilitates arbitrary SRS transformations from/to it? If no: do we need to constrain the SRS' that may be used in NRML files?
   3. What SRS should we assume in the case where the srsName attribute is absent in the NRML file.

      Test data needed

We currently have some sample NRML files in docs/schema/examples and can work towards defining a database schema that's capable of handling these. In case more is needed we will work with the domain experts to provide the additional inputs.

Pivotal tracker link

https://www.pivotaltracker.com/story/show/12197273

[mmpagani]

Hi Muharem, comments below in blue.

We need a database schema for the PSHA input model

Problem description

We require a database schema for storing PSHA input model entities like: fault, rupture and seismic source.

The equivalent NRML (XML) schema exists already and the database schema must be interoperable with the former i.e. we must be able to convert as follows:

 `NRML` file ->  PSHA input database ->  `NRML` file

Solution outline

Define a postgres/postgis database schema that is capable of storing faults, ruptures and seismic sources. Very early work on the database schema has already started and it looks like this.

Problems and questions identified

High complexity

The NRML schema is very complex and it is not obvious at this point what the database schema should look like. Figuring this out will require time as well as quite a bit of interaction with domain experts e.g. D. Monelli and/or F. Euchner. For what concerns the science part, I suggest you to interact mostly with Damiano, Graeme and me

Multiple spatial reference systems in NRML files

It is possible to define a spatial reference system (SRS) per geometry in a NRML file i.e. in the most complex case we could have a number of fault/source geometries with different spatial reference systems in the same file. I think for the time being we must accept/work only with nrML files containing homogeneous information (with a single SRS) PostGIS databases on the other hand have a fixed SRS per geometry column. Hence the following questions arise:

1. What is a suitable SRS for the geometry columns in the PSHA inputs database schema (epsg:4326)? LGTM
2. Assuming that we use SRS_DB for the geometry columns in the PSHA inputs database:
   1. Do we need to perform SRS transformations for all geometries encountered in an NRML file that use an SRS other than SRS_DB (probably: yes)? Yes. However at the moment we have only information with spatial information that miss this level of detail i.e. the vertexes of area polygons are expressed in decimal degrees (no SRS specified - we can assume epsg:4326
   2. Is there a SRS that we can use for database geometry columns that facilitates arbitrary SRS transformations from/to it? If no: do we need to constrain the SRS' that may be used in NRML files?
   3. What SRS should we assume in the case where the srsName attribute is absent in the NRML file. See above

      Test data needed

We currently have some sample NRML files in docs/schema/examples and can work towards defining a database schema that's capable of handling these. In case more is needed we will work with the domain experts to provide the additional inputs.

I think the files you mention are fine for the time being. Damiano can provide with you more complex files. Thanks, Marco

[al-maisan]

Please note: upon completion this task will result in a number of scripts (bash/SQL) that can be executed. Once they are run the database is set up and ready to be used.

Please see also https://github.com/al-maisan/openquake/tree/db-schema

[al-maisan]

Done