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