This notebook was developed by Keneth Garcia. Source and license info are on GitHub.

SeisComP SQL Queries Introduction¶

This notebook is an introduction to the SQL queries in SeisComP systems. It is intended to be used as a reference for the SieveComP package in order to build the SQL files received by its API. To further information about the SieveComP package, please refer to the GitHub repository.

1. Making queries to the database¶

Probably one of the most important aspects in the use of SieveComP is the ability to make queries to SeisComP databases. This is a very important aspect because it allows us to extract the data that we need to perform the analysis. In the package, the pymysql library is used to make queries to the database. You can read more about this library here.

Let's start by importing the library:

pymsql requires the following parameters to connect to the database:

• host: the host of the database
• user: the user to connect to the database
• password: the password of the user
• database: the name of the database
• port: the port of the database

In this case, if you define correctly the connection parameters to SieveComP, the package will be able to connect to the database and make queries. The following code snippet shows how to connect to the database:

Now, with the connection established, let's continue with the queries to the database. Here we will use pandas to store the results of the queries in a DataFrame. You can read more about pandas here. In particular, the method read_sql allows us to read the results of a query directly into a DataFrame. This function requires as argument the query and the connection to the database. The queries can be a little bit complex, but in general, they are simple. For example, the following query retrieves the first 10 rows of the table event:

IMPORTANT: These examples shows how to use simple queries to retrieve data from SeisComP databases. You can use this or more complex queries to retrieve the data that you need in SieveComP. The queries can be stored in a SQL file and then read by the package to make the queries to the database. You can read more about this in the Quick Start Guide.

And the last 20 rows of the table event, containing the last 20 events processed by analysts or automatic systems:

As you can notice, the table have the following columns:

But, how we can access to more global parameters, such as the uncertainty of the location of the events? This information is stored in the table Origin. The following query retrieves the last 10 rows of the table Origin:

This table contains all the required columns to create an origin in SeisComP3. Usually the main columns used to analyze any event in SeisComP are the following:

• _oid: the unique identifier of the origin
• time_value: the origin time of the event
• latitude_value: the latitude of the event
• latitude_uncertainty: the uncertainty of the latitude
• longitude_value: the longitude of the event
• longitude_uncertainty: the uncertainty of the longitude
• depth_value: the depth of the event
• depth_uncertainty: the uncertainty of the depth
• methodID: the method used to calculate the origin
• earthModelID: the earth model used to calculate the origin
• quality_associatedPhaseCount: the number of TOTAL associated phases
• quality_usedPhaseCount: the number of used phases for localization
• quality_usedStationCount: the number of used stations for localization
• quality_azimuthalGap: the azimuthal gap of the localization
• creationInfo_author: the author of the origin, i.e. the analyst who created the origin
• creationInfo_creationTime: the creation time of the origin

WARNING: The table Origin contains all the origins without any filter. It means that, for a unique event, there can be multiple origins. Notice that some _oid have the same time value.

Similar to the Event and Origin tables, there is a lot of information stored in SeisComP databases, depending on the specific network design. You can see all the tables contained in the database by running the following query:

For example, the table Magnitude contains the magnitude information of the events. The following query retrieves the last 10 rows of the table Magnitude:

Summary¶

As you can see, the queries to the database are very simple and can be used to retrieve the data that you need. You can use these queries to build the SQL files that are used by the SieveComP package to make queries to the database. You can also use these queries to retrieve the data that you need for your analysis.

For expert users, the limit is the sky, you can build complex queries to retrieve the data that you need. You can also use these queries to retrieve the data that you need for your analysis. For example, at SGC - RSNC we use the following query (added in the examples folder of the package):

SELECT
    Origin.time_value,  -- Event origin time
    POEv.publicID,  -- Event public ID
    Origin.depth_value,  -- Event depth
    Magnitude.magnitude_value,  -- Event magnitude
    Origin.quality_standardError,  -- Standard error of the origin time
    Origin.depth_uncertainty, -- Uncertainty of the event depth
    Origin.latitude_uncertainty,  -- Uncertainty of the event latitude
    Origin.longitude_uncertainty,  -- Uncertainty of the event longitude
    Origin.quality_associatedPhaseCount,  -- Number of associated phases
    Origin.quality_usedPhaseCount,  -- Number of used phases
    Origin.creationInfo_author,  -- Author of the origin creation info
    Origin.quality_usedStationCount,  -- Number of used stations
    Event.type AS event_type,  -- Event type (e.g., earthquake, explosion)
    Origin.creationInfo_agencyID,  -- Agency ID of the origin creation info
    EventDescription.text,  -- Event description text
    Origin.latitude_value,  -- Event latitude
    Origin.longitude_value,  -- Event longitude
    Magnitude.type AS magnitude_type,  -- Magnitude type (e.g., Mw, ML)
    Origin.methodID,  -- Method ID used for the origin
    Origin.earthModelID,  -- Earth model ID used for the origin
    Comment.text AS comment  -- Comment text (e.g., DESTACADO)
FROM Event AS EvMF
    LEFT JOIN PublicObject AS POEv ON EvMF._oid = POEv._oid
    LEFT JOIN PublicObject AS POOri ON EvMF.preferredOriginID = POOri.publicID
    LEFT JOIN Origin ON POOri._oid = Origin._oid
    LEFT JOIN PublicObject AS POMag ON EvMF.preferredMagnitudeID = POMag.publicID
    LEFT JOIN Magnitude ON Magnitude._oid = POMag._oid
    LEFT JOIN Event ON Event._oid= POEv._oid
    LEFT JOIN EventDescription ON EvMF._oid = EventDescription._parent_oid
    LEFT JOIN Comment ON Comment._parent_oid = EvMF._oid

as the general query to retrieve the information of the events in the SGC - RSNC SeisComP database. This query retrieves the information of the events, where:

• EvMF is the Event table, which contains the main information about the event, such as the origin time, depth, magnitude, etc.
• POEv is the PublicObject table, which contains the public ID of the event, which is used to link the Event table with the Origin and Magnitude tables.
• POOri is the PublicObject table, which contains the public ID of the origin, which is used to link the Origin table with the Event table.
• Origin is the Origin table, which contains the information about the origin of the event, such as the time, depth, latitude, longitude, etc.
• POMag is the PublicObject table, which contains the public ID of the magnitude, which is used to link the Magnitude table with the Event table.
• Magnitude is the Magnitude table, which contains the information about the magnitude of the event, such as the magnitude value, type, etc.
• EventDescription is the EventDescription table, which contains the description of the event, such as the text description, etc.

In this SQL query, we are selecting the following fields:

• Origin.time_value: the origin time of the event
• POEv.publicID: the public ID of the event
• Origin.depth_value: the depth of the event
• Magnitude.magnitude_value: the magnitude of the event
• Origin.quality_standardError: the standard error of the origin
• Origin.depth_uncertainty: the uncertainty of the depth
• Origin.latitude_uncertainty: the uncertainty of the latitude
• Origin.longitude_uncertainty: the uncertainty of the longitude
• Origin.quality_associatedPhaseCount: the number of associated phases
• Origin.creationInfo_author: the author of the origin
• Event.type: the type of the event
• Origin.creationInfo_agencyID: the agency ID of the origin
• EventDescription.text: the text description of the event
• Origin.latitude_value: the latitude of the event
• Origin.longitude_value: the longitude of the event
• Magnitude.type: the type of the magnitude
• Origin.methodID: the method ID of the origin
• Origin.earthModelID: the earth model ID of the origin

and you can also add an origin time condition to the query, such that the origin time of the event is between a certain time range, at the last part of the query.

Finally, do not forget to close the connection to the database when you are done with your queries. This is important to avoid any issues with the database connection. You can do this by running the following code: