(Beta) MPC Database Tables Schema
The MPC makes its PostgreSQL database of observations and orbits available for replication via the SBN. Displayed below are two tables.- The first table lists the tables and a description of its contents.
- The second table displayed lists each table and each field within each table and a description of it.
A machine-readable format of each table and its schema can be downloaded here.
To order the columns click the header to sort the data.
Last Updated: 2023-09-30
| Table Name | Description |
|---|---|
| obs | None |
| neocp_events | None |
| current_identifications | All single-designations, and all identifications between designations. Always uses primary provisional designation (even for numbered objects). Includes all comets and satellites. |
| neocp_var | None |
| neocp_prev_des | None |
| neocp_obs_archive | None |
| neocp_obs | None |
| orbfit_results | It is intended that the fields in this table will contain the immediate results from orbifits, in the as-supplied json/dictionary format. It is intended that these fields will ... (i) Be supplied back to the orbit-fit code on subsequent calls (rwo_json and mid_epoch_json are the two that would be supplied) (ii) Be expanded out (trigger? generated-fields?) to populate detailed tables (rwo_json, mid_epoch_json, stamndard_epoch_json, quality_json would all be expanded out) |
| obs_alterations_corrections | It is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce UNPUBLISH/UNASSOCIATION/SEND-TO-ITF Some/all of these changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form. |
| obs_alterations_redesignations | It is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce REDESIGNATIONS These changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form. |
| obs_alterations_deletions | It is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce DELETION Some/all of these changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form. |
| obs_alterations_unassociations | It is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce UNPUBLISH/UNASSOCIATION/SEND-TO-ITF Some/all of these changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form. |
| neocp_els | None |
| numbered_identifications | Numbers and Names for any objects that have been Numbered or Named. Linked to primary-provisional-designation in current_identifications |
| primary_objects | All Objects Live Here: Labelled by their primary provisional designation. Fields indicate the tables in which any orbit information might be found |
Last Updated: 2023-09-30
| Table Name | Column | Data Type | DateTime Precision (if applicable) | Description |
|---|---|---|---|---|
| current_identifications | id | int4 | None | None |
| current_identifications | packed_primary_provisional_designation | text | None | The primary provisional designation in packed form (e.g. K08A00B) |
| current_identifications | packed_secondary_provisional_designation | text | None | The secondary provisional designation in packed form (e.g. K08A00B). May be the same-as (A=A) or different-to (A=B) the primary provisional designation |
| current_identifications | unpacked_primary_provisional_designation | text | None | The primary provisional designation in unpacked form (e.g. 2008 AB) |
| current_identifications | unpacked_secondary_provisional_designation | text | None | The secondary provisional designation in unpacked form (e.g. 2008 AB). May be the same-as (A=A) or different-to (A=B) the primary provisional designation |
| current_identifications | published | int4 | None | Has this been published yet? i.e. has it been published in the DOU, Mid month, or monthly products. Field values: 0=unpublished ; 1=published as MPEC; 2=published in DOU ; 3=published in mid-month ; 4=published in monthly |
| current_identifications | identifier_ids | _text | None | This is a set of unique identifier_ids in an array e.g. {1e96e1ba-63be-11ea-8e74-801844df97e0,1eb8bdee-63be-11ea-8e74-801844df97e0} that can link to multiple records in the identification_metadata table. Multiple people can suggest A=B and this would be recorded in the identification_metadata table. This is important for the backfill when reading the flat files. So in the backfill scenario, if the record exists and it has a different name and/or reference then 1. add to the id_history, then add to metadata, then UPDATE columns publication_references, identifier_ids in current_identifications otherwise if no record exists create one |
| current_identifications | object_type | int4 | None | Integer to indicate the object type. To be linked (foreign key) to object_type lookup table |
| current_identifications | numbered | bool | None | Has the object been numbered and hence does it appear in the numbered_objects table? |
| current_identifications | created_at | timestamp | 6 | None |
| current_identifications | updated_at | timestamp | 6 | None |
| mpc_orbits | id | int4 | None | None |
| mpc_orbits | packed_primary_provisional_designation | text | None | The primary provisional designation in packed form (e.g. K08A00B). UNIQUE |
| mpc_orbits | unpacked_primary_provisional_designation | text | None | The primary provisional designation in UNpacked form (e.g. 2008 AB). UNIQUE |
| mpc_orbits | mpc_orb_jsonb | jsonb | None | None |
| mpc_orbits | created_at | timestamp | 6 | None |
| mpc_orbits | updated_at | timestamp | 6 | None |
| neocp_els | id | int4 | None | None |
| neocp_els | desig | varchar | None | None |
| neocp_els | els | varchar | None | None |
| neocp_els | dsc_obs | varchar | None | None |
| neocp_els | digest2 | numeric | None | None |
| neocp_els | flag | bpchar | None | None |
| neocp_els | prep | bpchar | None | None |
| neocp_els | comet | bpchar | None | None |
| neocp_els | created_at | timestamp | 6 | None |
| neocp_els | updated_at | timestamp | 6 | None |
| neocp_events | id | int4 | None | None |
| neocp_events | desig | text | None | None |
| neocp_events | event_type | text | None | None |
| neocp_events | event_text | text | None | None |
| neocp_events | event_user | text | None | None |
| neocp_events | created_at | timestamp | 6 | None |
| neocp_events | updated_at | timestamp | 6 | None |
| neocp_obs | id | int4 | None | None |
| neocp_obs | desig | varchar | None | None |
| neocp_obs | trkid | text | None | None |
| neocp_obs | obs80 | varchar | None | None |
| neocp_obs | rmstime | numeric | None | None |
| neocp_obs | rmsra | numeric | None | None |
| neocp_obs | rmsdec | numeric | None | None |
| neocp_obs | rmscorr | numeric | None | None |
| neocp_obs | force_code | text | None | None |
| neocp_obs | created_at | timestamp | 6 | None |
| neocp_obs | updated_at | timestamp | 6 | None |
| neocp_obs_archive | id | int4 | None | None |
| neocp_obs_archive | desig | varchar | None | None |
| neocp_obs_archive | trkid | text | None | None |
| neocp_obs_archive | obs80 | varchar | None | None |
| neocp_obs_archive | rmstime | numeric | None | None |
| neocp_obs_archive | rmsra | numeric | None | None |
| neocp_obs_archive | rmsdec | numeric | None | None |
| neocp_obs_archive | rmscorr | numeric | None | None |
| neocp_obs_archive | force_code | text | None | None |
| neocp_obs_archive | created_at | timestamp | 6 | None |
| neocp_obs_archive | updated_at | timestamp | 6 | None |
| neocp_prev_des | id | int4 | None | None |
| neocp_prev_des | desig | text | None | None |
| neocp_prev_des | status | text | None | None |
| neocp_prev_des | iau_desig | text | None | None |
| neocp_prev_des | pkd_desig | text | None | None |
| neocp_prev_des | ref | text | None | None |
| neocp_prev_des | digest2 | numeric | None | None |
| neocp_prev_des | created_at | timestamp | 6 | None |
| neocp_prev_des | updated_at | timestamp | 6 | None |
| neocp_var | id | int4 | None | None |
| neocp_var | desig | varchar | None | None |
| neocp_var | els | varchar | None | None |
| neocp_var | created_at | timestamp | 6 | None |
| neocp_var | updated_at | timestamp | 6 | None |
| numbered_identifications | id | int4 | None | None |
| numbered_identifications | packed_primary_provisional_designation | text | None | Keeping a map of packed (K03B35W) and unpacked (2003 BW35) |
| numbered_identifications | unpacked_primary_provisional_designation | text | None | Keeping a map of packed (K03B35W) and unpacked (2003 BW35) |
| numbered_identifications | permid | text | None | The ADES version of the permanent ID which ... follows the associated IAU designation very closely, with the exception of removing parentheses around the permanent designation of minor planets ... For natural satellites, the IAU designation is preserved except that in permanent designations arabic numerals are used instead of roman numerals and for permanent designations of satellites of minor planets the name of the central body is neglected. Could be 541130 (Minor Planet), 1 (Minor Planet), 257P (Comet), 73P-AC (Comet Fragment), Jupiter 13 (Satellite - of Jupiter) |
| numbered_identifications | iau_designation | text | None | None |
| numbered_identifications | iau_name | text | None | For example, 1 = Ceres, 57567 = Crikey |
| numbered_identifications | numbered_publication_references | _text | None | Array of references to any MPC publication(s) that concern the numbering of this object (e.g. Monthly-MPC, etc) |
| numbered_identifications | named_publication_references | _text | None | None |
| numbered_identifications | naming_credit | text | None | None |
| numbered_identifications | created_at | timestamp | 6 | None |
| numbered_identifications | updated_at | timestamp | 6 | None |
| obs_alterations_corrections | id | int4 | None | None |
| obs_alterations_corrections | obsid_old | text | None | The unique obsid of the OLD observation that was REPLACED |
| obs_alterations_corrections | obsid_new | text | None | The unique obsid of the NEW observation that REPLACES the obsid_old |
| obs_alterations_corrections | publication_ref | _text | None | Array of reference(s) to pubn(s) announcing this unassociation |
| obs_alterations_corrections | created_at | timestamp | 6 | None |
| obs_alterations_corrections | updated_at | timestamp | 6 | None |
| obs_alterations_deletions | id | int4 | None | None |
| obs_alterations_deletions | obsid | text | None | The unique obsid of the observation that was altered. Links to the observation record in the obs table. |
| obs_alterations_deletions | publication_ref | _text | None | Array of reference(s) to pubn(s) announcing this deletion |
| obs_alterations_deletions | status | int4 | None | Integer to indicate state of publication. 0=Unpublished/Needs-to-be-published, 1=Published in DOU, 2=Published in Monthly |
| obs_alterations_deletions | created_at | timestamp | 6 | None |
| obs_alterations_deletions | updated_at | timestamp | 6 | None |
| obs_alterations_redesignations | id | int4 | None | None |
| obs_alterations_redesignations | obsid | text | None | The unique obsid of the observation that was altered. Links to the observation record in the obs table. |
| obs_alterations_redesignations | packed_provisional_designation_from | text | None | Original packed provisional designation |
| obs_alterations_redesignations | unpacked_provisional_designation_from | text | None | Original unpacked provisional designation |
| obs_alterations_redesignations | packed_provisional_designation_to | text | None | New packed provisional designation |
| obs_alterations_redesignations | unpacked_provisional_designation_to | text | None | New unpacked provisional designation |
| obs_alterations_redesignations | publication_ref | _text | None | Array of reference(s) to pubn(s) announcing this redesignation |
| obs_alterations_redesignations | status | int4 | None | Integer to indicate state of publication. 0=Unpublished/Needs-to-be-published, 1=Published in DOU, 2=Published in Monthly |
| obs_alterations_redesignations | created_at | timestamp | 6 | None |
| obs_alterations_redesignations | updated_at | timestamp | 6 | None |
| obs_alterations_redesignations | new_designation_created | bool | None | None |
| obs_alterations_unassociations | id | int4 | None | None |
| obs_alterations_unassociations | obsid | text | None | The unique obsid of the observation that was altered. Links to the observation record in the obs table. |
| obs_alterations_unassociations | unpacked_provisional_designation_from | text | None | None |
| obs_alterations_unassociations | packed_provisional_designation_from | text | None | None |
| obs_alterations_unassociations | trkmpc_to | text | None | New trkMPC used to label the observations within the ITF |
| obs_alterations_unassociations | publication_ref | _text | None | Array of reference(s) to pubn(s) announcing this unassociation |
| obs_alterations_unassociations | created_at | timestamp | 6 | None |
| obs_alterations_unassociations | updated_at | timestamp | 6 | None |
| obs_sbn | id | int4 | None | None |
| obs_sbn | trksub | text | None | None |
| obs_sbn | trkid | text | None | None |
| obs_sbn | obsid | text | None | None |
| obs_sbn | submission_id | text | None | None |
| obs_sbn | submission_block_id | text | None | None |
| obs_sbn | obs80 | text | None | None |
| obs_sbn | status | bpchar | None | None |
| obs_sbn | ref | text | None | None |
| obs_sbn | healpix | int8 | None | None |
| obs_sbn | permid | text | None | None |
| obs_sbn | provid | text | None | None |
| obs_sbn | artsat | text | None | None |
| obs_sbn | mode | text | None | None |
| obs_sbn | stn | text | None | None |
| obs_sbn | trx | text | None | None |
| obs_sbn | rcv | text | None | None |
| obs_sbn | sys | text | None | None |
| obs_sbn | ctr | int4 | None | None |
| obs_sbn | pos1 | numeric | None | None |
| obs_sbn | pos2 | numeric | None | None |
| obs_sbn | pos3 | numeric | None | None |
| obs_sbn | poscov11 | numeric | None | None |
| obs_sbn | poscov12 | numeric | None | None |
| obs_sbn | poscov13 | numeric | None | None |
| obs_sbn | poscov22 | numeric | None | None |
| obs_sbn | poscov23 | numeric | None | None |
| obs_sbn | poscov33 | numeric | None | None |
| obs_sbn | prog | text | None | None |
| obs_sbn | obstime | timestamp | 6 | None |
| obs_sbn | ra | numeric | None | None |
| obs_sbn | dec | numeric | None | None |
| obs_sbn | rastar | numeric | None | None |
| obs_sbn | decstar | numeric | None | None |
| obs_sbn | obscenter | text | None | None |
| obs_sbn | deltara | numeric | None | None |
| obs_sbn | deltadec | numeric | None | None |
| obs_sbn | dist | numeric | None | None |
| obs_sbn | pa | numeric | None | None |
| obs_sbn | rmsra | numeric | None | None |
| obs_sbn | rmsdec | numeric | None | None |
| obs_sbn | rmsdist | numeric | None | None |
| obs_sbn | rmspa | numeric | None | None |
| obs_sbn | rmscorr | numeric | None | None |
| obs_sbn | delay | numeric | None | None |
| obs_sbn | rmsdelay | numeric | None | None |
| obs_sbn | doppler | numeric | None | None |
| obs_sbn | rmsdoppler | numeric | None | None |
| obs_sbn | astcat | text | None | None |
| obs_sbn | mag | numeric | None | None |
| obs_sbn | rmsmag | numeric | None | None |
| obs_sbn | band | text | None | None |
| obs_sbn | photcat | text | None | None |
| obs_sbn | photap | numeric | None | None |
| obs_sbn | nucmag | int2 | None | None |
| obs_sbn | logsnr | numeric | None | None |
| obs_sbn | seeing | numeric | None | None |
| obs_sbn | exp | numeric | None | None |
| obs_sbn | rmsfit | numeric | None | None |
| obs_sbn | com | int2 | None | None |
| obs_sbn | frq | numeric | None | None |
| obs_sbn | disc | bpchar | None | None |
| obs_sbn | subfrm | text | None | None |
| obs_sbn | subfmt | text | None | None |
| obs_sbn | prectime | int4 | None | None |
| obs_sbn | precra | numeric | None | None |
| obs_sbn | precdec | numeric | None | None |
| obs_sbn | unctime | numeric | None | None |
| obs_sbn | notes | text | None | None |
| obs_sbn | remarks | text | None | None |
| obs_sbn | deprecated | bpchar | None | None |
| obs_sbn | localuse | text | None | None |
| obs_sbn | nstars | int4 | None | None |
| obs_sbn | prev_desig | text | None | None |
| obs_sbn | prev_ref | text | None | None |
| obs_sbn | rmstime | numeric | None | None |
| obs_sbn | created_at | timestamptz | 6 | None |
| obs_sbn | updated_at | timestamptz | 6 | None |
| obs_sbn | trkmpc | text | None | None |
| obs_sbn | orbit_id | text | None | None |
| obs_sbn | designation_asterisk | bool | None | None |
| obs_sbn | all_pub_ref | _text | None | None |
| obs_sbn | shapeocc | bool | None | None |
| obs_sbn | obssubid | text | None | None |
| obs_sbn | replacesobsid | text | None | None |
| obs_sbn | group_id | text | None | None |
| primary_objects | id | int4 | None | None |
| primary_objects | packed_primary_provisional_designation | text | None | The primary provisional designation in packed form (e.g. K08A00B). UNIQUE |
| primary_objects | unpacked_primary_provisional_designation | text | None | The primary provisional designation in UNpacked form (e.g. 2008 AB). UNIQUE |
| primary_objects | status | int4 | None | granular status of orbfit results |
| primary_objects | standard_minor_planet | bool | None | Boolean to indicate whether the object-orbit is specified in the standard_minor_planet table |
| primary_objects | standard_epoch | bool | None | If in standard_minor_planet, boolean indicates whether orbit at standard-epoch is populated |
| primary_objects | orbfit_epoch | bool | None | If in standard_minor_planet, boolean indicates whether orbit at mid-observation-epoch is populated |
| primary_objects | nongravs | bool | None | Boolean to indicate whether the object-orbit is specified in the nongravs table |
| primary_objects | satellite | bool | None | Boolean to indicate whether the object-orbit is specified in the satellite table |
| primary_objects | comet | bool | None | Boolean to indicate whether the object-orbit is specified in the comet table |
| primary_objects | barycentric | bool | None | Boolean to indicate whether the object-orbit is specified in the barycentric table. N.B. Can be in standard-table AND barycentric table |
| primary_objects | no_orbit | bool | None | If no orbit calculated: new insert; no-possible calc; ... |
| primary_objects | orbit_publication_references | _text | None | Array of references to MPC publication(s) that contained this particular orbit calculation (e.g. DOU MPEC, mid-month, Monthly-MPC, etc) |
| primary_objects | flag_all_object_obs_consistent | bool | None | flag if/when all observations for an object have been checked to be consistent with obs-files |
| primary_objects | flag_orbit_calculated_from_consistent_obs | bool | None | Was this orbit calculated using obs flagged as consistent ? (may be necessary while developing / transitioning) |
| primary_objects | flag_allowed_external | bool | None | If flag_all_object_obs_consistent=True and flag_orbit_calculated_from_consistent_obs=True, then set flag_allowed_external = True |
| primary_objects | created_at | timestamp | 6 | None |
| primary_objects | updated_at | timestamp | 6 | None |
| primary_objects | orbit_published | int4 | None | Has this ORBIT (not object-desig) been published yet? i.e. has it been published in the DOU, Mid month, or monthly products. Field values: 0=unpublished ; 1=published as MPEC; 2=published in DOU ; 3=published in mid-month ; 4=published in monthly |
| primary_objects | object_type | int4 | None | None |