Input data table formats

The Deliverable 4 "European Macroseismic Database" of the NERIES NA4 project called “European macroseismic database 1000-1600, M > 5.0” and its continuation in Deliverable 7 part 1 “European Macroseismic Database 1000-1750, M > 5.0” tries to establish a series of guidelines for macroseismic data compilation and validation.

MIDOP has been designed by the AHEAD Working Group and adopts all of its guidelines and it is currently used for publishing intensity maps of AHEAD (the European Archive of Historical Earthquake Data).

Field names presented in this page are merely a suggestion as MIDOP is able of associating any field contained in your table.

Please, note that there are restrinction rules within the field name, common rules while dealing with database tables:

• no spaces;
• no special characters, the only "safe" character is "_";
• no duplicated names.

The following symbols are used in this page:

• specifies which field should be used as the table primary key.
  A "primary key" in a database table refers to a field that contain values which cannot be ever repeated within the same table and are used
  in order to uniquely index the record of the table. Read the explanation about primary keys on Wikipedia.
• specifies which field in the table should be indexed.
  An indexed field greatly improves the speed of data retrieval operations.
  Indexing your tables is important when you have a lot of data, making MIDOP much snappier while generating both maps and tables.
  Read the explanation about database index on Wikipedia.
• “*” specifies a required field.
  Please note that other fields are not necessary but MIDOP will use them when filled.

List of tables:

• Earthquake catalogue table
  • Epicenters and magnitudes
• MDP (Macroseismic Data Point) table
• Map reference places

Earthquake catalogue table

The earthquake catalogue table contains the complete list of earthquakes that you are going to publish within MIDOP. It contains all the information about the whole earthquake, such as the origin time, the epicentral area and the source of information from which data are taken.

Field	Description	Type
EQid *	Earthquake unique identifier. Trailing spaces and special characters must be avoided (a simple integer number is advisable). (field used retrieving all the corresponding macroseismic intensity points)	text or number
Year *	Time of the event (year). Note that negative values are accepted.	integer
Mo *	Time of the event (month).	integer
Da *	Time of the event (day).	integer
Ho *	Time of the event (hours).	integer
Mi *	Time of the event (minutes).	integer
Se *	Time of the event (seconds).	integer
Ct	Comment to time.	text
Ax	Denomination of the area where the largest effects are located.	text
AxShort	Denomination of the area where the largest effects are located (shortened). (field useful for html tables that have a character space constraint)	text
Et	Earthquake type. Allowed values are: earthquake, induced earthquake, quarry blast, explosion, chemical explosion, nuclear explosion, landslide, rockslide, snow avalanche, debris avalanche, mine collapse, building collapse, volcanic eruption, meteor impact, plane crash, sonic boom, not existing, other, null	text
EQdeaths	People died during the earthquake.	text
EQinjured	The number of people injured during the earthquake.	text
EQlink	Link to an external webpage with more information about the earthquake.	text
EQnotes	Useful notes about the earthquake.	long text
MDPsetID *	MDP dataset identifier. (field used to link to the corresponding list of MDPs. In our experience the identifier of an earthquake differs from the identifier of a MDP dataset, but in your case you may decide that the two identifiers are the same)	text
StudyShort	Short bibliographical citation of the MDP dataset, mentioning the main author and the date of study publication (e.g.: Stucchi M., 2009 - Stucchi & Locati, 2009 - Stucchi et al., 2009)	text
StudyCode	An extremely simplified code representing the above study. Trailing spaces and special characters must be avoided. (field used to link to the corresponding record in the earthquake studies table and used for naming study related PDFs and images)	text or number

Epicenters and magnitudes

The earthquake catalogue table may contain a section with information about epicentres and magnitudes: if you want to plot such epicentres with MIDOP, you must add an additional set of fields to the catalogue table in order to describe the epicentre parameters. Below the list of available epicentre descriptors:

Field	Description	Type
EpLabel *	Epicentre label (will appear within the earthquake information frame).	text
EpSource	Epicentre location source.	text
EpLat *	Epicentre latitude (geographic coordinates in decimal degree).	decimal
EpLon *	Epicentre longitude (geographic coordinates in decimal degree).	decimal
EpUncRadius	Circular confidence region, expression of the horizontal uncertainty associated with the calculation of the epicentre. The value must expressed in meters.	text
EpUncEllipseAxisMajor	Major axis of the confidence ellipsoid, representing the location uncertainty associated with the calculation of the epicentre. The value must expressed in meters.	text
EpUncEllipseAxisMinor	Minor axis of the confidence ellipsoid, representing the location uncertainty associated with the calculation of the epicentre. The value must expressed in meters.	text
EpUncEllipseAzimuth	Azimuth of the confidence ellipsoid, representing the location uncertainty associated with the calculation of the epicentre.	text
EpIntensityScale	The macroseismic intensity scale used for assessing the epicentral intensity. e.g. MCS (Mercalli Cancani Sieberg), MM (Modified Mercalli), EMS98 (European Macroseismic Scale 1998), MSK64 or MSK81 (Medvedev-Sponheuer-Karnik).	text
EpIntensity	Epicentral intensity, expressed using a macroseismic intensity scale, such as MCS(Mercalli Cancani Sieberg) MM (Modified Mercalli), EMS98 (European Macroseismic Scale 1998), MSK (Medvedev-Sponheuer-Karnik) (e.g.: 6, 6-7, 7, 7-8, …).	text
EpIntensityNum	Epicentral intensity, expressed as pure numerical value (e.g.: 6, 6.5, 7, 7.5, …).	decimal
EpMagSource	Magnitude source (e.g.: references to a published paper).	text
EpMagStatus	Magnitude evaluation status. Allowed values are: preliminary, confirmed, reviewed, final, rejected.	text
EpMagMethod	Magnitude evaluation method.	text
EpMagType	Magnitude type (how the epicentre is obtained: manually or if calculated, the adopted method, mentioning the reference published paper or, at least, a descriptive text).	text
EpMag	Magnitude (might contains also text).	text
EpMagNum	Magnitude, expressed as pure numerical value.	decimal
EpMagUnc	Magnitude associated estimation uncertainty.	text
EpDepth	Hypocenter depth, expressed in kilometers.	text
EpDepthUnc	Hypocenter depth associated uncertainty.	text
EpBox	Box representing the surface projection of the sismogenic source. The field must be compiled with 4 couple of coordinates expressed in decimal degree. Use ";" to separate each couple of coordinates and "_" to separated longitude and latitude: lon1_lat1; lon2_lat2; lon3_lat3; lon4_lat4.	text

MIDOP allows more than one epicentre, each one represented with a different symbol.
In order to add another epicentre, another set of dedicated fields must be added to the catalogue table. Remember that within the same table two fields cannot have the same name, so you will have to change it, for instance by adding a progressing number (e.g.: Ep2Label, Ep2Source, Ep2Lal, Ep2Lon, Ep2Intensity, ...).

MDP (Macroseismic Data Point) table

The macroseismic intensity data table stores the complete list of Macroseismic Data Point (MDP) for each earthquake. Their scope is to describe as much as possible all those information retrieved in the original published earthquake study about places where the earthquake was felt.

Field	Description	Type
MDPsetID *	MDP dataset unique identifier. (field used in order to link the corresponding earthquake in the catalogue table)	text or number
MDPid *	Macroseismic Data Point (MDP) unique identifier. Trailing spaces and special characters must be avoided (a simple integer number is advisable).	text or number
PlaceID	Place unique identifier. It must be the same in every earthquake where the place is mentioned. Trailing spaces and special characters must be avoided (a simple integer number is advisable). It may differ from the original gazetteer identifier used for retrieving the place coordinates. (field used in order to generate place seismic histories: if not specified the “query by place” will not be available)	text or number
PlaceName	Place name	text
PlaceNameShort	Place name (shortened). (field used for html tables that have a character space constrain)	text
PlaceNameSource	Place name as found in the historical source.	text
PlaceSC	Place special case. See the two characters code reference listed in the table below.	text
PlaceLat *	Place latitude in geographical coordinates in decimal degree. (field used for plotting the place)	decimal
PlaceLon *	Place longitude in geographical coordinates in decimal degree. (field used for plotting the place)	decimal
IntensityScale	The macroseismic intensity scale used for assessing the intensity. e.g. MCS (Mercalli Cancani Sieberg), MM (Modified Mercalli), EMS98 (European Macroseismic Scale 1998), MSK64 or MSK81 (Medvedev-Sponheuer-Karnik).	text
Intensity *	Macroseismic intensity expressed using a macroseismic scale. Some unconventional descriptive code is accepted, see table below.	text
IntensityNum *	Numerical value corresponding to the expressed intensity. (field used for sorting tables and layering by intensity the plotted MDP on the earthquake map)	decimal
Reliability	Reliability (as available from the convention in use locally).	text
Reports	Number of reports used to assess the intensity.	text
EffectsEnv	List of effects on the environment caused or associated to the earthquake (e.g. explosion, felt, fire, flood, geyser activity, ground cracking, landslide, liquefaction, mine collapse, sand blow, seiche, sounds, subsidence, tsunami, uplift, volcanic activity, other). Use a semicolon to separate multiple effects.	text
EffectsInfr	List of effects on infrastructures caused or associated to the earthquake (e.g. bridges, drainage, electricity, gas, railway, roads, sanitation, telecommunications, water supply, other). Use a semicolon to separate multiple effects.	text
MDPdeaths	People died in the place during the earthquake.	text
MDPinjured	The number of people injured in the place during the earthquake.	text
Country	The country code of the locality as described in the ISO 3166-1 (see the reference table below).	text
Admin1code	First order country administrative division code (e.g. region code for Italy).	text
Admin1name	First order country administrative division name (e.g. region name for Italy).	text
Admin2code	Second order country administrative division code (e.g. province code for Italy).	text
Admin2name	Second order country administrative division name (e.g. province name for Italy).	text
Admin3code	Third order country administrative division code (e.g. ISTAT code for Italy).	text
Admin3name	Third order country administrative division name (e.g. comune name for Italy).	text
MDPlink	Link to an external webpage with more information about the MDP.	text
MDPnotes	Useful notes about the MDP. (it may be useful for keeping trace on how the intensity was assessed, which were the information taken into account, or simply for keeping trace on notes about the place, as they were reported in the historical sources)	long text

Further information about geographical Gazetteers can be found in a very interesting reading: "Georeferencing: The Geographical Associations of Information" [Hill L., 2006].

For your convenience, below is reported the simplified and generalized European Macroseismic Scale (EMS-98, [Grünthal et al., 1998]) which is nowadays the recommended macroseismic scale to be used while assessing intensities.

EMS-98 intensity	Definition	Description of typical observed effects (abstracted)
1	Not felt	Not felt.
2	Scarcely felt	Felt only by very few individual people at rest in houses.
3	Weak	Felt indoors by a few people. People at rest feel a swaying or light trembling.
4	Largely observed	Felt indoors by many people, outdoors by very few. A few people are awakened. Windows, doors and dishes rattle.
5	Strong	Felt indoors by most, outdoors by few. Many sleeping people awake. A few are frightened. Buildings tremble throughout. Hanging objects swing considerably. Small objects are shifted. Doors and windows swing open or shut.
6	Slightly damaging	Many people are frightened and run outdoors. Some objects fall. Many houses suffer slight non-structural damage like hair-line cracks and fall of small pieces of plaster.
7	Damaging	Most people are frightened and run outdoors. Furniture is shifted and objects fall from shelves in large numbers. Many well built ordinary buildings suffer moderate damage: small cracks in walls, fall of plaster, parts of chimneys fall down; older buildings may show large cracks in walls and failure of fill-in walls.
8	Heavily damaging	Many people find it difficult to stand. Many houses have large cracks in walls. A few well built ordinary buildings show serious failure of walls, while weak older structures may collapse.
9	Destructive	General panic. Many weak constructions collapse. Even well built ordinary buildings show very heavy damage: serious failure of walls and partial structural failure.
10	Very destructive	Many ordinary well built buildings collapse.
11	Devastating	Most ordinary well built buildings collapse, even some with good earthquake resistant design are destroyed.
12	Completely devastating	Almost all buildings are destroyed.

Notes on the intensity notation:

• so called uncertain intensity values, such as 7-8, 8-9, etc. are accepted;
• intensity notation is of fundamental importance in order to let MIDOP plot the right symbol.
  Do not use variants such roman numerals or other characters as they will not get plotted unless you create your own symbol set (see the chapter "Advanced Customization");
• MIDOP requires also a numerical translation of such intensity in order to being able to correctly sort tables and for the correctly layering of the intensity symbols on maps (lower intensities will be displayed underneath higher intensities).

Unconventional macroseismic descriptive codes reference table (extracted from NERIES NA4 Deliverable 7 part 1):

Code	Unconventional macroseismic descriptive code description
NR	Not Reported in the source.
NC	Not Classified, there is no enough information in the source describing the effects.
E	Environmental effects on the terrain (e.g.: landslide, liquefaction).
W	Environmental effects on water bodies (e.g.: effects produced by tsunamis).
NF	Not Felt.
SF	Slightly Felt.
F	Felt.
HF	Highly Felt.
SD	Slightly Damaged.
D	Damage.
HD	Heavily Damaged, destruction, extensive damage or total collapse .

Code	Special case	Description	Problem	Epicentral parameters assessment notes
TE	large area, territory	area containing several localities; the size of the area exceeds the one suggested by the EMS98	intensity assignment is not compatible with the definition of intensity and any location would be arbitrary	coordinates and intensity must not be assessed. F, D or HD and arbitrary coordinates, only for graphical representation, should be assessed. These MDPs must not be used for earthquake parameters determination.
UL	unknown (not located)  locality	a place which both the author of the study and the MDP compiler is not able to locate today	coordinates cannot be assigned	these MDPs cannot be used for earthquake parameters determination
SS	small settlement	settlement the size of which is too small to supply a significant building sample for intensity assessment	intensity assignment is not compatible with the statistical meaning of intensity	coordinates are assessed; F, D or HD are assessed. As a first choice these MDPs should not be used for earthquake parameters determination
IB	isolated building	single (isolated) building. A building standing alone, like a light tower, a country church etc.	intensity assignment is not compatible with the statistical meaning of intensity	coordinates are assessed; F, G3, G4 or G5 (grade damage of the EMS98) are assessed. These MDPs should not be used for earthquake parameters determination
MS	multiple settlement	settlement whose traditional place name refers to a set of small settlements in a limited area, including small islands	information may not strictly refer to the place name. However, no better interpretation can be provided	assess intensity and coordinates. The code represents a warning for the user
DL	deserted locality	abandoned locality, eventually rebuilt elsewhere with the same or another name	the seismic history may show interruptions or non seismic gaps	assess intensity and coordinates. The code is a warning for understanding the seismic history
AL	absorbed locality	a locality absorbed into a larger one	same as above	assess intensity and coordinates. The code is a warning for understanding the seismic history
CQ	city quarter	information related to part of a city	place name and the coordinates may be somewhat arbitrary	assess Is and coordinates. The code is a warning for understanding the seismic history

List of country codes as in the ISO 3166-1 standard:

North America Canada CA Greenland GL Mexico MX Saint Pierre and Miquelon PM United States US   Central America Belize BZ Costa Rica CR El Salvador SV Guatemala GT Honduras HN Nicaragua NI Panama PA   Caribbean Anguilla AI Antigua and Barbuda AG Aruba AW Bahamas BS Barbados BB Bonaire, Sint Eustatius and Saba BQ Cayman Islands KY Cuba CU Curaçao CW Dominica DM Dominican Republic DO Grenada GD Guadeloupe GP Haiti HT Jamaica JM Martinique MQ Montserrat MS Puerto Rico PR Saint Barthélemy BL Saint Kitts and Nevis KN Saint Lucia LC Saint Martin (French part) MF Saint Vincent and the Grenadines VC Sint Maarten (Dutch part) SX Trinidad and Tobago TT Turks and Caicos Islands TC Virgin Islands, British VG Virgin Islands, U.S. VI   South America Argentina AR Bolivia, Plurinational State of BO Brazil BR Chile CL Colombia CO Ecuador EC French Guiana GF Guyana GY Paraguay PY Peru PE Suriname SR Uruguay UY Venezuela, Bolivarian Republic of VE   Atlantic Ocean Bermuda BM Falkland Islands (Malvinas) FK Saint Helena, Ascension and Tristan da Cunha SH South Georgia and the South Sandwich Islands GS   Southeast Asia Brunei Darussalam BN Cambodia KH Indonesia ID Lao People's Democratic Republic LA Malaysia MY Myanmar MM Philippines PH Singapore SG Thailand TH Timor-Leste TL Viet Nam VN

Europe Åland Islands AX Albania AL Andorra AD Austria AT Belarus BY Belgium BE Bosnia and Herzegovina BA Bulgaria BG Croatia HR Czech Republic CZ Denmark DK Estonia EE Faroe Islands FO Finland FI France FR Germany DE Gibraltar GI Greece GR Guernsey GG Holy See (Vatican City State) VA Hungary HU Iceland IS Ireland IE Isle of Man IM Italy IT Jersey JE Latvia LV Liechtenstein LI Lithuania LT Luxembourg LU Macedonia, The Former Yugoslav Republic of MK Malta MT Moldova, Republic of MD Monaco MC Montenegro ME Netherlands NL Norway NO Poland PL Portugal PT Romania RO San Marino SM Serbia RS Slovakia SK Slovenia SI Spain ES Svalbard and Jan Mayen SJ Sweden SE Switzerland CH Ukraine UA United Kingdom GB   Middle East Bahrain BH Cyprus CY Iran, Islamic Republic of IR Iraq IQ Israel IL Jordan JO Kuwait KW Lebanon LB Oman OM Palestinian Territory, Occupied PS Qatar QA Saudi Arabia SA Syrian Arab Republic SY Turkey TR United Arab Emirates AE Yemen YE   Asia Afghanistan AF Armenia AM Azerbaijan AZ Bangladesh BD Bhutan BT China CN Georgia GE Hong Kong HK India IN Japan JP Kazakhstan KZ Korea, Democratic People's Republic of KP Korea, Republic of KR Kyrgyzstan KG Macao MO Mongolia MN Nepal NP Pakistan PK Russian Federation RU Taiwan, Province of China TW Tajikistan TJ Turkmenistan TM Uzbekistan UZ

Africa Algeria DZ Angola AO Benin BJ Botswana BW Burkina Faso BF Burundi BI Cameroon CM Cape Verde CV Central African Republic CF Chad TD Congo CG Congo, the Democratic Republic of the CD Côte d'Ivoire CI Djibouti DJ Egypt EG Equatorial Guinea GQ Eritrea ER Ethiopia ET Gabon GA Gambia GM Ghana GH Guinea GN Guinea-Bissau GW Kenya KE Lesotho LS Liberia LR Libya LY Malawi MW Mali ML Mauritania MR Morocco MA Mozambique MZ Namibia NA Niger NE Nigeria NG Rwanda RW Sao Tome and Principe ST Senegal SN Sierra Leone SL Somalia SO South Africa ZA South Sudan SS Sudan SD Swaziland SZ Tanzania, United Republic of TZ Togo TG Tunisia TN Uganda UG Western Sahara EH Zambia ZM Zimbabwe ZW   Indian Ocean Bouvet Island BV British Indian Ocean Territory IO Christmas Island CX Cocos (Keeling) Islands CC Comoros KM French Southern Territories TF Heard Island and McDonald Islands HM Madagascar MG Maldives MV Mauritius MU Mayotte YT Réunion RE Seychelles SC Sri Lanka LK   Oceania American Samoa AS Australia AU Cook Islands CK Fiji FJ French Polynesia PF Guam GU Kiribati KI Marshall Islands MH Micronesia, Federated States of FM Nauru NR New Caledonia NC New Zealand NZ Niue NU Norfolk Island NF Northern Mariana Islands MP Palau PW Papua New Guinea PG Pitcairn PN Samoa WS Solomon Islands SB Tokelau TK Tonga TO Tuvalu TV United States Minor Outlying Islands UM Vanuatu VU Wallis and Futuna WF

Map reference places

MIDOP can show place names as a background information on the maps as it help users better locating the geographical area where the earthquake occurred.

These place names shows up in the map based on the zoom level and have been manually selected by the AHEAD working group for all Europe.

As most MIDOP elements, these places can be easily customised changing the corresponding MySQL table, commonly named “ref_places”.

Field	Description	Type
RecordID *	Record unique identifier. This is not a place identifier, as the same place can be inserted more than one time.	text or number
PlaceName *	Place name.	text
PlaceNameDistant *	Place name displayed when the map will be zoomed out.	text
PlaceLat *	Place latitude in geographical coordinates in decimal degree.	decimal
PlaceLon *	Place latitude in geographical coordinates in decimal degree.	decimal
ZoomLevel *	When to show the place on the map. MIDOP accepts one of these 3 zoom values: “large”, “medium” and “detail”. When empty, the place will NOT be plotted.	text
GeographicalArea *	Geographical area code where the place record should be used. Here a compleate list of supported geographical areas.	text

In top of the place information (place name and coordinates), MIDOP requires a couple of special information:

• "Zoom level", where only three values are allowed "large", "medium" and "detail". This information will be taken into account for deciding when the place name should appear on the map.

Example of a large zoom. Only those places marked “large” are plotted.

Example of a medium scaled zoom. Both places marked “large” and “medium” are plotted.

Example of a detailed zoom. All places “large”, “medium” and “detail” are plotted.

• "Geographical Area", where it should be expressed in which of the MIDOP available geographical layers (here a list) the place should appear. For example if you want to show “Paris” both in UK and France maps, you must insert “Paris” twice in this table, one will be used while generating maps for the UK area, and the another will be used while generating maps for the France area.