Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-36489

Implement spatial indexing for live obscore table.

    XMLWordPrintable

    Details

      Description

      Support for basic live obscore table was added in DM-35947, but it is lacking database-native spatial column with indexing. This ticket will implement that remaining piece. I want to make it flexible and configuration-driven to be able to use either pgSphere or PostGIS (or may be both).

        Attachments

          Issue Links

            Activity

            No builds found.
            salnikov Andy Salnikov created issue -
            salnikov Andy Salnikov made changes -
            Field Original Value New Value
            Link This issue is triggered by DM-35947 [ DM-35947 ]
            salnikov Andy Salnikov made changes -
            Status To Do [ 10001 ] In Progress [ 3 ]
            Hide
            salnikov Andy Salnikov added a comment -

            Some progress with pgSphere, and there is now a general plugin framework to support extensible way to add more columns. I decided not to implement PostGIS plugin for now, so there is only a pgSphere currently. The schema with these new columns looks like:

            test_postgis=> \d+  obscore;
                                                                                             Table "public.obscore"
                   Column        |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target |                           Description
            ---------------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+------------------------------------------------------------------
             dataproduct_type    | text                  |           |          |         | extended |             |              | Logical data product type
             dataproduct_subtype | text                  |           |          |         | extended |             |              | Data product specific type
             facility_name       | text                  |           |          |         | extended |             |              | The name of the facility used for the observation
             calib_level         | smallint              |           |          |         | plain    |             |              | Calibration level {0, 1, 2, 3, 4}
             target_name         | text                  |           |          |         | extended |             |              | Object of interest
             obs_id              | text                  |           |          |         | extended |             |              | Observation ID
             obs_collection      | text                  |           |          |         | extended |             |              | Name of the data collection
             obs_publisher_did   | text                  |           |          |         | extended |             |              | Dataset identifier given by the publisher
             access_url          | text                  |           |          |         | extended |             |              | URL used to access (download) dataset
             access_format       | text                  |           |          |         | extended |             |              | File content format
             s_resolution        | double precision      |           |          |         | plain    |             |              | Spatial resolution of data as FWHM (arcsec)
             s_xel1              | integer               |           |          |         | plain    |             |              | Number of elements along the first spatial axis
             s_xel2              | integer               |           |          |         | plain    |             |              | Number of elements along the second spatial axis
             t_xel               | integer               |           |          |         | plain    |             |              | Number of elements along the time axis
             t_min               | double precision      |           |          |         | plain    |             |              | Start time in MJD
             t_max               | double precision      |           |          |         | plain    |             |              | Stop time in MJD
             t_exptime           | double precision      |           |          |         | plain    |             |              | Total exposure time (sec)
             t_resolution        | double precision      |           |          |         | plain    |             |              | Temporal resolution (sec)
             em_xel              | integer               |           |          |         | plain    |             |              | Number of elements along the spectral axis
             em_min              | double precision      |           |          |         | plain    |             |              | Start in spectral coordinates (m)
             em_max              | double precision      |           |          |         | plain    |             |              | Stop in spectral coordinates (m)
             em_res_power        | double precision      |           |          |         | plain    |             |              | Spectral resolving power
             em_filter_name      | text                  |           |          |         | extended |             |              | Filter name (non-standard column)
             o_ucd               | text                  |           |          |         | extended |             |              | UCD of observable
             pol_xel             | integer               |           |          |         | plain    |             |              | Number of polarization samples
             instrument_name     | text                  |           |          |         | extended |             |              | Name of the instrument used for this observation
             lsst_visit          | bigint                |           |          |         | plain    |             |              | Visit ID
             lsst_exposure       | bigint                |           |          |         | plain    |             |              | Exposure ID
             lsst_detector       | bigint                |           |          |         | plain    |             |              | Detector ID
             lsst_tract          | bigint                |           |          |         | plain    |             |              | Tract ID
             lsst_patch          | bigint                |           |          |         | plain    |             |              | Patch ID
             lsst_band           | character varying(32) |           |          |         | extended |             |              | Spectral band name
             lsst_filter         | character varying(32) |           |          |         | extended |             |              | Physical filter name
             lsst_dataset_type   | text                  |           |          |         | extended |             |              | Dataset type name
             lsst_run            | text                  |           |          |         | extended |             |              | Run name
             registry_dataset_id | uuid                  |           | not null |         | plain    |             |              | Registry dataset ID
             s_ra                | double precision      |           |          |         | plain    |             |              | Central right ascension, ICRS (deg)
             s_dec               | double precision      |           |          |         | plain    |             |              | Central declination, ICRS (deg)
             s_fov               | double precision      |           |          |         | plain    |             |              | Diameter (bounds) of the covered region (deg)
             s_region            | text                  |           |          |         | extended |             |              | Sky region covered by the data product (expressed in ICRS frame)
             pgsphere_region     | spoly                 |           |          |         | external |             |              | pgSphere polygon for this record region.
             pgsphere_position   | spoint                |           |          |         | plain    |             |              | pgSphere position for this record, center of bounding circle.
            Indexes:
                "obscore_pkey" PRIMARY KEY, btree (registry_dataset_id)
                "obscore_idx_dataproduct_type_dataproduct_subtype" btree (dataproduct_type, dataproduct_subtype)
                "obscore_idx_lsst_exposure" btree (lsst_exposure)
                "obscore_idx_lsst_visit" btree (lsst_visit)
                "obscore_idx_pgsphere_position" gist (pgsphere_position)
                "obscore_idx_pgsphere_region" gist (pgsphere_region)
            Foreign-key constraints:
                "fkey_obscore_dataset_id_registry_dataset_id" FOREIGN KEY (registry_dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
            

            Show
            salnikov Andy Salnikov added a comment - Some progress with pgSphere, and there is now a general plugin framework to support extensible way to add more columns. I decided not to implement PostGIS plugin for now, so there is only a pgSphere currently. The schema with these new columns looks like: test_postgis=> \d+ obscore; Table "public.obscore" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+------------------------------------------------------------------ dataproduct_type | text | | | | extended | | | Logical data product type dataproduct_subtype | text | | | | extended | | | Data product specific type facility_name | text | | | | extended | | | The name of the facility used for the observation calib_level | smallint | | | | plain | | | Calibration level {0, 1, 2, 3, 4} target_name | text | | | | extended | | | Object of interest obs_id | text | | | | extended | | | Observation ID obs_collection | text | | | | extended | | | Name of the data collection obs_publisher_did | text | | | | extended | | | Dataset identifier given by the publisher access_url | text | | | | extended | | | URL used to access (download) dataset access_format | text | | | | extended | | | File content format s_resolution | double precision | | | | plain | | | Spatial resolution of data as FWHM (arcsec) s_xel1 | integer | | | | plain | | | Number of elements along the first spatial axis s_xel2 | integer | | | | plain | | | Number of elements along the second spatial axis t_xel | integer | | | | plain | | | Number of elements along the time axis t_min | double precision | | | | plain | | | Start time in MJD t_max | double precision | | | | plain | | | Stop time in MJD t_exptime | double precision | | | | plain | | | Total exposure time (sec) t_resolution | double precision | | | | plain | | | Temporal resolution (sec) em_xel | integer | | | | plain | | | Number of elements along the spectral axis em_min | double precision | | | | plain | | | Start in spectral coordinates (m) em_max | double precision | | | | plain | | | Stop in spectral coordinates (m) em_res_power | double precision | | | | plain | | | Spectral resolving power em_filter_name | text | | | | extended | | | Filter name (non-standard column) o_ucd | text | | | | extended | | | UCD of observable pol_xel | integer | | | | plain | | | Number of polarization samples instrument_name | text | | | | extended | | | Name of the instrument used for this observation lsst_visit | bigint | | | | plain | | | Visit ID lsst_exposure | bigint | | | | plain | | | Exposure ID lsst_detector | bigint | | | | plain | | | Detector ID lsst_tract | bigint | | | | plain | | | Tract ID lsst_patch | bigint | | | | plain | | | Patch ID lsst_band | character varying(32) | | | | extended | | | Spectral band name lsst_filter | character varying(32) | | | | extended | | | Physical filter name lsst_dataset_type | text | | | | extended | | | Dataset type name lsst_run | text | | | | extended | | | Run name registry_dataset_id | uuid | | not null | | plain | | | Registry dataset ID s_ra | double precision | | | | plain | | | Central right ascension, ICRS (deg) s_dec | double precision | | | | plain | | | Central declination, ICRS (deg) s_fov | double precision | | | | plain | | | Diameter (bounds) of the covered region (deg) s_region | text | | | | extended | | | Sky region covered by the data product (expressed in ICRS frame) pgsphere_region | spoly | | | | external | | | pgSphere polygon for this record region. pgsphere_position | spoint | | | | plain | | | pgSphere position for this record, center of bounding circle. Indexes: "obscore_pkey" PRIMARY KEY, btree (registry_dataset_id) "obscore_idx_dataproduct_type_dataproduct_subtype" btree (dataproduct_type, dataproduct_subtype) "obscore_idx_lsst_exposure" btree (lsst_exposure) "obscore_idx_lsst_visit" btree (lsst_visit) "obscore_idx_pgsphere_position" gist (pgsphere_position) "obscore_idx_pgsphere_region" gist (pgsphere_region) Foreign-key constraints: "fkey_obscore_dataset_id_registry_dataset_id" FOREIGN KEY (registry_dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
            Hide
            salnikov Andy Salnikov added a comment -

            Jim Bosch, I think this is ready for review. I have added unit tests for pgsphere and also added pgsphere extension to github actions, so we should notice if something breaks.

            Show
            salnikov Andy Salnikov added a comment - Jim Bosch , I think this is ready for review. I have added unit tests for pgsphere and also added pgsphere extension to github actions, so we should notice if something breaks.
            salnikov Andy Salnikov made changes -
            Reviewers Jim Bosch [ jbosch ]
            Status In Progress [ 3 ] In Review [ 10004 ]
            Hide
            jbosch Jim Bosch added a comment -

            Looks good, sorry it took me so long.  Left a few minor comments on the daf_butler PR.

            Show
            jbosch Jim Bosch added a comment - Looks good, sorry it took me so long.  Left a few minor comments on the daf_butler PR.
            jbosch Jim Bosch made changes -
            Status In Review [ 10004 ] Reviewed [ 10101 ]
            salnikov Andy Salnikov made changes -
            Resolution Done [ 10000 ]
            Status Reviewed [ 10101 ] Done [ 10002 ]
            salnikov Andy Salnikov made changes -
            Epic Link DM-30629 [ 513192 ] PREOPS-1593 [ 2393150 ]
            Sprint DB_F22_6 [ 1172 ]
            Team Data Access and Database [ 10204 ] Ops Middleware [ 15600 ]
            gpdf Gregory Dubois-Felsmann made changes -
            Link This issue relates to DM-37275 [ DM-37275 ]

              People

              Assignee:
              salnikov Andy Salnikov
              Reporter:
              salnikov Andy Salnikov
              Reviewers:
              Jim Bosch
              Watchers:
              Andy Salnikov, Jim Bosch
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Jenkins

                  No builds found.