def flush_triangulation(utc_min, utc_max):
"""
Remove all pre-existing triangulation metadata.
:param utc_min:
The earliest time for which we are to flush observation groups.
:param utc_max:
The latest time for which we are to flush observation groups.
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Delete group metadata fields that start 'triangulation:*'
conn.execute(
"""
DELETE m
FROM archive_metadata m
INNER JOIN archive_obs_groups o ON m.groupId = o.uid
WHERE
fieldId IN (SELECT uid FROM archive_metadataFields WHERE metaKey LIKE 'triangulation:%%') AND
o.time BETWEEN %s AND %s;
""", (utc_min, utc_max))
# Commit changes to database
db0.commit()
conn.close()
db0.close()
def flush_identifications(utc_min, utc_max):
"""
Remove all meteor identifications within a specified time period.
:param utc_min:
The earliest time for which we are to flush shower data.
:param utc_max:
The latest time for which we are to flush shower data.
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Delete observation metadata fields that start 'shower:*'
conn.execute("""
DELETE m
FROM archive_metadata m
INNER JOIN archive_observations o ON m.observationId = o.uid
WHERE
fieldId IN (SELECT uid FROM archive_metadataFields WHERE metaKey LIKE 'shower:%%') AND
o.obsTime BETWEEN %s AND %s;
""", (utc_min, utc_max))
# Commit changes to database
db0.commit()
conn.close()
db0.close()
def flush_calibration(obstory_id, utc_min, utc_max):
"""
Remove all calibration data for a particular observatory within a specified time period.
:param obstory_id:
The publicId of the observatory we are to flush.
:param utc_min:
The earliest time for which we are to flush calibration data.
:param utc_max:
The latest time for which we are to flush calibration data.
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Delete observatory metadata fields that start 'calibration:*'
conn.execute(
"""
DELETE m
FROM archive_metadata m
WHERE
fieldId IN (SELECT uid FROM archive_metadataFields WHERE metaKey LIKE 'calibration:%%') AND
m.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
m.time BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
# Delete observation metadata fields that start 'calibration:*'
conn.execute(
"""
DELETE m
FROM archive_metadata m
INNER JOIN archive_observations o ON m.observationId = o.uid
WHERE
fieldId IN (SELECT uid FROM archive_metadataFields WHERE metaKey LIKE 'calibration:%%') AND
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
# Clear astrometryProcessed fields
conn.execute(
"""
UPDATE archive_observations
SET astrometryProcessed=NULL, astrometryProcessingTime=NULL, astrometrySource=NULL,
fieldWidth=NULL, fieldHeight=NULL, positionAngle=NULL, centralConstellation=NULL,
skyArea=ST_GEOMFROMTEXT(%s), position=POINT(-999,-999)
WHERE observatory = (SELECT x.uid FROM archive_observatories x WHERE x.publicId=%s) AND
obsTime BETWEEN %s AND %s;
""", (obsarchive_sky_area.null_polygon, obstory_id, utc_min, utc_max))
# Commit changes to database
db0.commit()
conn.close()
db0.close()
def edit_observatory(public_id, delete=False, name=None, latitude=None, longitude=None, owner=None):
"""
Edit an observatory record, or create a new one.
:param public_id:
The public id of the observatory we are to edit
:param delete:
Boolean flag indicating whether we are to delete the observatory altogether
:param name:
The new name of the observatory
:param latitude:
The new latitude of the observatory
:param longitude:
The new longitude of the observatory
:param owner:
The user responsible for this observatory
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Fetch observatory ID
obs_id = None
while True:
conn.execute('SELECT uid FROM archive_observatories WHERE publicId=%s;', (public_id,))
results = conn.fetchall()
if len(results) > 0:
obs_id = results[0]['uid']
break
conn.execute('INSERT INTO archive_observatories (publicId, location) VALUES (%s, POINT(-999,-999));',
(public_id,))
if delete:
conn.execute('DELETE FROM archive_observatories WHERE uid=%s;', (obs_id,))
db0.commit()
db0.close()
return
if name:
conn.execute('UPDATE archive_observatories SET name=%s WHERE uid=%s;', (name, obs_id))
if latitude and longitude:
conn.execute('UPDATE archive_observatories SET location=POINT(%s,%s) WHERE uid=%s;',
(longitude, latitude, obs_id))
if owner:
conn.execute('UPDATE archive_observatories SET userId=%s WHERE uid=%s;', (owner, obs_id))
# Commit changes to database
db0.commit()
db0.close()
def flush_orientation(obstory_id, utc_min, utc_max):
"""
Remove all orientation data for a particular observatory within a specified time period.
:param obstory_id:
The publicId of the observatory we are to flush.
:param utc_min:
The earliest time for which we are to flush orientation data.
:param utc_max:
The latest time for which we are to flush orientation data.
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Delete observatory metadata fields that start 'orientation:*'
conn.execute(
"""
DELETE m
FROM archive_metadata m
WHERE
fieldId IN (SELECT uid FROM archive_metadataFields WHERE metaKey LIKE 'orientation:%%') AND
m.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
m.time BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
# Delete observation metadata field 'orientation:fit_quality_to_daily'
conn.execute(
"""
DELETE m
FROM archive_metadata m
INNER JOIN archive_observations o ON m.observationId = o.uid
WHERE
fieldId IN (SELECT uid FROM archive_metadataFields WHERE metaKey LIKE 'orientation:fit_quality_to_daily') AND
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
# Commit changes to database
db0.commit()
conn.close()
db0.close()
def list_observatories():
"""
Display a list of all the observatories registered in the database.
:return:
None
"""
# Open connection to image archive
db = obsarchive_db.ObservationDatabase(
file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
# Fetch observatory IDs
obstory_ids = db.get_obstory_ids()
obstory_ids.sort()
# Open connection to database
[db0, conn] = connect_db.connect_db()
# List information about each observatory
print("{:6s} {:32s} {:32s} {:6s} {:6s} {:s}".format(
"ObsID", "Public ID", "Name", "Lat", "Lng", "Observations"))
for item in obstory_ids:
obstory_info = db.get_obstory_from_id(obstory_id=item)
# Count observations
conn.execute(
'SELECT COUNT(*) FROM archive_observations WHERE observatory=%s;',
(obstory_info['uid'], ))
results = conn.fetchall()
obs_count = results[0]["COUNT(*)"]
print("{:6d} {:32s} {:32s} {:6.1f} {:6.1f} {:7d}".format(
obstory_info['uid'], obstory_info['publicId'],
obstory_info['name'], obstory_info['latitude'],
obstory_info['longitude'], obs_count))
def list_users():
"""
Display a list of all user accounts in the database.
:return:
None
"""
# Open connection to image archive
db = obsarchive_db.ObservationDatabase(
file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
# Fetch list of users
user_ids = db.get_users()
user_ids.sort(key=operator.attrgetter('user_id'))
# Open connection to database
[db0, conn] = connect_db.connect_db()
# List information about each user in turn
print("{:32s} {:32s} {:48s} {:s}".format("Username", "Name", "Roles",
"Observations"))
for user_info in user_ids:
# Count observations
conn.execute(
'SELECT COUNT(*) FROM archive_observations WHERE userId=%s;',
(user_info.user_id, ))
results = conn.fetchall()
obs_count = results[0]["COUNT(*)"]
# Print user information
print("{:32s} {:32s} {:48s} {:9d}".format(user_info.user_id,
user_info.name,
str(user_info.roles),
obs_count))
def delete_data(utc_min, utc_max, obstory, dry_run):
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Search for observations
conn.execute(
"""
SELECT o.publicId
FROM archive_observations o
INNER JOIN archive_observatories ao on o.observatory = ao.uid
WHERE (o.obsTime BETWEEN %s AND %s) AND ao.publicId=%s;
""", (utc_min, utc_max, obstory))
results_observations = conn.fetchall()
# Delete each observation in turn
for observation in results_observations:
command = """
./deleteObservation.py --id {}
""".format(observation['publicId']).strip()
logging.info(command)
if not dry_run:
os.system(command)
def list_simultaneous_detections(utc_min=None, utc_max=None):
"""
Display a list of all the simultaneous object detections registered in the database.
:param utc_min:
Only show observations made after the specified time stamp.
:type utc_min:
float
:param utc_max:
Only show observations made before the specified time stamp.
:type utc_max:
float
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Compile search criteria for observation groups
where = [
"g.semanticType = (SELECT uid FROM archive_semanticTypes WHERE name=\"{}\")"
.format(simultaneous_event_type)
]
args = []
if utc_min is not None:
where.append("o.obsTime>=%s")
args.append(utc_min)
if utc_max is not None:
where.append("o.obsTime<=%s")
args.append(utc_max)
# Search for observation groups containing groups of simultaneous detections
conn.execute(
"""
SELECT g.publicId AS groupId, o.publicId AS obsId, o.obsTime, am.stringValue AS objectType
FROM archive_obs_groups g
INNER JOIN archive_obs_group_members m on g.uid = m.groupId
INNER JOIN archive_observations o ON m.childObservation = o.uid
INNER JOIN archive_metadata am ON g.uid = am.groupId AND
am.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="web:category")
WHERE """ + " AND ".join(where) + """
ORDER BY o.obsTime;
""", args)
results = conn.fetchall()
# Count how many simultaneous detections we find by type
detections_by_type = {}
# Compile list of groups
obs_groups = {}
obs_group_ids = []
for item in results:
key = item['groupId']
if key not in obs_groups:
obs_groups[key] = []
obs_group_ids.append({
'groupId': key,
'time': item['obsTime'],
'type': item['objectType']
})
# Add this simultaneous detection to tally
if item['objectType'] not in detections_by_type:
detections_by_type[item['objectType']] = 0
detections_by_type[item['objectType']] += 1
obs_groups[key].append(item['obsId'])
# List information about each observation in turn
print("{:16s} {:20s} {:20s} {:s}".format("Time", "groupId", "Object type",
"Observations"))
for group_info in obs_group_ids:
# Print group information
print("{:16s} {:20s} {:20s} {:s}".format(
group_info['groupId'], date_string(group_info['time']),
group_info['type'], " ".join(obs_groups[group_info['groupId']])))
# Report tally of events
print("\nTally of events by type:")
for event_type in sorted(detections_by_type.keys()):
print(" * {:26s}: {:6d}".format(event_type,
detections_by_type[event_type]))
def list_images(utc_min=None,
utc_max=None,
username=None,
obstory=None,
img_type=None,
obs_type=None,
stride=1):
"""
Display a list of all the images registered in the database.
:param utc_min:
Only show observations made after the specified time stamp.
:type utc_min:
float
:param utc_max:
Only show observations made before the specified time stamp.
:type utc_max:
float
:param username:
Optionally specify a username, to filter only images by a particular user
:type username:
str
:param obstory:
The public id of the observatory we are to show observations from
:type obstory:
str
:param img_type:
Only show images with this semantic type
:type img_type:
str
:param obs_type:
Only show observations with this semantic type
:type obs_type:
str
:param stride:
Only show every nth observation matching the search criteria
:type stride:
int
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
where = ["1"]
args = []
if utc_min is not None:
where.append("o.obsTime>=%s")
args.append(utc_min)
if utc_max is not None:
where.append("o.obsTime<=%s")
args.append(utc_max)
if username is not None:
where.append("o.userId=%s")
args.append(username)
if obstory is not None:
where.append("l.publicId=%s")
args.append(obstory)
if obs_type is not None:
where.append("ast.name=%s")
args.append(obs_type)
conn.execute(
"""
SELECT o.uid, o.userId, l.name AS place, o.obsTime
FROM archive_observations o
INNER JOIN archive_observatories l ON o.observatory = l.uid
INNER JOIN archive_semanticTypes ast ON o.obsType = ast.uid
WHERE """ + " AND ".join(where) + """
ORDER BY obsTime DESC LIMIT 200;
""", args)
results = conn.fetchall()
# List information about each observation in turn
sys.stdout.write("{:6s} {:10s} {:32s} {:17s} {:20s}\n".format(
"obsId", "Username", "Observatory", "Time", "Images"))
for counter, obs in enumerate(results):
# Only show every nth hit
if counter % stride != 0:
continue
# Print observation information
sys.stdout.write("{:6d} {:10s} {:32s} {:17s} ".format(
obs['uid'], obs['userId'], obs['place'],
date_string(obs['obsTime'])))
where = ["f.observationId=%s"]
args = [obs['uid']]
if img_type is not None:
where.append("ast.name=%s")
args.append(img_type)
# Fetch list of files in this observation
conn.execute(
"""
SELECT ast.name AS semanticType, repositoryFname, am.floatValue AS skyClarity
FROM archive_files f
INNER JOIN archive_semanticTypes ast ON f.semanticType = ast.uid
LEFT OUTER JOIN archive_metadata am ON f.uid = am.fileId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
WHERE """ + " AND ".join(where) + """;
""", args)
files = conn.fetchall()
for count, item in enumerate(files):
if count > 0:
sys.stdout.write("\n{:69s}".format(""))
if item['skyClarity'] is None:
item['skyClarity'] = 0
sys.stdout.write("{:40s} {:32s} {:10.1f}".format(
item['semanticType'], item['repositoryFname'],
item['skyClarity']))
sys.stdout.write("\n")
def calibrate_lens(obstory_id, utc_min, utc_max, utc_must_stop=None):
"""
Use astrometry.net to determine the orientation of a particular observatory.
:param obstory_id:
The ID of the observatory we want to determine the orientation for.
:param utc_min:
The start of the time period in which we should determine the observatory's orientation.
:param utc_max:
The end of the time period in which we should determine the observatory's orientation.
:param utc_must_stop:
The time by which we must finish work
:return:
None
"""
global parameter_scales, fit_list
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Open connection to image archive
db = obsarchive_db.ObservationDatabase(
file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
logging.info(
"Starting estimation of lens calibration for <{}>".format(obstory_id))
# Mathematical constants
deg = pi / 180
rad = 180 / pi
# Count how many successful fits we achieve
successful_fits = 0
# Read properties of known lenses
hw = hardware_properties.HardwareProps(
path=os.path.join(settings['pythonPath'], "..", "configuration_global",
"camera_properties"))
# Reduce time window to where observations are present
conn.execute(
"""
SELECT obsTime
FROM archive_observations
WHERE obsTime BETWEEN %s AND %s
AND observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
ORDER BY obsTime ASC LIMIT 1
""", (utc_min, utc_max, obstory_id))
results = conn.fetchall()
if len(results) == 0:
logging.warning("No observations within requested time window.")
return
utc_min = results[0]['obsTime'] - 1
conn.execute(
"""
SELECT obsTime
FROM archive_observations
WHERE obsTime BETWEEN %s AND %s
AND observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
ORDER BY obsTime DESC LIMIT 1
""", (utc_min, utc_max, obstory_id))
results = conn.fetchall()
utc_max = results[0]['obsTime'] + 1
# Divide up time interval into day-long blocks
logging.info("Searching for images within period {} to {}".format(
date_string(utc_min), date_string(utc_max)))
block_size = 3600
minimum_sky_clarity = 1e6 + 1400
utc_min = (floor(utc_min / block_size + 0.5) -
0.5) * block_size # Make sure that blocks start at noon
time_blocks = list(
np.arange(start=utc_min, stop=utc_max + block_size, step=block_size))
# Start new block whenever we have a hardware refresh
conn.execute(
"""
SELECT time FROM archive_metadata
WHERE observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
AND fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey='refresh')
AND time BETWEEN %s AND %s
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
time_blocks.append(item['time'])
# Make sure that start points for time blocks are in order
time_blocks.sort()
# Build list of images we are to analyse
images_for_analysis = []
for block_index, utc_block_min in enumerate(time_blocks[:-1]):
utc_block_max = time_blocks[block_index + 1]
logging.info("Calibrating lens within period {} to {}".format(
date_string(utc_block_min), date_string(utc_block_max)))
# Search for background-subtracted time lapse image with best sky clarity within this time period
conn.execute(
"""
SELECT ao.obsTime, ao.publicId AS observationId, f.repositoryFname, am.floatValue AS skyClarity
FROM archive_files f
INNER JOIN archive_observations ao on f.observationId = ao.uid
INNER JOIN archive_metadata am ON f.uid = am.fileId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
LEFT OUTER JOIN archive_metadata am2 ON f.uid = am2.fileId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:lens_barrel_parameters")
WHERE ao.obsTime BETWEEN %s AND %s
AND ao.observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
AND f.semanticType=(SELECT uid FROM archive_semanticTypes WHERE name="pigazing:timelapse/backgroundSubtracted")
AND am.floatValue > %s
AND am2.uid IS NULL
AND ao.astrometryProcessed IS NULL
ORDER BY am.floatValue DESC LIMIT 1
""", (utc_block_min, utc_block_max, obstory_id, minimum_sky_clarity))
results = conn.fetchall()
if len(results) > 0:
images_for_analysis.append({
'utc':
results[0]['obsTime'],
'skyClarity':
results[0]['skyClarity'],
'repositoryFname':
results[0]['repositoryFname'],
'observationId':
results[0]['observationId']
})
# Sort images into order of sky clarity
images_for_analysis.sort(key=itemgetter("skyClarity"))
images_for_analysis.reverse()
# Display logging list of the images we are going to work on
logging.info("Estimating the calibration of {:d} images:".format(
len(images_for_analysis)))
for item in images_for_analysis:
logging.info("{:17s} {:04.0f} {:32s}".format(date_string(item['utc']),
item['skyClarity'],
item['repositoryFname']))
# Analyse each image in turn
for item_index, item in enumerate(images_for_analysis):
logging.info("Working on image {:32s} ({:4d}/{:4d})".format(
item['repositoryFname'], item_index + 1, len(images_for_analysis)))
# Make a temporary directory to store files in.
# This is necessary as astrometry.net spams the cwd with lots of temporary junk
tmp0 = "/tmp/dcf21_calibrateLens_{}".format(item['repositoryFname'])
# logging.info("Created temporary directory <{}>".format(tmp))
os.system("mkdir {}".format(tmp0))
# Fetch observatory status
obstory_info = db.get_obstory_from_id(obstory_id)
obstory_status = None
if obstory_info and ('name' in obstory_info):
obstory_status = db.get_obstory_status(obstory_id=obstory_id,
time=item['utc'])
if not obstory_status:
logging.info("Aborting -- no observatory status available.")
continue
# Fetch observatory status
lens_name = obstory_status['lens']
lens_props = hw.lens_data[lens_name]
# This is an estimate of the *maximum* angular width we expect images to have.
# It should be within a factor of two of correct!
estimated_image_scale = lens_props.fov
# Find image orientation orientation
filename = os.path.join(settings['dbFilestore'],
item['repositoryFname'])
if not os.path.exists(filename):
logging.info("Error: File <{}> is missing!".format(
item['repositoryFname']))
continue
# 1. Copy image into working directory
# logging.info("Copying file")
img_name = item['repositoryFname']
command = "cp {} {}/{}_tmp.png".format(filename, tmp0, img_name)
# logging.info(command)
os.system(command)
# 2. We estimate the distortion of the image by passing a series of small portions of the image to
# astrometry.net. We use this to construct a mapping between (x, y) pixel coordinates to (RA, Dec).
# Define the size of each small portion we pass to astrometry.net
fraction_x = 0.15
fraction_y = 0.15
# Create a list of the centres of the portions we send
fit_list = []
portion_centres = [{'x': 0.5, 'y': 0.5}]
# Points along the leading diagonal of the image
for z in np.arange(0.1, 0.9, 0.1):
if z != 0.5:
portion_centres.append({'x': z, 'y': z})
portion_centres.append({'x': (z + 0.5) / 2, 'y': z})
portion_centres.append({'x': z, 'y': (z + 0.5) / 2})
# Points along the trailing diagonal of the image
for z in np.arange(0.1, 0.9, 0.1):
if z != 0.5:
portion_centres.append({'x': z, 'y': 1 - z})
portion_centres.append({'x': (1.5 - z) / 2, 'y': z})
portion_centres.append({'x': z, 'y': (1.5 - z) / 2})
# Points down the vertical centre-line of the image
for z in np.arange(0.15, 0.85, 0.1):
portion_centres.append({'x': 0.5, 'y': z})
# Points along the horizontal centre-line of the image
for z in np.arange(0.15, 0.85, 0.1):
portion_centres.append({'x': z, 'y': 0.5})
# Fetch the pixel dimensions of the image we are working on
d = image_dimensions("{}/{}_tmp.png".format(tmp0, img_name))
@dask.delayed
def analyse_image_portion(image_portion):
# Make a temporary directory to store files in.
# This is necessary as astrometry.net spams the cwd with lots of temporary junk
tmp = "/tmp/dcf21_calibrateLens_{}_{}".format(
item['repositoryFname'], image_portion['index'])
# logging.info("Created temporary directory <{}>".format(tmp))
os.system("mkdir {}".format(tmp))
# Use ImageMagick to crop out each small piece of the image
command = """
cd {6} ; \
rm -f {5}_tmp3.png ; \
convert {0}_tmp.png -colorspace sRGB -define png:format=png24 -crop {1:d}x{2:d}+{3:d}+{4:d} +repage {5}_tmp3.png
""".format(os.path.join(tmp0, img_name), int(fraction_x * d[0]),
int(fraction_y * d[1]),
int((image_portion['x'] - fraction_x / 2) * d[0]),
int((image_portion['y'] - fraction_y / 2) * d[1]),
img_name, tmp)
# logging.info(command)
os.system(command)
# Check that we've not run out of time
if utc_must_stop and (time.time() > utc_must_stop):
logging.info("We have run out of time! Aborting.")
os.system("rm -Rf {}".format(tmp))
return None
# How long should we allow astrometry.net to run for?
timeout = "40s"
# Run astrometry.net. Insert --no-plots on the command line to speed things up.
# logging.info("Running astrometry.net")
estimated_width = 2 * math.atan(
math.tan(estimated_image_scale / 2 * deg) * fraction_x) * rad
astrometry_output = os.path.join(tmp, "txt")
command = """
cd {5} ; \
timeout {0} solve-field --no-plots --crpix-center --scale-low {1:.1f} \
--scale-high {2:.1f} --overwrite {3}_tmp3.png > {4} 2> /dev/null \
""".format(timeout, estimated_width * 0.6, estimated_width * 1.2,
img_name, astrometry_output, tmp)
# logging.info(command)
os.system(command)
# Parse the output from astrometry.net
assert os.path.exists(
astrometry_output), "Path <{}> doesn't exist".format(
astrometry_output)
fit_text = open(astrometry_output).read()
# logging.info(fit_text)
# Clean up
# logging.info("Removing temporary directory <{}>".format(tmp))
os.system("rm -Rf {}".format(tmp))
# Extract celestial coordinates of the centre of the frame from astrometry.net output
test = re.search(
r"\(RA H:M:S, Dec D:M:S\) = \(([\d-]*):(\d\d):([\d.]*), [+]?([\d-]*):(\d\d):([\d\.]*)\)",
fit_text)
if not test:
logging.info("FAIL(POS): Point ({:.2f},{:.2f}).".format(
image_portion['x'], image_portion['y']))
return None
ra_sign = sgn(float(test.group(1)))
ra = abs(float(test.group(1))) + float(test.group(2)) / 60 + float(
test.group(3)) / 3600
if ra_sign < 0:
ra *= -1
dec_sign = sgn(float(test.group(4)))
dec = abs(float(test.group(4))) + float(
test.group(5)) / 60 + float(test.group(6)) / 3600
if dec_sign < 0:
dec *= -1
# If astrometry.net achieved a fit, then we report it to the user
logging.info(
"FIT: RA: {:7.2f}h. Dec {:7.2f} deg. Point ({:.2f},{:.2f}).".
format(ra, dec, image_portion['x'], image_portion['y']))
# Also, populate <fit_list> with a list of the central points of the image fragments, and their (RA, Dec)
# coordinates.
return {
'ra': ra * pi / 12,
'dec': dec * pi / 180,
'x': image_portion['x'],
'y': image_portion['y'],
'radius': hypot(image_portion['x'] - 0.5,
image_portion['y'] - 0.5)
}
# Analyse each small portion of the image in turn
dask_tasks = []
for index, image_portion in enumerate(portion_centres):
image_portion['index'] = index
dask_tasks.append(
analyse_image_portion(image_portion=image_portion))
fit_list = dask.compute(*dask_tasks)
# Remove fits which returned None
fit_list = [i for i in fit_list if i is not None]
# Clean up
os.system("rm -Rf {}".format(tmp0))
os.system("rm -Rf /tmp/tmp.*")
# Make histogram of fits as a function of radius
radius_histogram = [0] * 10
for fit in fit_list:
radius_histogram[int(fit['radius'] * 10)] += 1
logging.info("Fit histogram vs radius: {}".format(radius_histogram))
# Reject this image if there are insufficient fits from astrometry.net
if min(radius_histogram[:5]) < 2:
logging.info("Insufficient fits to continue")
continue
# Fit a gnomonic projection to the image, with barrel correction, to fit all the celestial positions of the
# image fragments.
# See <http://www.scipy-lectures.org/advanced/mathematical_optimization/> for more information
ra0 = fit_list[0]['ra']
dec0 = fit_list[0]['dec']
parameter_scales = [
pi / 4, pi / 4, pi / 4, pi / 4, pi / 4, pi / 4, 5e-2, 5e-6
]
parameters_default = [
ra0, dec0, pi / 4, pi / 4, 0, lens_props.barrel_parameters[2], 0
]
parameters_initial = [
parameters_default[i] / parameter_scales[i]
for i in range(len(parameters_default))
]
fitting_result = scipy.optimize.minimize(mismatch,
parameters_initial,
method='nelder-mead',
options={
'xtol': 1e-8,
'disp': True,
'maxiter': 1e8,
'maxfev': 1e8
})
parameters_optimal = fitting_result.x
parameters_final = [
parameters_optimal[i] * parameter_scales[i]
for i in range(len(parameters_default))
]
# Display best fit numbers
headings = [["Central RA / hr", 12 / pi],
["Central Decl / deg", 180 / pi],
["Image width / deg", 180 / pi],
["Image height / deg", 180 / pi],
["Position angle / deg", 180 / pi], ["barrel_k1", 1],
["barrel_k2", 1]]
logging.info(
"Fit achieved to {:d} points with offset of {:.5f}. Best fit parameters were:"
.format(len(fit_list), fitting_result.fun))
for i in range(len(parameters_default)):
logging.info("{0:30s} : {1}".format(
headings[i][0], parameters_final[i] * headings[i][1]))
# Reject fit if objective function too large
if fitting_result.fun > 1e-4:
logging.info("Rejecting fit as chi-squared too large.")
continue
# Reject fit if k1/k2 values are too extreme
if (abs(parameters_final[5]) > 0.3) or (abs(parameters_final[6]) >
0.1):
logging.info("Rejecting fit as parameters seem extreme.")
continue
# Update observation status
successful_fits += 1
user = settings['pigazingUser']
timestamp = time.time()
barrel_parameters = [
parameters_final[2] * 180 / pi, parameters_final[3] * 180 / pi,
parameters_final[5], parameters_final[6], 0
]
db.set_observation_metadata(
user_id=user,
observation_id=item['observationId'],
utc=timestamp,
meta=mp.Meta(key="calibration:lens_barrel_parameters",
value=json.dumps(barrel_parameters)))
db.set_observation_metadata(user_id=user,
observation_id=item['observationId'],
utc=timestamp,
meta=mp.Meta(key="calibration:chi_squared",
value=fitting_result.fun))
db.set_observation_metadata(user_id=user,
observation_id=item['observationId'],
utc=timestamp,
meta=mp.Meta(key="calibration:point_count",
value=str(radius_histogram)))
# Commit metadata changes
db.commit()
db0.commit()
# Report how many fits we achieved
logging.info(
"Total of {:d} images successfully fitted.".format(successful_fits))
if successful_fits > 0:
# Now determine mean lens calibration each day
logging.info("Averaging daily fits within period {} to {}".format(
date_string(utc_min), date_string(utc_max)))
block_size = 86400
utc_min = (floor(utc_min / block_size + 0.5) -
0.5) * block_size # Make sure that blocks start at noon
time_blocks = list(
np.arange(start=utc_min,
stop=utc_max + block_size,
step=block_size))
# Start new block whenever we have a hardware refresh
conn.execute(
"""
SELECT time FROM archive_metadata
WHERE observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
AND fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey='refresh')
AND time BETWEEN %s AND %s
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
time_blocks.append(item['time'])
# Make sure that start points for time blocks are in order
time_blocks.sort()
for block_index, utc_block_min in enumerate(time_blocks[:-1]):
utc_block_max = time_blocks[block_index + 1]
# Select observations with calibration fits
conn.execute(
"""
SELECT am1.stringValue AS barrel_parameters
FROM archive_observations o
INNER JOIN archive_metadata am1 ON o.uid = am1.observationId AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:lens_barrel_parameters")
WHERE
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_block_min, utc_block_max))
results = conn.fetchall()
logging.info(
"Averaging fits within period {} to {}: Found {} fits.".format(
date_string(utc_block_min), date_string(utc_block_max),
len(results)))
# Average the fits we found
if len(results) < 3:
logging.info("Insufficient images to reliably average.")
continue
# Pick the median fit
value_list = {
'scale_x': [],
'scale_y': [],
'barrel_k1': [],
'barrel_k2': [],
'barrel_k3': []
}
for item in results:
barrel_parameters = json.loads(item['barrel_parameters'])
value_list['scale_x'].append(barrel_parameters[0])
value_list['scale_y'].append(barrel_parameters[1])
value_list['barrel_k1'].append(barrel_parameters[2])
value_list['barrel_k2'].append(barrel_parameters[3])
value_list['barrel_k3'].append(barrel_parameters[4])
median_values = {}
for key, values in value_list.items():
values.sort()
median_values[key] = values[len(values) // 2]
# Print fit information
logging.info("""\
CALIBRATION FIT from {:2d} images: %s. \
""".format(
len(results), "; ".join([
"{}: {}".format(key, median)
for key, median in median_values.items()
])))
# Flush any previous observation status
flush_calibration(obstory_id=obstory_id,
utc_min=utc_block_min - 1,
utc_max=utc_block_min + 1)
# Update observatory status
user = settings['pigazingUser']
timestamp = time.time()
barrel_parameters = [
median_values['scale_x'], median_values['scale_y'],
median_values['barrel_k1'], median_values['barrel_k2'],
median_values['barrel_k3']
]
db.register_obstory_metadata(
obstory_id=obstory_id,
key="calibration:lens_barrel_parameters",
value=json.dumps(barrel_parameters),
time_created=timestamp,
metadata_time=utc_block_min,
user_created=user)
db.commit()
# Clean up and exit
db.commit()
db.close_db()
db0.commit()
conn.close()
db0.close()
return
def orientation_calc(obstory_id, utc_min, utc_max):
"""
Use astrometry.net to determine the orientation of a particular observatory within each night within the time
period between the unix times <utc_min> and <utc_max>.
:param obstory_id:
The ID of the observatory we want to determine the orientation for.
:type obstory_id:
str
:param utc_min:
The start of the time period in which we should determine the observatory's orientation (unix time).
:type utc_min:
float
:param utc_max:
The end of the time period in which we should determine the observatory's orientation (unix time).
:type utc_max:
float
:param utc_must_stop:
The unix time after which we must abort and finish work as quickly as possible.
:type utc_must_stop:
float
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Open connection to image archive
db = obsarchive_db.ObservationDatabase(
file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
logging.info(
"Starting calculation of camera alignment for <{}>".format(obstory_id))
# Reduce time window we are searching to the interval in which observations are present (to save time)
utc_max, utc_min = reduce_time_window(conn=conn,
obstory_id=obstory_id,
utc_max=utc_max,
utc_min=utc_min)
# Try to average the fits within each night to determine the sigma-clipped mean orientation
average_daily_fits(conn=conn,
db=db,
obstory_id=obstory_id,
utc_max=utc_max,
utc_min=utc_min)
measure_fit_quality_to_daily_fits(conn=conn,
db=db,
obstory_id=obstory_id,
utc_max=utc_max,
utc_min=utc_min)
# Clean up and exit
db.commit()
db.close_db()
db0.commit()
conn.close()
db0.close()
return
def plot_orientation(obstory_ids, utc_min, utc_max):
"""
Plot the orientation of a particular observatory within the time period between the unix times
<utc_min> and <utc_max>.
:param obstory_ids:
The IDs of the observatories we want to plot the orientation for.
:type obstory_ids:
list<str>
:param utc_min:
The start of the time period in which we should plot the observatory's orientation (unix time).
:type utc_min:
float
:param utc_max:
The end of the time period in which we should plot the observatory's orientation (unix time).
:type utc_max:
float
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Open connection to image archive
db = obsarchive_db.ObservationDatabase(
file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
logging.info("Plotting camera alignment for <{}>".format(obstory_ids))
# Data filename stem
filename_stem = "/tmp/orientation_plot_{:.1f}".format(utc_min)
# Make data file for each observatory in turn
for counter, obstory_id in enumerate(obstory_ids):
# Search for background-subtracted time lapse image with best sky clarity, and no existing orientation fit,
# within this time period
conn.execute(
"""
SELECT ao.obsTime, ao.publicId AS observationId,
am.floatValue AS skyClarity, am2.stringValue AS fitQuality, am3.stringValue AS fitQualityToAverage
FROM archive_files f
INNER JOIN archive_observations ao on f.observationId = ao.uid
INNER JOIN archive_metadata am ON f.uid = am.fileId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
LEFT OUTER JOIN archive_metadata am2 ON ao.uid = am2.observationId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:fit_quality")
LEFT OUTER JOIN archive_metadata am3 ON ao.uid = am3.observationId AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:fit_quality_to_daily")
WHERE ao.obsTime BETWEEN %s AND %s
AND ao.observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
AND f.semanticType=(SELECT uid FROM archive_semanticTypes WHERE name="pigazing:timelapse/backgroundSubtracted")
ORDER BY ao.obsTime
""", (utc_min, utc_max, obstory_id))
results = conn.fetchall()
# Data filename
filename = "{}_{:02d}".format(filename_stem, counter)
# Loop over results and write to data file
with open("{}.dat".format(filename), "w") as f:
for item in results:
utc = float(item['obsTime'])
sky_clarity = float(item['skyClarity'])
fit_quality = -99
fit_quality_to_average = -99
if item['fitQuality'] is not None:
fit_quality = float(json.loads(item['fitQuality'])[0])
if item['fitQualityToAverage'] is not None:
fit_quality_to_average = float(
json.loads(item['fitQualityToAverage'])[0])
f.write("{:.1f} {:6.1f} {:6.3f} {:6.3f}\n".format(
utc, sky_clarity, fit_quality, fit_quality_to_average))
# Write pyxplot command file
with open("{}.ppl".format(filename_stem), "w") as ppl:
plot_settings = {
"x_min": utc_min,
"x_max": utc_max,
"width": 18,
"spacing": 4,
"pt": 17,
"filename": filename_stem
}
ppl.write("""
set width {width}
set multiplot ; set nodisplay
set key below
set xlabel 'Time / hour' ; set xrange [{x_min}:{x_max}]
set xformat "%.1f"%((x/3600) % 24)
set ylabel 'Fit quality' ; set yrange [0:6]
set y2label 'Sky clarity' ; set y2range [0:1000]
""".format(**plot_settings))
for counter, obstory_id in enumerate(obstory_ids):
ppl.write("""
set origin ({width}+{spacing})*{counter}, 0
plot '{filename}_{counter:02d}.dat' title 'Fit quality' using 1:3 axes x1y1 with p col green pt {pt}, \
'{filename}_{counter:02d}.dat' title 'Fit quality (to daily average)' using 1:4 axes x1y1 with p col red pt {pt}, \
'{filename}_{counter:02d}.dat' title 'Sky clarity' using 1:2 axes x1y2 with p col blue pt {pt}
""".format(**plot_settings, counter=counter))
ppl.write("""
set term png ; set output '{filename}.png'
set term dpi 100
set display ; refresh
""".format(**plot_settings))
os.system("pyxplot {}.ppl".format(filename_stem))
# Close database handles
db.close_db()
conn.close()
db0.close()
return
def list_triangulations(utc_min=None, utc_max=None):
"""
Display a list of all the trajectories of moving objects registered in the database.
:param utc_min:
Only show observations made after the specified time stamp.
:type utc_min:
float
:param utc_max:
Only show observations made before the specified time stamp.
:type utc_max:
float
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Compile search criteria for observation groups
where = ["g.semanticType = (SELECT uid FROM archive_semanticTypes WHERE name=\"{}\")".
format(simultaneous_event_type)
]
args = []
if utc_min is not None:
where.append("g.time>=%s")
args.append(utc_min)
if utc_max is not None:
where.append("g.time<=%s")
args.append(utc_max)
# Search for observation groups containing groups of simultaneous detections
conn.execute("""
SELECT g.publicId AS groupId, g.time AS time, am.stringValue AS objectType,
am2.floatValue AS speed, am3.floatValue AS mean_altitude, am4.floatValue AS max_angular_offset,
am5.floatValue AS max_baseline, am6.stringValue AS radiant_direction, am7.floatValue AS sight_line_count,
am8.stringValue AS path
FROM archive_obs_groups g
INNER JOIN archive_metadata am ON g.uid = am.groupId AND
am.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="web:category")
INNER JOIN archive_metadata am2 ON g.uid = am2.groupId AND
am2.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:speed")
INNER JOIN archive_metadata am3 ON g.uid = am3.groupId AND
am3.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:mean_altitude")
INNER JOIN archive_metadata am4 ON g.uid = am4.groupId AND
am4.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:max_angular_offset")
INNER JOIN archive_metadata am5 ON g.uid = am5.groupId AND
am5.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:max_baseline")
INNER JOIN archive_metadata am6 ON g.uid = am6.groupId AND
am6.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:radiant_direction")
INNER JOIN archive_metadata am7 ON g.uid = am7.groupId AND
am7.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:sight_line_count")
INNER JOIN archive_metadata am8 ON g.uid = am8.groupId AND
am8.fieldId = (SELECT uid FROM archive_metadataFields WHERE metaKey="triangulation:path")
WHERE """ + " AND ".join(where) + """
ORDER BY g.time;
""", args)
results = conn.fetchall()
# Count how many simultaneous detections we find by type
detections_by_type = {}
# Compile tally by type
for item in results:
# Add this triangulation to tally
if item['objectType'] not in detections_by_type:
detections_by_type[item['objectType']] = 0
detections_by_type[item['objectType']] += 1
# List information about each observation in turn
print("{:16s} {:20s} {:20s} {:8s} {:10s}".format("GroupId", "Time", "Object type", "Speed", "Altitude"))
for item in results:
# Print triangulation information
print("{:16s} {:20s} {:20s} {:8.0f} {:10.0f}".format(item['groupId'],
date_string(item['time']),
item['objectType'],
item['speed'],
item['mean_altitude']
))
# Report tally of events
print("\nTally of events by type:")
for event_type in sorted(detections_by_type.keys()):
print(" * {:26s}: {:6d}".format(event_type, detections_by_type[event_type]))
def new_image(image, username, observatory, title, semantic_type='Original', time_offset=0):
"""
Insert an image file into the database.
:param image:
The filename of the image to be inserted
:param username:
The username of the user who is to own this image
:param observatory:
The observatory from which this observation was made
:param title:
The title of this image
:param semantic_type:
The semantic type of this image file, e.g. "Original"
:param time_offset:
Time offset to apply to image, seconds (positive means we move time forwards).
:type time_offset:
int
:return:
None
"""
our_path = os_path.split(os_path.abspath(__file__))[0]
db = obsarchive_db.ObservationDatabase(file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Fetch user ID
conn.execute('SELECT userId FROM pigazing_users WHERE username=%s;', (username,))
results = conn.fetchall()
assert len(results) > 0, "No such user <{}>".format(username)
# Fetch observatory ID
conn.execute('SELECT uid FROM archive_observatories WHERE publicId=%s;', (observatory,))
results = conn.fetchall()
assert len(results) > 0, "No such observatory <{}>".format(observatory)
# Look up image EXIF metadata
metadata = fetch_exif_metadata(input_path=image, time_offset=time_offset)
# Create observation object for this image
utc = time.time()
obs_obj = db.register_observation(obstory_id=observatory,
random_id=True,
obs_time=metadata['Time'],
creation_time=utc,
obs_type="image", user_id=username,
obs_meta=[],
published=1, moderated=1, featured=0,
ra=-999, dec=-999,
field_width=None, field_height=None,
position_angle=None, central_constellation=None,
altitude=-999, azimuth=-999, alt_az_pa=None,
astrometry_processed=None, astrometry_processing_time=None,
astrometry_source=None)
# Create metadata about image
obs_id = obs_obj.id
db.set_observation_metadata(username, obs_id, obsarchive_model.Meta("Observer", username))
db.set_observation_metadata(username, obs_id, obsarchive_model.Meta("Caption", title))
for key, value in metadata.items():
db.set_observation_metadata(user_id=username,
observation_id=obs_id,
meta=obsarchive_model.Meta(key, value))
db.commit()
# Make copy of file
tmp_file_path = os_path.join(our_path, "../auto/tmp/dss_images")
os.system("mkdir -p {}".format(tmp_file_path))
img_name = os_path.split(image)[1]
tmp_filename = os_path.join(tmp_file_path, img_name)
os.system("cp '{}' '{}'".format(image, tmp_filename))
os.system("chmod 644 '{}'".format(tmp_filename))
# Create file object for this image
file_obj = db.register_file(file_path=tmp_filename, user_id=username, mime_type="image/png",
semantic_type=semantic_type, primary_image=True,
file_time=metadata['Time'], file_meta=[],
observation_id=obs_id,
random_id=True)
db.commit()
def edit_user(username,
delete=False,
password=None,
name=None,
job=None,
email=None,
join_date=None,
profile_pic=None,
profile_text=None,
add_roles=None,
remove_roles=None):
"""
Edit a user's account details.
:param username:
Username for the user account that we are to edit
:param delete:
Boolean flag indicating whether we are to delete the observatory altogether
:param password:
New password for this user
:param name:
New real name for this user
:param job:
New job description for this user
:param email:
New email address for this user
:param join_date:
New join date for this user
:param profile_pic:
New profile picture for this user
:param profile_text:
New profile text for this user
:param add_roles:
New list of roles for this user
:type add_roles:
list
:param remove_roles:
List of roles to remove from this user
:type remove_roles:
list
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Fetch user ID
user_id = None
while True:
conn.execute('SELECT userId FROM pigazing_users WHERE username=%s;',
(username, ))
results = conn.fetchall()
if len(results) > 0:
user_id = results[0]['userId']
break
conn.execute('INSERT INTO pigazing_users (username) VALUES (%s);',
(username, ))
if delete:
conn.execute('DELETE FROM pigazing_users WHERE userId=%s;',
(user_id, ))
db0.commit()
db0.close()
return
if password:
conn.execute('UPDATE pigazing_users SET password=%s WHERE userId=%s;',
(passlib.hash.bcrypt.encrypt(password), user_id))
if name is not None:
conn.execute('UPDATE pigazing_users SET name = %s WHERE userId = %s',
(name, user_id))
if job is not None:
conn.execute('UPDATE pigazing_users SET job = %s WHERE userId = %s',
(job, user_id))
if email is not None:
conn.execute('UPDATE pigazing_users SET email = %s WHERE userId = %s',
(email, user_id))
if join_date is not None:
conn.execute(
'UPDATE pigazing_users SET joinDate = %s WHERE userId = %s',
(join_date, user_id))
if profile_pic is not None:
conn.execute(
'UPDATE pigazing_users SET profilePic = %s WHERE userId = %s',
(profile_pic, user_id))
if profile_text is not None:
conn.execute(
'UPDATE pigazing_users SET profileText = %s WHERE userId = %s',
(profile_text, user_id))
if add_roles:
for role in add_roles:
conn.execute("SELECT roleId FROM pigazing_roles WHERE name=%s;",
(role, ))
results = conn.fetchall()
if len(results) < 1:
conn.execute("INSERT INTO pigazing_roles (name) VALUES (%s);",
(role, ))
conn.execute(
"SELECT roleId FROM pigazing_roles WHERE name=%s;",
(role, ))
results = conn.fetchall()
conn.execute(
'REPLACE INTO pigazing_user_roles (userId, roleId) VALUES '
'((SELECT u.userId FROM pigazing_users u WHERE u.userId=%s),'
'%s)', (user_id, results[0]['roleId']))
if remove_roles:
for role in remove_roles:
conn.execute(
'DELETE FROM pigazing_user_roles WHERE userId=%s AND '
'roleId=(SELECT roleId FROM pigazing_roles WHERE name=%s);',
(user_id, role))
# Commit changes to database
db0.commit()
db0.close()
def list_orientations(obstory_id, utc_min, utc_max):
"""
List the worst orientation fits of a particular observatory within the time period between the unix times
<utc_min> and <utc_max>.
:param obstory_id:
The ID of the observatory we want to determine the orientation for.
:type obstory_id:
str
:param utc_min:
The start of the time period in which we should determine the observatory's orientation (unix time).
:type utc_min:
float
:param utc_max:
The end of the time period in which we should determine the observatory's orientation (unix time).
:type utc_max:
float
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Open connection to image archive
db = obsarchive_db.ObservationDatabase(
file_store_path=settings['dbFilestore'],
db_host=installation_info['mysqlHost'],
db_user=installation_info['mysqlUser'],
db_password=installation_info['mysqlPassword'],
db_name=installation_info['mysqlDatabase'],
obstory_id=installation_info['observatoryId'])
logging.info("Plotting camera alignment for <{}>".format(obstory_id))
# Search for background-subtracted time lapse image with best sky clarity, and no existing orientation fit,
# within this time period
conn.execute(
"""
SELECT ao.obsTime, f.repositoryFname AS observationId,
am.floatValue AS skyClarity, am2.stringValue AS fitQuality, am3.stringValue AS fitQualityToAverage
FROM archive_files f
INNER JOIN archive_observations ao on f.observationId = ao.uid
INNER JOIN archive_metadata am ON f.uid = am.fileId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
LEFT OUTER JOIN archive_metadata am2 ON ao.uid = am2.observationId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:fit_quality")
LEFT OUTER JOIN archive_metadata am3 ON ao.uid = am3.observationId AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:fit_quality_to_daily")
WHERE ao.obsTime BETWEEN %s AND %s
AND ao.observatory=(SELECT uid FROM archive_observatories WHERE publicId=%s)
AND f.semanticType=(SELECT uid FROM archive_semanticTypes WHERE name="pigazing:timelapse/backgroundSubtracted")
AND am.floatValue > %s
ORDER BY ao.obsTime
""", (utc_min, utc_max, obstory_id, minimum_sky_clarity))
results = conn.fetchall()
# Data filename
filename = "/tmp/worst_orientations"
# Loop over results
data = []
for item in results:
utc = float(item['obsTime'])
sky_clarity = float(item['skyClarity'])
fit_quality = -99
fit_quality_to_average = -99
if item['fitQuality'] is not None:
fit_quality = float(json.loads(item['fitQuality'])[0])
if item['fitQualityToAverage'] is not None:
fit_quality_to_average = float(
json.loads(item['fitQualityToAverage'])[0])
data.append({
'uid': item['observationId'],
'utc': utc,
'sky_clarity': sky_clarity,
'fit_quality': fit_quality,
'fit_quality_to_average': fit_quality_to_average
})
# Sort on fit quality
data.sort(key=itemgetter('fit_quality_to_average'))
data.reverse()
# Limit to 1000 worst points
data = data[:1000]
# Write to data file
with open("{}.dat".format(filename), "w") as f:
for item in data:
f.write("{} {:6.1f} {:6.3f} {:6.3f}\n".format(
item['uid'], item['sky_clarity'], item['fit_quality'],
item['fit_quality_to_average']))
# Close database handles
db.close_db()
conn.close()
db0.close()
return
def list_satellites(obstory_id, utc_min, utc_max):
"""
List all the satellite identifications for a particular observatory.
:param obstory_id:
The ID of the observatory we want to list identifications for.
:param utc_min:
The start of the time period in which we should list identifications (unix time).
:param utc_max:
The end of the time period in which we should list identifications (unix time).
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Start compiling list of satellite identifications
satellite_identifications = []
# Select moving objects with satellite identifications
conn.execute(
"""
SELECT am1.stringValue AS satellite_name, am2.floatValue AS ang_offset,
am3.floatValue AS clock_offset, am4.floatValue AS duration, am5.floatValue AS norad_id,
o.obsTime AS time, o.publicId AS obsId
FROM archive_observations o
INNER JOIN archive_metadata am1 ON o.uid = am1.observationId AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="satellite:name")
INNER JOIN archive_metadata am2 ON o.uid = am2.observationId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="satellite:angular_offset")
INNER JOIN archive_metadata am3 ON o.uid = am3.observationId AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="satellite:clock_offset")
INNER JOIN archive_metadata am4 ON o.uid = am4.observationId AND
am4.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:duration")
INNER JOIN archive_metadata am5 ON o.uid = am5.observationId AND
am5.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="satellite:norad_id")
WHERE
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
satellite_identifications.append({
'id':
item['obsId'],
'time':
item['time'],
'satellite_name':
item['satellite_name'],
'ang_offset':
item['ang_offset'],
'clock_offset':
item['clock_offset'],
'duration':
item['duration'],
'norad_id':
int(item['norad_id'])
})
# Sort identifications by time
satellite_identifications.sort(key=itemgetter('time'))
# Display column headings
print("""\
{:16s} {:7s} {:32s} {:26s} {:8s} {:10s} {:10s}\
""".format("Time", "NORAD", "ID", "Satellite", "Duration", "Ang offset",
"Clock offset"))
# Display list of meteors
for item in satellite_identifications:
print("""\
{:16s} {:7d} {:32s} {:26s} {:8.1f} {:10.1f} {:10.1f}\
""".format(
date_string(item['time']),
item['norad_id'],
item['id'],
item['satellite_name'],
item['duration'],
item['ang_offset'],
item['clock_offset'],
))
# Clean up and exit
return
def delete_observation(id, dry_run):
"""
Delete an observation and all associated files.
:param id:
The publicId of the observation to delete
:param dry_run:
Boolean indicating whether we should do a dry run, without actually deleting anything
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Search for observation
conn.execute(
"""
SELECT o.uid
FROM archive_observations o
WHERE o.publicId=%s;
""", (id, ))
results_observations = conn.fetchall()
# Delete each observation in turn
for observation in results_observations:
# Search for files
conn.execute(
"""
SELECT f.uid, f.repositoryFname
FROM archive_files f
WHERE f.observationId=%s
""", (observation['uid'], ))
results_files = conn.fetchall()
# Delete each file in turn
for file in results_files:
logging.info("Deleting file <{}>".format(file['repositoryFname']))
# Delete files
if not dry_run:
os.unlink(
os.path.join(settings['dbFilestore'],
file['repositoryFname']))
# Delete file record
if not dry_run:
conn.execute("DELETE FROM archive_files WHERE uid=%s",
(file['uid'], ))
# Delete observation
if not dry_run:
conn.execute("DELETE FROM archive_observations WHERE uid=%s",
(observation['uid'], ))
# Commit changes to database
db0.commit()
conn.close()
db0.close()
def update_sky_clarity(utc_min=None,
utc_max=None,
username=None,
obstory=None):
"""
Update the sky clarity measurements in the database. This is useful if the algorithm is changed.
:param utc_min:
Only update observations made after the specified time stamp.
:type utc_min:
float
:param utc_max:
Only update observations made before the specified time stamp.
:type utc_max:
float
:param username:
Optionally specify a username, to filter only images by a particular user
:type username:
str
:param obstory:
The public id of the observatory we are to update observations from
:type obstory:
str
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
where = ["1"]
args = []
if utc_min is not None:
where.append("o.obsTime>=%s")
args.append(utc_min)
if utc_max is not None:
where.append("o.obsTime<=%s")
args.append(utc_max)
if username is not None:
where.append("o.userId=%s")
args.append(username)
if obstory is not None:
where.append("l.publicId=%s")
args.append(obstory)
conn.execute(
"""
SELECT o.uid, o.userId, l.name AS place, o.obsTime, am.uid AS skyClarityUid
FROM archive_observations o
INNER JOIN archive_observatories l ON o.observatory = l.uid
INNER JOIN archive_semanticTypes ast ON o.obsType = ast.uid
LEFT OUTER JOIN archive_metadata am ON o.uid = am.observationId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
WHERE """ + " AND ".join(where) + """
ORDER BY obsTime ASC;
""", args)
results = conn.fetchall()
# Keep track of how many sky clarity values we have updated
values_unchanged = 0
values_updated = 0
# Update each observation in turn
for counter, obs in enumerate(results):
# Fetch list of files in this observation
conn.execute(
"""
SELECT ast.name AS semanticType, repositoryFname,
am.floatValue AS skyClarity, am.uid AS skyClarityUid, am2.floatValue AS noiseLevel
FROM archive_files f
INNER JOIN archive_semanticTypes ast ON f.semanticType = ast.uid
INNER JOIN archive_metadata am ON f.uid = am.fileId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
LEFT OUTER JOIN archive_metadata am2 ON f.uid = am2.fileId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:stackNoiseLevel")
WHERE f.observationId=%s;
""", (obs['uid'], ))
files = conn.fetchall()
for item in files:
# Run sky clarity calculator
p = subprocess.Popen(args=[
os.path.join(settings['imageProcessorPath'],
"skyClarity"), '--input',
os.path.join(settings['dbFilestore'], item['repositoryFname']),
'--noise',
str(float(item['noiseLevel']))
],
stdout=subprocess.PIPE,
stdin=subprocess.PIPE,
stderr=subprocess.STDOUT)
# Extract new sky clarity measurement
new_sky_clarity = float(
p.communicate(input=bytes("", 'utf-8'))[0].decode('utf-8'))
if new_sky_clarity == item['skyClarity']:
values_unchanged += 1
continue
# Print new measurement
values_updated += 1
logging.info("Updating {} from {:.0f} to {:.0f}".format(
item['repositoryFname'], item['skyClarity'], new_sky_clarity))
# Commit to database
conn.execute(
"UPDATE archive_metadata SET floatValue=%s WHERE uid=%s",
(new_sky_clarity, item['skyClarityUid']))
# Update the observation with the background-subtracted image's sky clarity
if ((item['semanticType']
== 'pigazing:timelapse/backgroundSubtracted')
and (obs['skyClarityUid'] is not None)):
conn.execute(
"UPDATE archive_metadata SET floatValue=%s WHERE uid=%s",
(new_sky_clarity, obs['skyClarityUid']))
# Commit changes to database
db0.commit()
db0.close()
# Report how many values we changed
logging.info("Updated {:d} images. Left {:d} images unchanged".format(
values_updated, values_unchanged))
def list_meteors(obstory_id, utc_min, utc_max):
"""
List all the meteor identifications for a particular observatory.
:param obstory_id:
The ID of the observatory we want to list meteor identifications for.
:param utc_min:
The start of the time period in which we should list meteor identifications (unix time).
:param utc_max:
The end of the time period in which we should list meteor identifications (unix time).
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Start compiling list of meteor identifications
meteor_identifications = []
# Count how many meteors we find in each shower
meteor_count_by_shower = {}
# Select observations with orientation fits
conn.execute("""
SELECT am1.stringValue AS name, am2.floatValue AS radiant_offset,
o.obsTime AS time, o.publicId AS obsId
FROM archive_observations o
INNER JOIN archive_metadata am1 ON o.uid = am1.observationId AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="shower:name")
INNER JOIN archive_metadata am2 ON o.uid = am2.observationId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="shower:radiant_offset")
WHERE
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
meteor_identifications.append({
'id': item['obsId'],
'time': item['time'],
'shower': item['name'],
'offset': item['radiant_offset']
})
# Update tally of meteors
if item['name'] not in meteor_count_by_shower:
meteor_count_by_shower[item['name']] = 0
meteor_count_by_shower[item['name']] += 1
# Sort meteors by time
meteor_identifications.sort(key=itemgetter('time'))
# Display column headings
print("""\
{:16s} {:20s} {:20s} {:5s}\
""".format("Time", "ID", "Shower", "Offset"))
# Display list of meteors
for item in meteor_identifications:
print("""\
{:16s} {:20s} {:26s} {:5.1f}\
""".format(date_string(item['time']),
item['id'],
item['shower'],
item['offset']
))
# Report tally of meteors
logging.info("Tally of meteors by shower:")
for shower in sorted(meteor_count_by_shower.keys()):
logging.info(" * {:26s}: {:6d}".format(shower, meteor_count_by_shower[shower]))
# Clean up and exit
return
def list_calibration_fixes(obstory_id, utc_min, utc_max):
"""
List all the calibration fixes for a particular observatory.
:param obstory_id:
The ID of the observatory we want to list calibration fixes for.
:param utc_min:
The start of the time period in which we should list calibration fixes (unix time).
:param utc_max:
The end of the time period in which we should list calibration fixes (unix time).
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Start compiling list of calibration fixes
calibration_fixes = []
# Select observatory with calibration fits
conn.execute(
"""
SELECT am1.stringValue AS barrel_parameters,
am4.floatValue AS chi_squared, am5.stringValue AS point_count,
o.obsTime AS time
FROM archive_observations o
INNER JOIN archive_metadata am1 ON o.uid = am1.observationId AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:lens_barrel_parameters")
INNER JOIN archive_metadata am4 ON o.uid = am4.observationId AND
am4.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:chi_squared")
INNER JOIN archive_metadata am5 ON o.uid = am5.observationId AND
am5.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:point_count")
WHERE
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
calibration_fixes.append({
'time': item['time'],
'average': False,
'fit': item
})
# Select observation calibration fits
conn.execute(
"""
SELECT am1.stringValue AS barrel_parameters,
am3.floatValue AS chi_squared, am4.stringValue AS point_count,
am1.time AS time
FROM archive_observatories o
INNER JOIN archive_metadata am1 ON o.uid = am1.observatory AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:lens_barrel_parameters")
LEFT OUTER JOIN archive_metadata am3 ON o.uid = am3.observatory AND am3.time=am1.time AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:chi_squared")
LEFT OUTER JOIN archive_metadata am4 ON o.uid = am4.observatory AND am4.time=am1.time AND
am4.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="calibration:point_count")
WHERE
o.publicId=%s AND
am1.time BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
calibration_fixes.append({
'time': item['time'],
'average': True,
'fit': item
})
# Sort fixes by time
calibration_fixes.sort(key=itemgetter('time'))
# Display column headings
print("""\
{:1s} {:16s} {:8s} {:8s} {:10s} {:12s} {:6s}\
""".format("", "Time", "barrelK1", "barrelK2", "barrelK3", "chi2", "points"))
# Display fixes
for item in calibration_fixes:
# Deal with missing data
if item['fit']['chi_squared'] is None:
item['fit']['chi_squared'] = -1
if item['fit']['point_count'] is None:
item['fit']['point_count'] = "-"
# Display calibration fix
barrel_parameters = json.loads(item['fit']['barrel_parameters'])
print("""\
{:s} {:16s} {:8.4f} {:8.4f} {:10.7f} {:12.9f} {:s} {:s}\
""".format("\n>" if item['average'] else " ", date_string(item['time']),
barrel_parameters[2], barrel_parameters[3], barrel_parameters[4],
item['fit']['chi_squared'], item['fit']['point_count'],
"\n" if item['average'] else ""))
# Clean up and exit
return
def list_orientation_fixes(obstory_id, utc_min, utc_max):
"""
List all the orientation fixes for a particular observatory.
:param obstory_id:
The ID of the observatory we want to list orientation fixes for.
:param utc_min:
The start of the time period in which we should list orientation fixes (unix time).
:param utc_max:
The end of the time period in which we should list orientation fixes (unix time).
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Start compiling list of orientation fixes
orientation_fixes = []
# Select observations with orientation fits
conn.execute(
"""
SELECT am1.floatValue AS altitude, am2.floatValue AS azimuth, am3.floatValue AS tilt,
am4.floatValue AS width_x_field, am5.floatValue AS width_y_field,
o.obsTime AS time
FROM archive_observations o
INNER JOIN archive_metadata am1 ON o.uid = am1.observationId AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:altitude")
INNER JOIN archive_metadata am2 ON o.uid = am2.observationId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:azimuth")
INNER JOIN archive_metadata am3 ON o.uid = am3.observationId AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:tilt")
INNER JOIN archive_metadata am4 ON o.uid = am4.observationId AND
am4.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:width_x_field")
INNER JOIN archive_metadata am5 ON o.uid = am5.observationId AND
am5.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:width_y_field")
WHERE
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
orientation_fixes.append({
'time': item['time'],
'average': False,
'fit': item
})
# Select observatory orientation fits
conn.execute(
"""
SELECT am1.floatValue AS altitude, am2.floatValue AS azimuth, am3.floatValue AS tilt,
am4.floatValue AS width_x_field, am5.floatValue AS width_y_field,
am1.time AS time
FROM archive_observatories o
INNER JOIN archive_metadata am1 ON o.uid = am1.observatory AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:altitude")
INNER JOIN archive_metadata am2 ON o.uid = am2.observatory AND am2.time=am1.time AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:azimuth")
INNER JOIN archive_metadata am3 ON o.uid = am3.observatory AND am3.time=am1.time AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:tilt")
INNER JOIN archive_metadata am4 ON o.uid = am4.observatory AND am4.time=am1.time AND
am4.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:width_x_field")
INNER JOIN archive_metadata am5 ON o.uid = am5.observatory AND am5.time=am1.time AND
am5.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="orientation:width_y_field")
WHERE
o.publicId=%s AND
am1.time BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
orientation_fixes.append({
'time': item['time'],
'average': True,
'fit': item
})
# Sort fixes by time
orientation_fixes.sort(key=itemgetter('time'))
# Display column headings
print("""\
{:1s} {:16s} {:9s} {:9s} {:9s} {:8s} {:8s}\
""".format("", "Time", "Alt", "Az", "Tilt", "FoV X", "FoV Y"))
# Display fixes
for item in orientation_fixes:
print("""\
{:s} {:16s} {:9.4f} {:9.4f} {:9.4f} {:8.3f} {:8.3f} {:s}\
""".format("\n>" if item['average'] else " ", date_string(item['time']),
item['fit']['altitude'], item['fit']['azimuth'],
item['fit']['tilt'], item['fit']['width_x_field'],
item['fit']['width_y_field'], "\n" if item['average'] else ""))
# Clean up and exit
return
def fetch_images(utc_min, utc_max, obstory, img_types, stride):
"""
Fetch a list of all the images registered in the database.
:param utc_min:
Only return observations made after the specified time stamp.
:type utc_min:
float
:param utc_max:
Only return observations made before the specified time stamp.
:type utc_max:
float
:param obstory:
The public id of the observatory we are to fetch observations from
:type obstory:
str
:param img_types:
Only return images with these semantic types
:type img_types:
list[str]
:param stride:
Only return every nth observation matching the search criteria
:type stride:
int
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Get list of observations
conn.execute(
"""
SELECT o.uid, o.userId, l.name AS place, o.obsTime
FROM archive_observations o
INNER JOIN archive_observatories l ON o.observatory = l.uid
INNER JOIN archive_semanticTypes ast ON o.obsType = ast.uid
WHERE o.obsTime BETWEEN %s AND %s AND l.publicId=%s
ORDER BY obsTime ASC;
""", (utc_min, utc_max, obstory))
results = conn.fetchall()
# Show each observation in turn
file_list = []
for counter, obs in enumerate(results):
# Only show every nth hit
if counter % stride != 0:
continue
# Fetch list of files in this observation
conn.execute(
"""
SELECT ast.name AS semanticType, repositoryFname, am.floatValue AS skyClarity
FROM archive_files f
INNER JOIN archive_semanticTypes ast ON f.semanticType = ast.uid
LEFT OUTER JOIN archive_metadata am ON f.uid = am.fileId AND
am.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:skyClarity")
WHERE f.observationId=%s;
""", (obs['uid'], ))
files = conn.fetchall()
# Make dictionary of files by semantic type
files_by_type = {'observation': obs}
for file in files:
files_by_type[file['semanticType']] = file
# Check that we have all the requested types of file
have_all_types = True
for img_type in img_types:
if img_type not in files_by_type:
have_all_types = False
if not have_all_types:
continue
file_list.append(files_by_type)
return file_list
def list_planes(obstory_id, utc_min, utc_max):
"""
List all the plane identifications for a particular observatory.
:param obstory_id:
The ID of the observatory we want to list identifications for.
:param utc_min:
The start of the time period in which we should list identifications (unix time).
:param utc_max:
The end of the time period in which we should list identifications (unix time).
:return:
None
"""
# Open connection to database
[db0, conn] = connect_db.connect_db()
# Start compiling list of plane identifications
plane_identifications = []
# Select moving objects with plane identifications
conn.execute(
"""
SELECT am1.stringValue AS call_sign, am2.floatValue AS ang_offset,
am3.floatValue AS clock_offset, am4.floatValue AS duration, am5.stringValue AS hex_ident,
am6.floatValue AS distance,
am7.stringValue AS operator, am8.stringValue AS model, am9.stringValue AS manufacturer,
o.obsTime AS time, o.publicId AS obsId
FROM archive_observations o
INNER JOIN archive_metadata am1 ON o.uid = am1.observationId AND
am1.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:call_sign")
INNER JOIN archive_metadata am2 ON o.uid = am2.observationId AND
am2.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:angular_offset")
INNER JOIN archive_metadata am3 ON o.uid = am3.observationId AND
am3.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:clock_offset")
INNER JOIN archive_metadata am4 ON o.uid = am4.observationId AND
am4.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="pigazing:duration")
INNER JOIN archive_metadata am5 ON o.uid = am5.observationId AND
am5.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:hex_ident")
LEFT JOIN archive_metadata am6 ON o.uid = am6.observationId AND
am6.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:distance")
LEFT JOIN archive_metadata am7 ON o.uid = am7.observationId AND
am7.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:operator")
LEFT JOIN archive_metadata am8 ON o.uid = am8.observationId AND
am8.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:model")
LEFT JOIN archive_metadata am9 ON o.uid = am9.observationId AND
am9.fieldId=(SELECT uid FROM archive_metadataFields WHERE metaKey="plane:manufacturer")
WHERE
o.observatory = (SELECT uid FROM archive_observatories WHERE publicId=%s) AND
o.obsTime BETWEEN %s AND %s;
""", (obstory_id, utc_min, utc_max))
results = conn.fetchall()
for item in results:
plane_identifications.append({
'id': item['obsId'],
'time': item['time'],
'call_sign': item['call_sign'],
'ang_offset': item['ang_offset'],
'clock_offset': item['clock_offset'],
'duration': item['duration'],
'hex_ident': item['hex_ident'],
'distance': item['distance'],
'operator': item['operator'],
'model': item['model'],
'manufacturer': item['manufacturer']
})
# Sort identifications by time
plane_identifications.sort(key=itemgetter('time'))
# Display column headings
print("""\
{:16s} {:18s} {:18s} {:8s} {:10s} {:10s} {:10s} {:30s} {:30s} {:30s}\
""".format("Time", "Call sign", "Hex ident", "Duration", "Ang offset",
"Clock off", "Distance", "Operator", "Model", "Manufacturer"))
# Display list of meteors
for item in plane_identifications:
print("""\
{:16s} {:18s} {:18s} {:8.1f} {:10.1f} {:10.1f} {:10.1f} {:30s} {:30s} {:30s}\
""".format(date_string(item['time']), item['call_sign'], item['hex_ident'],
item['duration'], item['ang_offset'], item['clock_offset'],
item['distance'], item['operator'], item['model'],
item['manufacturer']))
# Clean up and exit
return
