Implement spatial indexing for live obscore table.

XMLWordPrintable

Details

• Type: Improvement
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
• Story Points:
10
• Team:
Ops Middleware

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).

Activity

Hide
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
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
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
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.
Hide
Jim Bosch added a comment -

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

Show
Jim Bosch added a comment - Looks good, sorry it took me so long.  Left a few minor comments on the daf_butler PR.

People

Assignee:
Andy Salnikov
Reporter:
Andy Salnikov
Reviewers:
Jim Bosch
Watchers:
Andy Salnikov, Jim Bosch