How to store horizon points in the database

[nicholas512]

the key problem is that horizon points are tuples (x,y) but an observation is a singleton (except for depth max/min info that can be added).

Suggested solutions are below (# 3 is my favorite).

suggested solution 1

create new locations for horizon measurements that are lines instead of points, the azimuth of the line then corresponds to the azimuth of the horizon

advantages: not many disadvantages: separates observations into many different locations, when they represent the same location. Convention is somewhat ad-hoc; would require good documentation. Creates many locations that are only used for one purpose, stores information about the observation (i.e. the azimuth) in the location description

suggested solution 2

implement an ad-hoc convention: e.g.

• by convention, the 'text_value' is azimuth and 'numeric_value' is horizon
  • by convention, the 'height_max_metres' is horizon and 'numeric_value' is azimuth
  • store as a text value e.g. "000-00" (azimuth-horizon)

advantages: data can be stored easily, all observation data is kept in the observations table disadvantages: does not fit into conceptual schema. Would need to be documented and followed.

suggested solution 3:

discretize azimuthal directions into different sensors e.g. {"device_id":"horizon_at_000", ..., }, {"device_id":"horizon_at_005", ..., }, …, {"device_id":"horizon_at_355", ..., } All horizon sensors could be grouped as a single device (e.g. horizon camera/quickhorizon) (instead of human observation)

advantages: fits well into conceptual schema disadvantages: restricts the data that can be entered. Creates a lot of sensors that are basically the same, stores information about the observation (i.e. the azimuth) in the sensor description

[nicholas512]

Option 3 has been unanimously (n=1) chosen as the preferred solution.

For each direction from 000 to 359:

INSERT INTO sensors(device_id, 
                    label, 
                    type_of_measurement,
                    unit_of_measurement,
                    accuracy,
                    precision,
                    height_in_metres)                
     VALUES ('5265f740-8e09-4d41-b5b6-2136f5d35ea3',
             'horizon_angle_at_000_true',
             'numeric',
             'degrees',
             1,
             1,
             0);

If done after migration to devices_sensors link table then

INSERT INTO sensors(device_id, 
                    label, 
                    type_of_measurement,
                    unit_of_measurement,
                    accuracy,
                    precision,
                    height_in_metres)                
     VALUES ('5265f740-8e09-4d41-b5b6-2136f5d35ea3',
             'horizon_angle_at_000_true',
             'numeric',
             'degrees',
             1,
             1,
             0)
     RETURNING id as new_id

INSERT INTO devices_sensors (timestamp,
                             device_id,
                             sensor_id)                
     VALUES (make_timestamptz(1900, 1, 1, 00, 00, 00, 'UTC')
             '5265f740-8e09-4d41-b5b6-2136f5d35ea3',
             new_id);

[nicholas512]

RE: using a 'horizon cam' device, this might complicate things, but could be added later and could also have the device_type of 'human observation' (so we would have multiple devices with human observation as their type). Queries for human observation would have to be by device_type and not by id

 serial_number = null      
access_code  = null
device_type = human observation 
manufacturer = 
manufacturer_device_name = horizon camera? quickhorizon?
acquired_on = whenever it was acquired
notes  = actually have some details here 

[EmilieStewartJones]

BC16-RV13_HOR_5211.txt This horizon file is for site BC16-RV13_03