1#!/usr/local/bin/python 

2# encoding: utf-8 

3""" 

4*classify a set of transients defined by a database query in the sherlock settings file* 

5 

6:Author: 

7 David Young 

8""" 

9from __future__ import print_function 

10from __future__ import division 

11from builtins import zip 

12from builtins import str 

13from builtins import range 

14from builtins import object 

15from past.utils import old_div 

16import sys 

17import os 

18import collections 

19import codecs 

20import re 

21import math 

22import time 

23import inspect 

24import yaml 

25from random import randint 

26os.environ['TERM'] = 'vt100' 

27from datetime import datetime, date, time, timedelta 

28from operator import itemgetter 

29import numpy as np 

30from fundamentals import tools 

31from fundamentals.mysql import readquery, directory_script_runner, writequery 

32from fundamentals.renderer import list_of_dictionaries 

33from HMpTy.mysql import conesearch 

34from HMpTy.htm import sets 

35from sherlock.imports import ned 

36from sherlock.commonutils import get_crossmatch_catalogues_column_map 

37import psutil 

38from fundamentals import fmultiprocess 

39from fundamentals.mysql import insert_list_of_dictionaries_into_database_tables 

40import copy 

41import psutil 

42import copy 

43from operator import itemgetter 

44from astrocalc.coords import unit_conversion 

45 

46theseBatches = [] 

47crossmatchArray = [] 

48 

49 

50class transient_classifier(object): 

51 """ 

52 *The Sherlock Transient Classifier* 

53 

54 **Key Arguments** 

55 

56 - ``log`` -- logger 

57 - ``settings`` -- the settings dictionary 

58 - ``update`` -- update the transient database with crossmatch results (boolean) 

59 - ``ra`` -- right ascension of a single transient source. Default *False* 

60 - ``dec`` -- declination of a single transient source. Default *False* 

61 - ``name`` -- the ID of a single transient source. Default *False* 

62 - ``verbose`` -- amount of details to print about crossmatches to stdout. 0|1|2 Default *0* 

63 - ``updateNed`` -- update the local NED database before running the classifier. Classification will not be as accuracte the NED database is not up-to-date. Default *True*. 

64 - ``daemonMode`` -- run sherlock in daemon mode. In daemon mode sherlock remains live and classifies sources as they come into the database. Default *True* 

65 - ``updatePeakMags`` -- update peak magnitudes in human-readable annotation of objects (can take some time - best to run occationally) 

66 - ``lite`` -- return only a lite version of the results with the topped ranked matches only. Default *False* 

67 - ``oneRun`` -- only process one batch of transients, usful for unit testing. Default *False* 

68 

69 **Usage** 

70 

71 To setup your logger, settings and database connections, please use the ``fundamentals`` package (`see tutorial here <http://fundamentals.readthedocs.io/en/latest/#tutorial>`_). 

72 

73 To initiate a transient_classifier object, use the following: 

74 

75 .. todo:: 

76 

77 - update the package tutorial if needed 

78 

79 The sherlock classifier can be run in one of two ways. The first is to pass into the coordinates of an object you wish to classify: 

80 

81 ```python 

82 from sherlock import transient_classifier 

83 classifier = transient_classifier( 

84 log=log, 

85 settings=settings, 

86 ra="08:57:57.19", 

87 dec="+43:25:44.1", 

88 name="PS17gx", 

89 verbose=0 

90 ) 

91 classifications, crossmatches = classifier.classify() 

92 ``` 

93 

94 The crossmatches returned are a list of dictionaries giving details of the crossmatched sources. The classifications returned are a list of classifications resulting from these crossmatches. The lists are ordered from most to least likely classification and the indicies for the crossmatch and the classification lists are synced. 

95 

96 The second way to run the classifier is to not pass in a coordinate set and therefore cause sherlock to run the classifier on the transient database referenced in the sherlock settings file: 

97 

98 ```python 

99 from sherlock import transient_classifier 

100 classifier = transient_classifier( 

101 log=log, 

102 settings=settings, 

103 update=True 

104 ) 

105 classifier.classify() 

106 ``` 

107 

108 Here the transient list is selected out of the database using the ``transient query`` value in the settings file: 

109 

110 ```yaml 

111 database settings: 

112 transients: 

113 user: myusername 

114 password: mypassword 

115 db: nice_transients 

116 host: 127.0.0.1 

117 transient table: transientBucket 

118 transient query: "select primaryKeyId as 'id', transientBucketId as 'alt_id', raDeg 'ra', decDeg 'dec', name 'name', sherlockClassification as 'object_classification' 

119 from transientBucket where object_classification is null 

120 transient primary id column: primaryKeyId 

121 transient classification column: sherlockClassification 

122 tunnel: False 

123 ``` 

124 

125 By setting ``update=True`` the classifier will update the ``sherlockClassification`` column of the ``transient table`` with new classification and populate the ``sherlock_crossmatches`` table with key details of the crossmatched sources from the catalogues database. By setting ``update=False`` results are printed to stdout but the database is not updated (useful for dry runs and testing new algorithms), 

126 

127 

128 .. todo :: 

129 

130 - update key arguments values and definitions with defaults 

131 - update return values and definitions 

132 - update usage examples and text 

133 - update docstring text 

134 - check sublime snippet exists 

135 - clip any useful text to docs mindmap 

136 - regenerate the docs and check redendering of this docstring 

137 """ 

138 # INITIALISATION 

139 

140 def __init__( 

141 self, 

142 log, 

143 settings=False, 

144 update=False, 

145 ra=False, 

146 dec=False, 

147 name=False, 

148 verbose=0, 

149 updateNed=True, 

150 daemonMode=False, 

151 updatePeakMags=True, 

152 oneRun=False, 

153 lite=False 

154 ): 

155 self.log = log 

156 log.debug("instansiating a new 'classifier' object") 

157 self.settings = settings 

158 self.update = update 

159 self.ra = ra 

160 self.dec = dec 

161 self.name = name 

162 self.cl = False 

163 self.verbose = verbose 

164 self.updateNed = updateNed 

165 self.daemonMode = daemonMode 

166 self.updatePeakMags = updatePeakMags 

167 self.oneRun = oneRun 

168 self.lite = lite 

169 self.filterPreference = [ 

170 "R", "_r", "G", "V", "_g", "B", "I", "_i", "_z", "J", "H", "K", "U", "_u", "_y", "W1", "unkMag" 

171 ] 

172 

173 # COLLECT ADVANCED SETTINGS IF AVAILABLE 

174 parentDirectory = os.path.dirname(__file__) 

175 advs = parentDirectory + "/advanced_settings.yaml" 

176 level = 0 

177 exists = False 

178 count = 1 

179 while not exists and len(advs) and count < 10: 

180 count += 1 

181 level -= 1 

182 exists = os.path.exists(advs) 

183 if not exists: 

184 advs = "/".join(parentDirectory.split("/") 

185 [:level]) + "/advanced_settings.yaml" 

186 print(advs) 

187 if not exists: 

188 advs = {} 

189 else: 

190 with open(advs, 'r') as stream: 

191 advs = yaml.load(stream) 

192 # MERGE ADVANCED SETTINGS AND USER SETTINGS (USER SETTINGS OVERRIDE) 

193 self.settings = {**advs, **self.settings} 

194 

195 # INITIAL ACTIONS 

196 # SETUP DATABASE CONNECTIONS 

197 # SETUP ALL DATABASE CONNECTIONS 

198 from sherlock import database 

199 db = database( 

200 log=self.log, 

201 settings=self.settings 

202 ) 

203 dbConns, dbVersions = db.connect() 

204 self.dbVersions = dbVersions 

205 self.transientsDbConn = dbConns["transients"] 

206 self.cataloguesDbConn = dbConns["catalogues"] 

207 

208 # SIZE OF BATCHES TO SPLIT TRANSIENT INTO BEFORE CLASSIFYING 

209 self.largeBatchSize = self.settings["database-batch-size"] 

210 self.miniBatchSize = 1000 

211 

212 # LITE VERSION CANNOT BE RUN ON A DATABASE QUERY AS YET 

213 if self.ra == False: 

214 self.lite = False 

215 

216 # IS SHERLOCK CLASSIFIER BEING QUERIED FROM THE COMMAND-LINE? 

217 if self.ra and self.dec: 

218 self.cl = True 

219 if not self.name: 

220 self.name = "Transient" 

221 

222 # ASTROCALC UNIT CONVERTER OBJECT 

223 self.converter = unit_conversion( 

224 log=self.log 

225 ) 

226 

227 if self.ra and not isinstance(self.ra, float) and ":" in self.ra: 

228 # ASTROCALC UNIT CONVERTER OBJECT 

229 self.ra = self.converter.ra_sexegesimal_to_decimal( 

230 ra=self.ra 

231 ) 

232 self.dec = self.converter.dec_sexegesimal_to_decimal( 

233 dec=self.dec 

234 ) 

235 

236 # DATETIME REGEX - EXPENSIVE OPERATION, LET"S JUST DO IT ONCE 

237 self.reDatetime = re.compile('^[0-9]{4}-[0-9]{2}-[0-9]{2}T') 

238 

239 return None 

240 

241 def classify(self): 

242 """ 

243 *classify the transients selected from the transient selection query in the settings file or passed in via the CL or other code* 

244 

245 **Return** 

246 

247 - ``crossmatches`` -- list of dictionaries of crossmatched associated sources 

248 - ``classifications`` -- the classifications assigned to the transients post-crossmatches (dictionary of rank ordered list of classifications) 

249 

250 

251 See class docstring for usage. 

252 

253 .. todo :: 

254 

255 - update key arguments values and definitions with defaults 

256 - update return values and definitions 

257 - update usage examples and text 

258 - update docstring text 

259 - check sublime snippet exists 

260 - clip any useful text to docs mindmap 

261 - regenerate the docs and check redendering of this docstring 

262 """ 

263 

264 global theseBatches 

265 global crossmatchArray 

266 

267 self.log.debug('starting the ``classify`` method') 

268 

269 remaining = 1 

270 

271 # THE COLUMN MAPS - WHICH COLUMNS IN THE CATALOGUE TABLES = RA, DEC, 

272 # REDSHIFT, MAG ETC 

273 colMaps = get_crossmatch_catalogues_column_map( 

274 log=self.log, 

275 dbConn=self.cataloguesDbConn 

276 ) 

277 

278 if self.transientsDbConn and self.update: 

279 self._create_tables_if_not_exist() 

280 

281 import time 

282 start_time = time.time() 

283 

284 # COUNT SEARCHES 

285 sa = self.settings["search algorithm"] 

286 searchCount = 0 

287 brightnessFilters = ["bright", "faint", "general"] 

288 for search_name, searchPara in list(sa.items()): 

289 for bf in brightnessFilters: 

290 if bf in searchPara: 

291 searchCount += 1 

292 

293 cpuCount = psutil.cpu_count() 

294 if searchCount > cpuCount: 

295 searchCount = cpuCount 

296 

297 miniBatchSize = self.miniBatchSize 

298 

299 while remaining: 

300 

301 # IF A TRANSIENT HAS NOT BEEN PASSED IN VIA THE COMMAND-LINE, THEN 

302 # QUERY THE TRANSIENT DATABASE 

303 if not self.ra and not self.dec: 

304 

305 # COUNT REMAINING TRANSIENTS 

306 from fundamentals.mysql import readquery 

307 sqlQuery = self.settings["database settings"][ 

308 "transients"]["transient count"] 

309 thisInt = randint(0, 100) 

310 if "where" in sqlQuery: 

311 sqlQuery = sqlQuery.replace( 

312 "where", "where %(thisInt)s=%(thisInt)s and " % locals()) 

313 

314 if remaining == 1 or remaining < self.largeBatchSize: 

315 rows = readquery( 

316 log=self.log, 

317 sqlQuery=sqlQuery, 

318 dbConn=self.transientsDbConn, 

319 ) 

320 remaining = rows[0]["count(*)"] 

321 else: 

322 remaining = remaining - self.largeBatchSize 

323 

324 print( 

325 "%(remaining)s transient sources requiring a classification remain" % locals()) 

326 

327 # START THE TIME TO TRACK CLASSIFICATION SPPED 

328 start_time = time.time() 

329 

330 # A LIST OF DICTIONARIES OF TRANSIENT METADATA 

331 transientsMetadataList = self._get_transient_metadata_from_database_list() 

332 

333 count = len(transientsMetadataList) 

334 print( 

335 " now classifying the next %(count)s transient sources" % locals()) 

336 

337 # EXAMPLE OF TRANSIENT METADATA 

338 # { 'name': 'PS17gx', 

339 # 'alt_id': 'PS17gx', 

340 # 'object_classification': 'SN', 

341 # 'dec': '+43:25:44.1', 

342 # 'id': 1, 

343 # 'ra': '08:57:57.19'} 

344 # TRANSIENT PASSED VIA COMMAND-LINE 

345 else: 

346 # CONVERT SINGLE TRANSIENTS TO LIST 

347 if not isinstance(self.ra, list): 

348 self.ra = [self.ra] 

349 self.dec = [self.dec] 

350 self.name = [self.name] 

351 

352 # GIVEN TRANSIENTS UNIQUE NAMES IF NOT PROVIDED 

353 if not self.name[0]: 

354 self.name = [] 

355 for i, v in enumerate(self.ra): 

356 self.name.append("transient_%(i)05d" % locals()) 

357 

358 transientsMetadataList = [] 

359 for r, d, n in zip(self.ra, self.dec, self.name): 

360 transient = { 

361 'name': n, 

362 'object_classification': None, 

363 'dec': d, 

364 'id': n, 

365 'ra': r 

366 } 

367 transientsMetadataList.append(transient) 

368 remaining = 0 

369 

370 if self.oneRun: 

371 remaining = 0 

372 

373 if len(transientsMetadataList) == 0: 

374 if self.daemonMode == False: 

375 remaining = 0 

376 print("No transients need classified") 

377 return None, None 

378 else: 

379 print( 

380 "No remaining transients need classified, will try again in 5 mins") 

381 time.sleep("10") 

382 

383 # FROM THE LOCATIONS OF THE TRANSIENTS, CHECK IF OUR LOCAL NED DATABASE 

384 # NEEDS UPDATED 

385 if self.updateNed: 

386 

387 self._update_ned_stream( 

388 transientsMetadataList=transientsMetadataList 

389 ) 

390 

391 # SOME TESTING SHOWED THAT 25 IS GOOD 

392 total = len(transientsMetadataList) 

393 batches = int((old_div(float(total), float(miniBatchSize))) + 1.) 

394 

395 if batches == 0: 

396 batches = 1 

397 

398 start = 0 

399 end = 0 

400 theseBatches = [] 

401 for i in range(batches): 

402 end = end + miniBatchSize 

403 start = i * miniBatchSize 

404 thisBatch = transientsMetadataList[start:end] 

405 theseBatches.append(thisBatch) 

406 

407 if self.verbose: 

408 print("BATCH SIZE = %(total)s" % locals()) 

409 print("MINI BATCH SIZE = %(batches)s x %(miniBatchSize)s" % locals()) 

410 

411 poolSize = self.settings["cpu-pool-size"] 

412 if poolSize and batches < poolSize: 

413 poolSize = batches 

414 

415 start_time2 = time.time() 

416 

417 if self.verbose: 

418 print("START CROSSMATCH") 

419 

420 crossmatchArray = fmultiprocess(log=self.log, function=_crossmatch_transients_against_catalogues, 

421 inputArray=list(range(len(theseBatches))), poolSize=poolSize, settings=self.settings, colMaps=colMaps) 

422 

423 if self.verbose: 

424 print("FINISH CROSSMATCH/START RANKING: %d" % 

425 (time.time() - start_time2,)) 

426 start_time2 = time.time() 

427 

428 classifications = {} 

429 crossmatches = [] 

430 

431 for sublist in crossmatchArray: 

432 sublist = sorted( 

433 sublist, key=itemgetter('transient_object_id')) 

434 

435 # REORGANISE INTO INDIVIDUAL TRANSIENTS FOR RANKING AND 

436 # TOP-LEVEL CLASSIFICATION EXTRACTION 

437 

438 batch = [] 

439 if len(sublist) != 0: 

440 transientId = sublist[0]['transient_object_id'] 

441 for s in sublist: 

442 if s['transient_object_id'] != transientId: 

443 # RANK TRANSIENT CROSSMATCH BATCH 

444 cl, cr = self._rank_classifications( 

445 batch, colMaps) 

446 crossmatches.extend(cr) 

447 classifications = dict( 

448 list(classifications.items()) + list(cl.items())) 

449 

450 transientId = s['transient_object_id'] 

451 batch = [s] 

452 else: 

453 batch.append(s) 

454 

455 # RANK FINAL BATCH 

456 cl, cr = self._rank_classifications( 

457 batch, colMaps) 

458 classifications = dict( 

459 list(classifications.items()) + list(cl.items())) 

460 crossmatches.extend(cr) 

461 

462 for t in transientsMetadataList: 

463 if t["id"] not in classifications: 

464 classifications[t["id"]] = ["ORPHAN"] 

465 

466 # UPDATE THE TRANSIENT DATABASE IF UPDATE REQUESTED (ADD DATA TO 

467 # tcs_crossmatch_table AND A CLASSIFICATION TO THE ORIGINAL TRANSIENT 

468 # TABLE) 

469 if self.verbose: 

470 print("FINISH RANKING/START UPDATING TRANSIENT DB: %d" % 

471 (time.time() - start_time2,)) 

472 start_time2 = time.time() 

473 if self.update and not self.ra: 

474 self._update_transient_database( 

475 crossmatches=crossmatches, 

476 classifications=classifications, 

477 transientsMetadataList=transientsMetadataList, 

478 colMaps=colMaps 

479 ) 

480 

481 if self.verbose: 

482 print("FINISH UPDATING TRANSIENT DB/START ANNOTATING TRANSIENT DB: %d" % 

483 (time.time() - start_time2,)) 

484 start_time2 = time.time() 

485 

486 # COMMAND-LINE SINGLE CLASSIFICATION 

487 if self.ra: 

488 classifications = self.update_classification_annotations_and_summaries( 

489 False, True, crossmatches, classifications) 

490 for k, v in classifications.items(): 

491 if len(v) == 1 and v[0] == "ORPHAN": 

492 v.append( 

493 "No contexual information is available for this transient") 

494 

495 if self.lite != False: 

496 crossmatches = self._lighten_return(crossmatches) 

497 

498 if self.cl: 

499 self._print_results_to_stdout( 

500 classifications=classifications, 

501 crossmatches=crossmatches 

502 ) 

503 

504 return classifications, crossmatches 

505 

506 if self.updatePeakMags and self.settings["database settings"]["transients"]["transient peak magnitude query"]: 

507 self.update_peak_magnitudes() 

508 

509 # BULK RUN -- NOT A COMMAND-LINE SINGLE CLASSIFICATION 

510 self.update_classification_annotations_and_summaries( 

511 self.updatePeakMags) 

512 

513 print("FINISH ANNOTATING TRANSIENT DB: %d" % 

514 (time.time() - start_time2,)) 

515 start_time2 = time.time() 

516 

517 classificationRate = old_div(count, (time.time() - start_time)) 

518 print( 

519 "Sherlock is classify at a rate of %(classificationRate)2.1f transients/sec" % locals()) 

520 

521 self.log.debug('completed the ``classify`` method') 

522 return None, None 

523 

524 def _get_transient_metadata_from_database_list( 

525 self): 

526 """use the transient query in the settings file to generate a list of transients to corssmatch and classify 

527 

528 **Return** 

529 

530 

531 - ``transientsMetadataList`` 

532 

533 .. todo :: 

534 

535 - update key arguments values and definitions with defaults 

536 - update return values and definitions 

537 - update usage examples and text 

538 - update docstring text 

539 - check sublime snippet exists 

540 - clip any useful text to docs mindmap 

541 - regenerate the docs and check redendering of this docstring 

542 """ 

543 self.log.debug( 

544 'starting the ``_get_transient_metadata_from_database_list`` method') 

545 

546 sqlQuery = self.settings["database settings"][ 

547 "transients"]["transient query"] + " limit " + str(self.largeBatchSize) 

548 

549 thisInt = randint(0, 100) 

550 if "where" in sqlQuery: 

551 sqlQuery = sqlQuery.replace( 

552 "where", "where %(thisInt)s=%(thisInt)s and " % locals()) 

553 

554 transientsMetadataList = readquery( 

555 log=self.log, 

556 sqlQuery=sqlQuery, 

557 dbConn=self.transientsDbConn, 

558 quiet=False 

559 ) 

560 

561 self.log.debug( 

562 'completed the ``_get_transient_metadata_from_database_list`` method') 

563 return transientsMetadataList 

564 

565 def _update_ned_stream( 

566 self, 

567 transientsMetadataList 

568 ): 

569 """ update the NED stream within the catalogues database at the locations of the transients 

570 

571 **Key Arguments** 

572 

573 - ``transientsMetadataList`` -- the list of transient metadata lifted from the database. 

574 

575 

576 .. todo :: 

577 

578 - update key arguments values and definitions with defaults 

579 - update return values and definitions 

580 - update usage examples and text 

581 - update docstring text 

582 - check sublime snippet exists 

583 - clip any useful text to docs mindmap 

584 - regenerate the docs and check redendering of this docstring 

585 """ 

586 self.log.debug('starting the ``_update_ned_stream`` method') 

587 

588 coordinateList = [] 

589 for i in transientsMetadataList: 

590 # thisList = str(i["ra"]) + " " + str(i["dec"]) 

591 thisList = (i["ra"], i["dec"]) 

592 coordinateList.append(thisList) 

593 

594 coordinateList = self._remove_previous_ned_queries( 

595 coordinateList=coordinateList 

596 ) 

597 

598 # MINIMISE COORDINATES IN LIST TO REDUCE NUMBER OF REQUIRE NED QUERIES 

599 coordinateList = self._consolidate_coordinateList( 

600 coordinateList=coordinateList 

601 ) 

602 

603 stream = ned( 

604 log=self.log, 

605 settings=self.settings, 

606 coordinateList=coordinateList, 

607 radiusArcsec=self.settings["ned stream search radius arcec"] 

608 ) 

609 stream.ingest() 

610 

611 sqlQuery = """SET session sql_mode = "";""" % locals( 

612 ) 

613 writequery( 

614 log=self.log, 

615 sqlQuery=sqlQuery, 

616 dbConn=self.cataloguesDbConn 

617 ) 

618 sqlQuery = """update tcs_cat_ned_stream set magnitude = CAST(`magnitude_filter` AS DECIMAL(5,2)) where magnitude is null and magnitude_filter is not null;""" % locals( 

619 ) 

620 writequery( 

621 log=self.log, 

622 sqlQuery=sqlQuery, 

623 dbConn=self.cataloguesDbConn 

624 ) 

625 

626 self.log.debug('completed the ``_update_ned_stream`` method') 

627 return None 

628 

629 def _remove_previous_ned_queries( 

630 self, 

631 coordinateList): 

632 """iterate through the transient locations to see if we have recent local NED coverage of that area already 

633 

634 **Key Arguments** 

635 

636 - ``coordinateList`` -- set of coordinate to check for previous queries 

637 

638 

639 **Return** 

640 

641 - ``updatedCoordinateList`` -- coordinate list with previous queries removed 

642 

643 

644 .. todo :: 

645 

646 - update key arguments values and definitions with defaults 

647 - update return values and definitions 

648 - update usage examples and text 

649 - update docstring text 

650 - check sublime snippet exists 

651 - clip any useful text to docs mindmap 

652 - regenerate the docs and check redendering of this docstring 

653 """ 

654 self.log.debug('starting the ``_remove_previous_ned_queries`` method') 

655 

656 # 1 DEGREE QUERY RADIUS 

657 radius = 60. * 60. 

658 updatedCoordinateList = [] 

659 keepers = [] 

660 

661 # CALCULATE THE OLDEST RESULTS LIMIT 

662 now = datetime.now() 

663 td = timedelta( 

664 days=self.settings["ned stream refresh rate in days"]) 

665 refreshLimit = now - td 

666 refreshLimit = refreshLimit.strftime("%Y-%m-%d %H:%M:%S") 

667 

668 raList = [] 

669 raList[:] = [c[0] for c in coordinateList] 

670 decList = [] 

671 decList[:] = [c[1] for c in coordinateList] 

672 

673 # MATCH COORDINATES AGAINST PREVIOUS NED SEARCHES 

674 cs = conesearch( 

675 log=self.log, 

676 dbConn=self.cataloguesDbConn, 

677 tableName="tcs_helper_ned_query_history", 

678 columns="*", 

679 ra=raList, 

680 dec=decList, 

681 radiusArcsec=radius, 

682 separations=True, 

683 distinct=True, 

684 sqlWhere="dateQueried > '%(refreshLimit)s'" % locals(), 

685 closest=False 

686 ) 

687 matchIndies, matches = cs.search() 

688 

689 # DETERMINE WHICH COORDINATES REQUIRE A NED QUERY 

690 curatedMatchIndices = [] 

691 curatedMatches = [] 

692 for i, m in zip(matchIndies, matches.list): 

693 match = False 

694 row = m 

695 row["separationArcsec"] = row["cmSepArcsec"] 

696 raStream = row["raDeg"] 

697 decStream = row["decDeg"] 

698 radiusStream = row["arcsecRadius"] 

699 dateStream = row["dateQueried"] 

700 angularSeparation = row["separationArcsec"] 

701 

702 if angularSeparation + self.settings["first pass ned search radius arcec"] < radiusStream: 

703 curatedMatchIndices.append(i) 

704 curatedMatches.append(m) 

705 

706 # NON MATCHES 

707 for i, v in enumerate(coordinateList): 

708 if i not in curatedMatchIndices: 

709 updatedCoordinateList.append(v) 

710 

711 self.log.debug('completed the ``_remove_previous_ned_queries`` method') 

712 return updatedCoordinateList 

713 

714 def _update_transient_database( 

715 self, 

716 crossmatches, 

717 classifications, 

718 transientsMetadataList, 

719 colMaps): 

720 """ update transient database with classifications and crossmatch results 

721 

722 **Key Arguments** 

723 

724 - ``crossmatches`` -- the crossmatches and associations resulting from the catlaogue crossmatches 

725 - ``classifications`` -- the classifications assigned to the transients post-crossmatches (dictionary of rank ordered list of classifications) 

726 - ``transientsMetadataList`` -- the list of transient metadata lifted from the database. 

727 - ``colMaps`` -- maps of the important column names for each table/view in the crossmatch-catalogues database 

728 

729 

730 .. todo :: 

731 

732 - update key arguments values and definitions with defaults 

733 - update return values and definitions 

734 - update usage examples and text 

735 - update docstring text 

736 - check sublime snippet exists 

737 - clip any useful text to docs mindmap 

738 - regenerate the docs and check redendering of this docstring 

739 """ 

740 

741 self.log.debug('starting the ``_update_transient_database`` method') 

742 

743 import time 

744 start_time = time.time() 

745 print("UPDATING TRANSIENTS DATABASE WITH RESULTS") 

746 print("DELETING OLD RESULTS") 

747 

748 now = datetime.now() 

749 now = now.strftime("%Y-%m-%d_%H-%M-%S-%f") 

750 

751 transientTable = self.settings["database settings"][ 

752 "transients"]["transient table"] 

753 transientTableClassCol = self.settings["database settings"][ 

754 "transients"]["transient classification column"] 

755 transientTableIdCol = self.settings["database settings"][ 

756 "transients"]["transient primary id column"] 

757 

758 # COMBINE ALL CROSSMATCHES INTO A LIST OF DICTIONARIES TO DUMP INTO 

759 # DATABASE TABLE 

760 transientIDs = [str(c) 

761 for c in list(classifications.keys())] 

762 transientIDs = ",".join(transientIDs) 

763 

764 # REMOVE PREVIOUS MATCHES 

765 sqlQuery = """delete from sherlock_crossmatches where transient_object_id in (%(transientIDs)s);""" % locals( 

766 ) 

767 writequery( 

768 log=self.log, 

769 sqlQuery=sqlQuery, 

770 dbConn=self.transientsDbConn, 

771 ) 

772 sqlQuery = """delete from sherlock_classifications where transient_object_id in (%(transientIDs)s);""" % locals( 

773 ) 

774 writequery( 

775 log=self.log, 

776 sqlQuery=sqlQuery, 

777 dbConn=self.transientsDbConn, 

778 ) 

779 

780 print("FINISHED DELETING OLD RESULTS/ADDING TO CROSSMATCHES: %d" % 

781 (time.time() - start_time,)) 

782 start_time = time.time() 

783 

784 if len(crossmatches): 

785 insert_list_of_dictionaries_into_database_tables( 

786 dbConn=self.transientsDbConn, 

787 log=self.log, 

788 dictList=crossmatches, 

789 dbTableName="sherlock_crossmatches", 

790 dateModified=True, 

791 batchSize=10000, 

792 replace=True, 

793 dbSettings=self.settings["database settings"][ 

794 "transients"] 

795 ) 

796 

797 print("FINISHED ADDING TO CROSSMATCHES/UPDATING CLASSIFICATIONS IN TRANSIENT TABLE: %d" % 

798 (time.time() - start_time,)) 

799 start_time = time.time() 

800 

801 sqlQuery = "" 

802 inserts = [] 

803 for k, v in list(classifications.items()): 

804 thisInsert = { 

805 "transient_object_id": k, 

806 "classification": v[0] 

807 } 

808 inserts.append(thisInsert) 

809 

810 print("FINISHED UPDATING CLASSIFICATIONS IN TRANSIENT TABLE/UPDATING sherlock_classifications TABLE: %d" % 

811 (time.time() - start_time,)) 

812 start_time = time.time() 

813 

814 insert_list_of_dictionaries_into_database_tables( 

815 dbConn=self.transientsDbConn, 

816 log=self.log, 

817 dictList=inserts, 

818 dbTableName="sherlock_classifications", 

819 dateModified=True, 

820 batchSize=10000, 

821 replace=True, 

822 dbSettings=self.settings["database settings"][ 

823 "transients"] 

824 ) 

825 

826 print("FINISHED UPDATING sherlock_classifications TABLE: %d" % 

827 (time.time() - start_time,)) 

828 start_time = time.time() 

829 

830 self.log.debug('completed the ``_update_transient_database`` method') 

831 return None 

832 

833 def _rank_classifications( 

834 self, 

835 crossmatchArray, 

836 colMaps): 

837 """*rank the classifications returned from the catalogue crossmatcher, annotate the results with a classification rank-number (most likely = 1) and a rank-score (weight of classification)* 

838 

839 **Key Arguments** 

840 

841 - ``crossmatchArrayIndex`` -- the index of list of unranked crossmatch classifications 

842 - ``colMaps`` -- dictionary of dictionaries with the name of the database-view (e.g. `tcs_view_agn_milliquas_v4_5`) as the key and the column-name dictary map as value (`{view_name: {columnMap}}`). 

843 

844 

845 **Return** 

846 

847 - ``classifications`` -- the classifications assigned to the transients post-crossmatches 

848 - ``crossmatches`` -- the crossmatches annotated with rankings and rank-scores 

849 

850 

851 .. todo :: 

852 

853 - update key arguments values and definitions with defaults 

854 - update return values and definitions 

855 - update usage examples and text 

856 - update docstring text 

857 - check sublime snippet exists 

858 - clip any useful text to docs mindmap 

859 - regenerate the docs and check redendering of this docstring 

860 """ 

861 self.log.debug('starting the ``_rank_classifications`` method') 

862 

863 crossmatches = crossmatchArray 

864 

865 # GROUP CROSSMATCHES INTO DISTINCT SOURCES (DUPLICATE ENTRIES OF THE 

866 # SAME ASTROPHYSICAL SOURCE ACROSS MULTIPLE CATALOGUES) 

867 ra, dec = list(zip(*[(r["raDeg"], r["decDeg"]) for r in crossmatches])) 

868 

869 from HMpTy.htm import sets 

870 xmatcher = sets( 

871 log=self.log, 

872 ra=ra, 

873 dec=dec, 

874 radius=1. / (60. * 60.), # in degrees 

875 sourceList=crossmatches 

876 ) 

877 groupedMatches = xmatcher.match 

878 

879 associatationTypeOrder = ["AGN", "CV", "NT", "SN", "VS", "BS"] 

880 

881 # ADD DISTINCT-SOURCE KEY 

882 dupKey = 0 

883 distinctMatches = [] 

884 for x in groupedMatches: 

885 dupKey += 1 

886 mergedMatch = copy.deepcopy(x[0]) 

887 mergedMatch["merged_rank"] = int(dupKey) 

888 

889 # ADD OTHER ESSENTIAL KEYS 

890 for e in ['z', 'photoZ', 'photoZErr']: 

891 if e not in mergedMatch: 

892 mergedMatch[e] = None 

893 bestQualityCatalogue = colMaps[mergedMatch[ 

894 "catalogue_view_name"]]["object_type_accuracy"] 

895 bestDirectDistance = { 

896 "direct_distance": mergedMatch["direct_distance"], 

897 "direct_distance_modulus": mergedMatch["direct_distance_modulus"], 

898 "direct_distance_scale": mergedMatch["direct_distance_scale"], 

899 "qual": colMaps[mergedMatch["catalogue_view_name"]]["object_type_accuracy"] 

900 } 

901 if not mergedMatch["direct_distance"]: 

902 bestDirectDistance["qual"] = 0 

903 

904 bestSpecz = { 

905 "z": mergedMatch["z"], 

906 "distance": mergedMatch["distance"], 

907 "distance_modulus": mergedMatch["distance_modulus"], 

908 "scale": mergedMatch["scale"], 

909 "qual": colMaps[mergedMatch["catalogue_view_name"]]["object_type_accuracy"] 

910 } 

911 if not mergedMatch["distance"]: 

912 bestSpecz["qual"] = 0 

913 

914 bestPhotoz = { 

915 "photoZ": mergedMatch["photoZ"], 

916 "photoZErr": mergedMatch["photoZErr"], 

917 "qual": colMaps[mergedMatch["catalogue_view_name"]]["object_type_accuracy"] 

918 } 

919 if not mergedMatch["photoZ"]: 

920 bestPhotoz["qual"] = 0 

921 

922 # ORDER THESE FIRST IN NAME LISTING 

923 mergedMatch["search_name"] = None 

924 mergedMatch["catalogue_object_id"] = None 

925 primeCats = ["NED", "SDSS", "MILLIQUAS"] 

926 for cat in primeCats: 

927 for i, m in enumerate(x): 

928 # MERGE SEARCH NAMES 

929 snippet = m["search_name"].split(" ")[0].upper() 

930 if cat.upper() in snippet: 

931 if not mergedMatch["search_name"]: 

932 mergedMatch["search_name"] = m["search_name"].split(" ")[ 

933 0].upper() 

934 elif "/" not in mergedMatch["search_name"] and snippet not in mergedMatch["search_name"].upper(): 

935 mergedMatch["search_name"] = mergedMatch["search_name"].split( 

936 " ")[0].upper() + "/" + m["search_name"].split(" ")[0].upper() 

937 elif snippet not in mergedMatch["search_name"].upper(): 

938 mergedMatch[ 

939 "search_name"] += "/" + m["search_name"].split(" ")[0].upper() 

940 elif "/" not in mergedMatch["search_name"]: 

941 mergedMatch["search_name"] = mergedMatch["search_name"].split( 

942 " ")[0].upper() 

943 mergedMatch["catalogue_table_name"] = mergedMatch[ 

944 "search_name"] 

945 

946 # MERGE CATALOGUE SOURCE NAMES 

947 if not mergedMatch["catalogue_object_id"]: 

948 mergedMatch["catalogue_object_id"] = str( 

949 m["catalogue_object_id"]) 

950 

951 # NOW ADD THE REST 

952 for i, m in enumerate(x): 

953 # MERGE SEARCH NAMES 

954 snippet = m["search_name"].split(" ")[0].upper() 

955 if snippet not in primeCats: 

956 if not mergedMatch["search_name"]: 

957 mergedMatch["search_name"] = m["search_name"].split(" ")[ 

958 0].upper() 

959 elif "/" not in mergedMatch["search_name"] and snippet not in mergedMatch["search_name"].upper(): 

960 mergedMatch["search_name"] = mergedMatch["search_name"].split( 

961 " ")[0].upper() + "/" + m["search_name"].split(" ")[0].upper() 

962 elif snippet not in mergedMatch["search_name"].upper(): 

963 mergedMatch[ 

964 "search_name"] += "/" + m["search_name"].split(" ")[0].upper() 

965 elif "/" not in mergedMatch["search_name"]: 

966 mergedMatch["search_name"] = mergedMatch["search_name"].split( 

967 " ")[0].upper() 

968 mergedMatch["catalogue_table_name"] = mergedMatch[ 

969 "search_name"] 

970 

971 # MERGE CATALOGUE SOURCE NAMES 

972 if not mergedMatch["catalogue_object_id"]: 

973 mergedMatch["catalogue_object_id"] = str( 

974 m["catalogue_object_id"]) 

975 # else: 

976 # mergedMatch["catalogue_object_id"] = str( 

977 # mergedMatch["catalogue_object_id"]) 

978 # m["catalogue_object_id"] = str( 

979 # m["catalogue_object_id"]) 

980 # if m["catalogue_object_id"].replace(" ", "").lower() not in mergedMatch["catalogue_object_id"].replace(" ", "").lower(): 

981 # mergedMatch["catalogue_object_id"] += "/" + \ 

982 # m["catalogue_object_id"] 

983 

984 for i, m in enumerate(x): 

985 m["merged_rank"] = int(dupKey) 

986 if i > 0: 

987 # MERGE ALL BEST MAGNITUDE MEASUREMENTS 

988 for f in self.filterPreference: 

989 

990 if f in m and m[f] and (f not in mergedMatch or (f + "Err" in mergedMatch and f + "Err" in m and (mergedMatch[f + "Err"] == None or (m[f + "Err"] and mergedMatch[f + "Err"] > m[f + "Err"])))): 

991 mergedMatch[f] = m[f] 

992 try: 

993 mergedMatch[f + "Err"] = m[f + "Err"] 

994 except: 

995 pass 

996 mergedMatch["original_search_radius_arcsec"] = "multiple" 

997 mergedMatch["catalogue_object_subtype"] = "multiple" 

998 mergedMatch["catalogue_view_name"] = "multiple" 

999 

1000 # DETERMINE BEST CLASSIFICATION 

1001 if mergedMatch["classificationReliability"] == 3 and m["classificationReliability"] < 3: 

1002 mergedMatch["association_type"] = m["association_type"] 

1003 mergedMatch["catalogue_object_type"] = m[ 

1004 "catalogue_object_type"] 

1005 mergedMatch["classificationReliability"] = m[ 

1006 "classificationReliability"] 

1007 

1008 if m["classificationReliability"] != 3 and colMaps[m["catalogue_view_name"]]["object_type_accuracy"] > bestQualityCatalogue: 

1009 bestQualityCatalogue = colMaps[ 

1010 m["catalogue_view_name"]]["object_type_accuracy"] 

1011 mergedMatch["association_type"] = m["association_type"] 

1012 mergedMatch["catalogue_object_type"] = m[ 

1013 "catalogue_object_type"] 

1014 mergedMatch["classificationReliability"] = m[ 

1015 "classificationReliability"] 

1016 

1017 if m["classificationReliability"] != 3 and colMaps[m["catalogue_view_name"]]["object_type_accuracy"] == bestQualityCatalogue and m["association_type"] in associatationTypeOrder and (mergedMatch["association_type"] not in associatationTypeOrder or associatationTypeOrder.index(m["association_type"]) < associatationTypeOrder.index(mergedMatch["association_type"])): 

1018 mergedMatch["association_type"] = m["association_type"] 

1019 mergedMatch["catalogue_object_type"] = m[ 

1020 "catalogue_object_type"] 

1021 mergedMatch["classificationReliability"] = m[ 

1022 "classificationReliability"] 

1023 

1024 # FIND BEST DISTANCES 

1025 if "direct_distance" in m and m["direct_distance"] and colMaps[m["catalogue_view_name"]]["object_type_accuracy"] > bestDirectDistance["qual"]: 

1026 bestDirectDistance = { 

1027 "direct_distance": m["direct_distance"], 

1028 "direct_distance_modulus": m["direct_distance_modulus"], 

1029 "direct_distance_scale": m["direct_distance_scale"], 

1030 "catalogue_object_type": m["catalogue_object_type"], 

1031 "qual": colMaps[m["catalogue_view_name"]]["object_type_accuracy"] 

1032 } 

1033 # FIND BEST SPEC-Z 

1034 if "z" in m and m["z"] and colMaps[m["catalogue_view_name"]]["object_type_accuracy"] > bestSpecz["qual"]: 

1035 bestSpecz = { 

1036 "z": m["z"], 

1037 "distance": m["distance"], 

1038 "distance_modulus": m["distance_modulus"], 

1039 "scale": m["scale"], 

1040 "catalogue_object_type": m["catalogue_object_type"], 

1041 "qual": colMaps[m["catalogue_view_name"]]["object_type_accuracy"] 

1042 } 

1043 # FIND BEST PHOT-Z 

1044 if "photoZ" in m and m["photoZ"] and colMaps[m["catalogue_view_name"]]["object_type_accuracy"] > bestPhotoz["qual"]: 

1045 bestPhotoz = { 

1046 "photoZ": m["photoZ"], 

1047 "photoZErr": m["photoZErr"], 

1048 "distance": m["distance"], 

1049 "distance_modulus": m["distance_modulus"], 

1050 "scale": m["scale"], 

1051 "catalogue_object_type": m["catalogue_object_type"], 

1052 "qual": colMaps[m["catalogue_view_name"]]["object_type_accuracy"] 

1053 } 

1054 # CLOSEST ANGULAR SEP & COORDINATES 

1055 if m["separationArcsec"] < mergedMatch["separationArcsec"]: 

1056 mergedMatch["separationArcsec"] = m["separationArcsec"] 

1057 mergedMatch["raDeg"] = m["raDeg"] 

1058 mergedMatch["decDeg"] = m["decDeg"] 

1059 

1060 # MERGE THE BEST RESULTS 

1061 for l in [bestPhotoz, bestSpecz, bestDirectDistance]: 

1062 for k, v in list(l.items()): 

1063 if k != "qual" and v: 

1064 mergedMatch[k] = v 

1065 

1066 mergedMatch["catalogue_object_id"] = str(mergedMatch[ 

1067 "catalogue_object_id"]).replace(" ", "") 

1068 

1069 # RECALULATE PHYSICAL DISTANCE SEPARATION 

1070 if mergedMatch["direct_distance_scale"]: 

1071 mergedMatch["physical_separation_kpc"] = mergedMatch[ 

1072 "direct_distance_scale"] * mergedMatch["separationArcsec"] 

1073 

1074 elif mergedMatch["scale"]: 

1075 mergedMatch["physical_separation_kpc"] = mergedMatch[ 

1076 "scale"] * mergedMatch["separationArcsec"] 

1077 

1078 if "/" in mergedMatch["search_name"]: 

1079 mergedMatch["search_name"] = "multiple" 

1080 

1081 distinctMatches.append(mergedMatch) 

1082 

1083 crossmatches = [] 

1084 for xm, gm in zip(distinctMatches, groupedMatches): 

1085 # SPEC-Z GALAXIES 

1086 if (xm["physical_separation_kpc"] is not None and xm["physical_separation_kpc"] != "null" and xm["physical_separation_kpc"] < 20. and (("z" in xm and xm["z"] is not None) or "photoZ" not in xm or xm["photoZ"] is None or xm["photoZ"] < 0.)): 

1087 rankScore = xm["classificationReliability"] * 1000 + 2. - \ 

1088 (50 - old_div(xm["physical_separation_kpc"], 20)) 

1089 # PHOTO-Z GALAXIES 

1090 elif (xm["physical_separation_kpc"] is not None and xm["physical_separation_kpc"] != "null" and xm["physical_separation_kpc"] < 20. and xm["association_type"] == "SN"): 

1091 rankScore = xm["classificationReliability"] * 1000 + 5 - \ 

1092 (50 - old_div(xm["physical_separation_kpc"], 20)) 

1093 # NOT SPEC-Z, NON PHOTO-Z GALAXIES & PHOTO-Z GALAXIES 

1094 elif (xm["association_type"] == "SN"): 

1095 rankScore = xm["classificationReliability"] * 1000 + 5. 

1096 # VS 

1097 elif (xm["association_type"] == "VS"): 

1098 rankScore = xm["classificationReliability"] * \ 

1099 1000 + xm["separationArcsec"] + 2. 

1100 # BS 

1101 elif (xm["association_type"] == "BS"): 

1102 rankScore = xm["classificationReliability"] * \ 

1103 1000 + xm["separationArcsec"] 

1104 else: 

1105 rankScore = xm["classificationReliability"] * \ 

1106 1000 + xm["separationArcsec"] + 10. 

1107 xm["rankScore"] = rankScore 

1108 crossmatches.append(xm) 

1109 if len(gm) > 1: 

1110 for g in gm: 

1111 g["rankScore"] = rankScore 

1112 

1113 crossmatches = sorted( 

1114 crossmatches, key=itemgetter('rankScore'), reverse=False) 

1115 crossmatches = sorted( 

1116 crossmatches, key=itemgetter('transient_object_id')) 

1117 

1118 transient_object_id = None 

1119 uniqueIndexCheck = [] 

1120 classifications = {} 

1121 crossmatchesKeep = [] 

1122 rank = 0 

1123 transClass = [] 

1124 for xm in crossmatches: 

1125 rank += 1 

1126 if rank == 1: 

1127 transClass.append(xm["association_type"]) 

1128 classifications[xm["transient_object_id"]] = transClass 

1129 if rank == 1 or self.lite == False: 

1130 xm["rank"] = rank 

1131 crossmatchesKeep.append(xm) 

1132 crossmatches = crossmatchesKeep 

1133 

1134 crossmatchesKeep = [] 

1135 if self.lite == False: 

1136 for xm in crossmatches: 

1137 group = groupedMatches[xm["merged_rank"] - 1] 

1138 xm["merged_rank"] = None 

1139 crossmatchesKeep.append(xm) 

1140 

1141 if len(group) > 1: 

1142 groupKeep = [] 

1143 uniqueIndexCheck = [] 

1144 for g in group: 

1145 g["merged_rank"] = xm["rank"] 

1146 g["rankScore"] = xm["rankScore"] 

1147 index = "%(catalogue_table_name)s%(catalogue_object_id)s" % g 

1148 # IF WE HAVE HIT A NEW SOURCE 

1149 if index not in uniqueIndexCheck: 

1150 uniqueIndexCheck.append(index) 

1151 crossmatchesKeep.append(g) 

1152 

1153 crossmatches = crossmatchesKeep 

1154 

1155 self.log.debug('completed the ``_rank_classifications`` method') 

1156 

1157 return classifications, crossmatches 

1158 

1159 def _print_results_to_stdout( 

1160 self, 

1161 classifications, 

1162 crossmatches): 

1163 """*print the classification and crossmatch results for a single transient object to stdout* 

1164 

1165 **Key Arguments** 

1166 

1167 - ``crossmatches`` -- the unranked crossmatch classifications 

1168 - ``classifications`` -- the classifications assigned to the transients post-crossmatches (dictionary of rank ordered list of classifications) 

1169 

1170 

1171 .. todo :: 

1172 

1173 - update key arguments values and definitions with defaults 

1174 - update return values and definitions 

1175 - update usage examples and text 

1176 - update docstring text 

1177 - check sublime snippet exists 

1178 - clip any useful text to docs mindmap 

1179 - regenerate the docs and check redendering of this docstring 

1180 """ 

1181 self.log.debug('starting the ``_print_results_to_stdout`` method') 

1182 

1183 if self.verbose == 0: 

1184 return 

1185 

1186 crossmatchesCopy = copy.deepcopy(crossmatches) 

1187 

1188 # REPORT ONLY THE MOST PREFERED MAGNITUDE VALUE 

1189 basic = ["association_type", "rank", "rankScore", "catalogue_table_name", "catalogue_object_id", "catalogue_object_type", "catalogue_object_subtype", 

1190 "raDeg", "decDeg", "separationArcsec", "physical_separation_kpc", "direct_distance", "distance", "z", "photoZ", "photoZErr", "Mag", "MagFilter", "MagErr", "classificationReliability", "merged_rank"] 

1191 verbose = ["search_name", "catalogue_view_name", "original_search_radius_arcsec", "direct_distance_modulus", "distance_modulus", "direct_distance_scale", "major_axis_arcsec", "scale", "U", "UErr", 

1192 "B", "BErr", "V", "VErr", "R", "RErr", "I", "IErr", "J", "JErr", "H", "HErr", "K", "KErr", "_u", "_uErr", "_g", "_gErr", "_r", "_rErr", "_i", "_iErr", "_z", "_zErr", "_y", "G", "GErr", "_yErr", "unkMag"] 

1193 dontFormat = ["decDeg", "raDeg", "rank", 

1194 "catalogue_object_id", "catalogue_object_subtype", "merged_rank"] 

1195 if self.verbose == 2: 

1196 basic = basic + verbose 

1197 

1198 for n in self.name: 

1199 

1200 if n in classifications: 

1201 headline = "\n" + n + "'s Predicted Classification: " + \ 

1202 classifications[n][0] 

1203 else: 

1204 headline = n + "'s Predicted Classification: ORPHAN" 

1205 print(headline) 

1206 print("Suggested Associations:") 

1207 

1208 myCrossmatches = [] 

1209 myCrossmatches[:] = [c for c in crossmatchesCopy if c[ 

1210 "transient_object_id"] == n] 

1211 

1212 for c in myCrossmatches: 

1213 for f in self.filterPreference: 

1214 if f in c and c[f]: 

1215 c["Mag"] = c[f] 

1216 c["MagFilter"] = f.replace("_", "").replace("Mag", "") 

1217 if f + "Err" in c: 

1218 c["MagErr"] = c[f + "Err"] 

1219 else: 

1220 c["MagErr"] = None 

1221 break 

1222 

1223 allKeys = [] 

1224 for c in myCrossmatches: 

1225 for k, v in list(c.items()): 

1226 if k not in allKeys: 

1227 allKeys.append(k) 

1228 

1229 for c in myCrossmatches: 

1230 for k in allKeys: 

1231 if k not in c: 

1232 c[k] = None 

1233 

1234 printCrossmatches = [] 

1235 for c in myCrossmatches: 

1236 ordDict = collections.OrderedDict(sorted({}.items())) 

1237 for k in basic: 

1238 if k in c: 

1239 if k == "catalogue_table_name": 

1240 c[k] = c[k].replace( 

1241 "tcs_cat_", "").replace("_", " ") 

1242 if k == "classificationReliability": 

1243 if c[k] == 1: 

1244 c["classification reliability"] = "synonym" 

1245 elif c[k] == 2: 

1246 c["classification reliability"] = "association" 

1247 elif c[k] == 3: 

1248 c["classification reliability"] = "annotation" 

1249 k = "classification reliability" 

1250 if k == "catalogue_object_subtype" and "sdss" in c["catalogue_table_name"]: 

1251 if c[k] == 6: 

1252 c[k] = "galaxy" 

1253 elif c[k] == 3: 

1254 c[k] = "star" 

1255 columnName = k.replace( 

1256 "tcs_cat_", "").replace("_", " ") 

1257 value = c[k] 

1258 if k not in dontFormat: 

1259 try: 

1260 ordDict[columnName] = "%(value)0.2f" % locals() 

1261 except: 

1262 ordDict[columnName] = value 

1263 else: 

1264 ordDict[columnName] = value 

1265 

1266 printCrossmatches.append(ordDict) 

1267 

1268 from fundamentals.renderer import list_of_dictionaries 

1269 dataSet = list_of_dictionaries( 

1270 log=self.log, 

1271 listOfDictionaries=printCrossmatches 

1272 ) 

1273 tableData = dataSet.table(filepath=None) 

1274 

1275 print(tableData) 

1276 

1277 self.log.debug('completed the ``_print_results_to_stdout`` method') 

1278 return None 

1279 

1280 def _lighten_return( 

1281 self, 

1282 crossmatches): 

1283 """*lighten the classification and crossmatch results for smaller database footprint* 

1284 

1285 **Key Arguments** 

1286 

1287 - ``classifications`` -- the classifications assigned to the transients post-crossmatches (dictionary of rank ordered list of classifications) 

1288 """ 

1289 self.log.debug('starting the ``_lighten_return`` method') 

1290 

1291 # REPORT ONLY THE MOST PREFERED MAGNITUDE VALUE 

1292 basic = ["transient_object_id", "association_type", "catalogue_table_name", "catalogue_object_id", "catalogue_object_type", 

1293 "raDeg", "decDeg", "separationArcsec", "northSeparationArcsec", "eastSeparationArcsec", "physical_separation_kpc", "direct_distance", "distance", "z", "photoZ", "photoZErr", "Mag", "MagFilter", "MagErr", "classificationReliability", "major_axis_arcsec"] 

1294 verbose = ["search_name", "catalogue_view_name", "original_search_radius_arcsec", "direct_distance_modulus", "distance_modulus", "direct_distance_scale", "scale", "U", "UErr", 

1295 "B", "BErr", "V", "VErr", "R", "RErr", "I", "IErr", "J", "JErr", "H", "HErr", "K", "KErr", "_u", "_uErr", "_g", "_gErr", "_r", "_rErr", "_i", "_iErr", "_z", "_zErr", "_y", "G", "GErr", "_yErr", "unkMag"] 

1296 dontFormat = ["decDeg", "raDeg", "rank", 

1297 "catalogue_object_id", "catalogue_object_subtype", "merged_rank", "classificationReliability"] 

1298 if self.verbose == 2: 

1299 basic = basic + verbose 

1300 

1301 for c in crossmatches: 

1302 for f in self.filterPreference: 

1303 if f in c and c[f]: 

1304 c["Mag"] = c[f] 

1305 c["MagFilter"] = f.replace("_", "").replace("Mag", "") 

1306 if f + "Err" in c: 

1307 c["MagErr"] = c[f + "Err"] 

1308 else: 

1309 c["MagErr"] = None 

1310 break 

1311 

1312 allKeys = [] 

1313 for c in crossmatches: 

1314 for k, v in list(c.items()): 

1315 if k not in allKeys: 

1316 allKeys.append(k) 

1317 

1318 for c in crossmatches: 

1319 for k in allKeys: 

1320 if k not in c: 

1321 c[k] = None 

1322 

1323 liteCrossmatches = [] 

1324 for c in crossmatches: 

1325 ordDict = collections.OrderedDict(sorted({}.items())) 

1326 for k in basic: 

1327 if k in c: 

1328 if k == "catalogue_table_name": 

1329 c[k] = c[k].replace( 

1330 "tcs_cat_", "").replace("_", " ") 

1331 if k == "catalogue_object_subtype" and "sdss" in c["catalogue_table_name"]: 

1332 if c[k] == 6: 

1333 c[k] = "galaxy" 

1334 elif c[k] == 3: 

1335 c[k] = "star" 

1336 columnName = k.replace( 

1337 "tcs_cat_", "") 

1338 value = c[k] 

1339 if k not in dontFormat: 

1340 try: 

1341 ordDict[columnName] = float(f'{value:0.2f}') 

1342 except: 

1343 ordDict[columnName] = value 

1344 else: 

1345 ordDict[columnName] = value 

1346 

1347 liteCrossmatches.append(ordDict) 

1348 

1349 self.log.debug('completed the ``_lighten_return`` method') 

1350 return liteCrossmatches 

1351 

1352 def _consolidate_coordinateList( 

1353 self, 

1354 coordinateList): 

1355 """*match the coordinate list against itself with the parameters of the NED search queries to minimise duplicated NED queries* 

1356 

1357 **Key Arguments** 

1358 

1359 - ``coordinateList`` -- the original coordinateList. 

1360 

1361 

1362 **Return** 

1363 

1364 - ``updatedCoordinateList`` -- the coordinate list with duplicated search areas removed 

1365 

1366 

1367 **Usage** 

1368 

1369 .. todo:: 

1370 

1371 - add usage info 

1372 - create a sublime snippet for usage 

1373 - update package tutorial if needed 

1374 

1375 ```python 

1376 usage code 

1377 ``` 

1378 

1379 

1380 .. todo :: 

1381 

1382 - update key arguments values and definitions with defaults 

1383 - update return values and definitions 

1384 - update usage examples and text 

1385 - update docstring text 

1386 - check sublime snippet exists 

1387 - clip any useful text to docs mindmap 

1388 - regenerate the docs and check redendering of this docstring 

1389 """ 

1390 self.log.debug('starting the ``_consolidate_coordinateList`` method') 

1391 

1392 raList = [] 

1393 raList[:] = np.array([c[0] for c in coordinateList]) 

1394 decList = [] 

1395 decList[:] = np.array([c[1] for c in coordinateList]) 

1396 

1397 nedStreamRadius = old_div(self.settings[ 

1398 "ned stream search radius arcec"], (60. * 60.)) 

1399 firstPassNedSearchRadius = old_div(self.settings[ 

1400 "first pass ned search radius arcec"], (60. * 60.)) 

1401 radius = nedStreamRadius - firstPassNedSearchRadius 

1402 

1403 # LET'S BE CONSERVATIVE 

1404 # radius = radius * 0.9 

1405 

1406 xmatcher = sets( 

1407 log=self.log, 

1408 ra=raList, 

1409 dec=decList, 

1410 radius=radius, # in degrees 

1411 sourceList=coordinateList, 

1412 convertToArray=False 

1413 ) 

1414 allMatches = xmatcher.match 

1415 

1416 updatedCoordianteList = [] 

1417 for aSet in allMatches: 

1418 updatedCoordianteList.append(aSet[0]) 

1419 

1420 self.log.debug('completed the ``_consolidate_coordinateList`` method') 

1421 return updatedCoordianteList 

1422 

1423 def classification_annotations( 

1424 self): 

1425 """*add a detialed classification annotation to each classification in the sherlock_classifications table* 

1426 

1427 **Key Arguments** 

1428 

1429 # - 

1430 

1431 

1432 **Return** 

1433 

1434 - None 

1435 

1436 

1437 **Usage** 

1438 

1439 .. todo:: 

1440 

1441 - add usage info 

1442 - create a sublime snippet for usage 

1443 - write a command-line tool for this method 

1444 - update package tutorial with command-line tool info if needed 

1445 

1446 ```python 

1447 usage code 

1448 ``` 

1449 

1450 

1451 .. todo :: 

1452 

1453 - update key arguments values and definitions with defaults 

1454 - update return values and definitions 

1455 - update usage examples and text 

1456 - update docstring text 

1457 - check sublime snippet exists 

1458 - clip any useful text to docs mindmap 

1459 - regenerate the docs and check redendering of this docstring 

1460 """ 

1461 self.log.debug('starting the ``classification_annotations`` method') 

1462 

1463 from fundamentals.mysql import readquery 

1464 sqlQuery = u""" 

1465 select * from sherlock_classifications cl, sherlock_crossmatches xm where cl.transient_object_id=xm.transient_object_id and cl.annotation is null 

1466 """ % locals() 

1467 topXMs = readquery( 

1468 log=self.log, 

1469 sqlQuery=sqlQuery, 

1470 dbConn=self.transientsDbConn 

1471 ) 

1472 

1473 for xm in topXMs: 

1474 annotation = [] 

1475 classType = xm["classificationReliability"] 

1476 if classType == 1: 

1477 annotation.append("is synonymous with") 

1478 elif classType in [2, 3]: 

1479 annotation.append("is possibly associated with") 

1480 

1481 self.log.debug('completed the ``classification_annotations`` method') 

1482 return None 

1483 

1484 def update_classification_annotations_and_summaries( 

1485 self, 

1486 updatePeakMagnitudes=True, 

1487 cl=False, 

1488 crossmatches=False, 

1489 classifications=False): 

1490 """*update classification annotations and summaries* 

1491 

1492 **Key Arguments** 

1493 

1494 - ``updatePeakMagnitudes`` -- update the peak magnitudes in the annotations to give absolute magnitudes. Default *True* 

1495 - ``cl`` -- reporting only to the command-line, do not update database. Default *False* 

1496 - ``crossmatches`` -- crossmatches will be passed for the single classifications to report annotations from command-line 

1497 - ``classifications`` -- classifications will be passed for the single classifications to have annotation appended to the dictionary for stand-alone non-database scripts 

1498 

1499 **Return** 

1500 

1501 - None 

1502 

1503 

1504 **Usage** 

1505 

1506 .. todo:: 

1507 

1508 - add usage info 

1509 - create a sublime snippet for usage 

1510 - write a command-line tool for this method 

1511 - update package tutorial with command-line tool info if needed 

1512 

1513 ```python 

1514 usage code 

1515 ``` 

1516 

1517 

1518 .. todo :: 

1519 

1520 - update key arguments values and definitions with defaults 

1521 - update return values and definitions 

1522 - update usage examples and text 

1523 - update docstring text 

1524 - check sublime snippet exists 

1525 - clip any useful text to docs mindmap 

1526 - regenerate the docs and check redendering of this docstring 

1527 """ 

1528 self.log.debug( 

1529 'starting the ``update_classification_annotations_and_summaries`` method') 

1530 

1531 # import time 

1532 # start_time = time.time() 

1533 # print "COLLECTING TRANSIENTS WITH NO ANNOTATIONS" 

1534 

1535 # BULK RUN 

1536 if crossmatches == False: 

1537 if updatePeakMagnitudes: 

1538 sqlQuery = u""" 

1539 SELECT * from sherlock_crossmatches cm, sherlock_classifications cl where rank =1 and cl.transient_object_id= cm.transient_object_id and ((cl.classification not in ("AGN","CV","BS","VS") AND cm.dateLastModified > DATE_SUB(NOW(), INTERVAL 1 Day)) or cl.annotation is null) 

1540 -- SELECT * from sherlock_crossmatches cm, sherlock_classifications cl where rank =1 and cl.transient_object_id= cm.transient_object_id and (cl.annotation is null or cl.dateLastModified is null or cl.dateLastModified > DATE_SUB(NOW(), INTERVAL 30 DAY)) order by cl.dateLastModified asc limit 100000 

1541 """ % locals() 

1542 else: 

1543 sqlQuery = u""" 

1544 SELECT * from sherlock_crossmatches cm, sherlock_classifications cl where rank =1 and cl.transient_object_id=cm.transient_object_id and cl.summary is null 

1545 """ % locals() 

1546 

1547 rows = readquery( 

1548 log=self.log, 

1549 sqlQuery=sqlQuery, 

1550 dbConn=self.transientsDbConn 

1551 ) 

1552 # COMMAND-LINE SINGLE CLASSIFICATION 

1553 else: 

1554 rows = crossmatches 

1555 

1556 # print "FINISHED COLLECTING TRANSIENTS WITH NO ANNOTATIONS/GENERATING ANNOTATIONS: %d" % (time.time() - start_time,) 

1557 # start_time = time.time() 

1558 

1559 updates = [] 

1560 

1561 for row in rows: 

1562 annotation, summary, sep = self.generate_match_annotation( 

1563 match=row, updatePeakMagnitudes=updatePeakMagnitudes) 

1564 

1565 if cl and "rank" in row and row["rank"] == 1: 

1566 if classifications != False: 

1567 classifications[ 

1568 row["transient_object_id"]].append(annotation) 

1569 if self.verbose != 0: 

1570 print(annotation) 

1571 

1572 update = { 

1573 "transient_object_id": row["transient_object_id"], 

1574 "annotation": annotation, 

1575 "summary": summary, 

1576 "separationArcsec": sep 

1577 } 

1578 updates.append(update) 

1579 

1580 if cl: 

1581 return classifications 

1582 

1583 # print "FINISHED GENERATING ANNOTATIONS/ADDING ANNOTATIONS TO TRANSIENT DATABASE: %d" % (time.time() - start_time,) 

1584 # start_time = time.time() 

1585 

1586 insert_list_of_dictionaries_into_database_tables( 

1587 dbConn=self.transientsDbConn, 

1588 log=self.log, 

1589 dictList=updates, 

1590 dbTableName="sherlock_classifications", 

1591 dateModified=True, 

1592 batchSize=10000, 

1593 replace=True, 

1594 dbSettings=self.settings["database settings"]["transients"] 

1595 ) 

1596 

1597 # print "FINISHED ADDING ANNOTATIONS TO TRANSIENT DATABASE/UPDATING ORPHAN ANNOTATIONS: %d" % (time.time() - start_time,) 

1598 # start_time = time.time() 

1599 

1600 sqlQuery = """update sherlock_classifications set annotation = "The transient location is not matched against any known catalogued source", summary = "No catalogued match" where classification = 'ORPHAN' and summary is null """ % locals() 

1601 writequery( 

1602 log=self.log, 

1603 sqlQuery=sqlQuery, 

1604 dbConn=self.transientsDbConn, 

1605 ) 

1606 

1607 # print "FINISHED UPDATING ORPHAN ANNOTATIONS: %d" % (time.time() - start_time,) 

1608 # start_time = time.time() 

1609 

1610 self.log.debug( 

1611 'completed the ``update_classification_annotations_and_summaries`` method') 

1612 return None 

1613 

1614 # use the tab-trigger below for new method 

1615 def update_peak_magnitudes( 

1616 self): 

1617 """*update peak magnitudes* 

1618 

1619 **Key Arguments** 

1620 

1621 # - 

1622 

1623 

1624 **Return** 

1625 

1626 - None 

1627 

1628 

1629 **Usage** 

1630 

1631 .. todo:: 

1632 

1633 - add usage info 

1634 - create a sublime snippet for usage 

1635 - write a command-line tool for this method 

1636 - update package tutorial with command-line tool info if needed 

1637 

1638 ```python 

1639 usage code 

1640 ``` 

1641 

1642 

1643 .. todo :: 

1644 

1645 - update key arguments values and definitions with defaults 

1646 - update return values and definitions 

1647 - update usage examples and text 

1648 - update docstring text 

1649 - check sublime snippet exists 

1650 - clip any useful text to docs mindmap 

1651 - regenerate the docs and check redendering of this docstring 

1652 """ 

1653 self.log.debug('starting the ``update_peak_magnitudes`` method') 

1654 

1655 sqlQuery = self.settings["database settings"][ 

1656 "transients"]["transient peak magnitude query"] 

1657 

1658 sqlQuery = """UPDATE sherlock_crossmatches s, 

1659 (%(sqlQuery)s) t 

1660 SET 

1661 s.transientAbsMag = ROUND(t.mag - IFNULL(direct_distance_modulus, 

1662 distance_modulus), 

1663 2) 

1664 WHERE 

1665 IFNULL(direct_distance_modulus, 

1666 distance_modulus) IS NOT NULL 

1667 AND (s.association_type not in ("AGN","CV","BS","VS") 

1668 or s.transientAbsMag is null) 

1669 AND t.id = s.transient_object_id 

1670 AND (s.dateLastModified > DATE_SUB(NOW(), INTERVAL 1 DAY));""" % locals() 

1671 

1672 writequery( 

1673 log=self.log, 

1674 sqlQuery=sqlQuery, 

1675 dbConn=self.transientsDbConn, 

1676 ) 

1677 

1678 self.log.debug('completed the ``update_peak_magnitudes`` method') 

1679 return None 

1680 

1681 def _create_tables_if_not_exist( 

1682 self): 

1683 """*create the sherlock helper tables if they don't yet exist* 

1684 

1685 **Key Arguments** 

1686 

1687 # - 

1688 

1689 

1690 **Return** 

1691 

1692 - None 

1693 

1694 

1695 **Usage** 

1696 

1697 .. todo:: 

1698 

1699 - add usage info 

1700 - create a sublime snippet for usage 

1701 - write a command-line tool for this method 

1702 - update package tutorial with command-line tool info if needed 

1703 

1704 ```python 

1705 usage code 

1706 ``` 

1707 

1708 

1709 .. todo :: 

1710 

1711 - update key arguments values and definitions with defaults 

1712 - update return values and definitions 

1713 - update usage examples and text 

1714 - update docstring text 

1715 - check sublime snippet exists 

1716 - clip any useful text to docs mindmap 

1717 - regenerate the docs and check redendering of this docstring 

1718 """ 

1719 self.log.debug('starting the ``_create_tables_if_not_exist`` method') 

1720 

1721 transientTable = self.settings["database settings"][ 

1722 "transients"]["transient table"] 

1723 transientTableClassCol = self.settings["database settings"][ 

1724 "transients"]["transient classification column"] 

1725 transientTableIdCol = self.settings["database settings"][ 

1726 "transients"]["transient primary id column"] 

1727 

1728 crossmatchTable = "sherlock_crossmatches" 

1729 createStatement = """ 

1730CREATE TABLE IF NOT EXISTS `sherlock_crossmatches` ( 

1731 `transient_object_id` bigint(20) unsigned DEFAULT NULL, 

1732 `catalogue_object_id` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, 

1733 `catalogue_table_id` smallint(5) unsigned DEFAULT NULL, 

1734 `separationArcsec` double DEFAULT NULL, 

1735 `northSeparationArcsec` double DEFAULT NULL, 

1736 `eastSeparationArcsec` double DEFAULT NULL, 

1737 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 

1738 `z` double DEFAULT NULL, 

1739 `scale` double DEFAULT NULL, 

1740 `distance` double DEFAULT NULL, 

1741 `distance_modulus` double DEFAULT NULL, 

1742 `photoZ` double DEFAULT NULL, 

1743 `photoZErr` double DEFAULT NULL, 

1744 `association_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, 

1745 `dateCreated` datetime DEFAULT NULL, 

1746 `physical_separation_kpc` double DEFAULT NULL, 

1747 `catalogue_object_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, 

1748 `catalogue_object_subtype` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, 

1749 `association_rank` int(11) DEFAULT NULL, 

1750 `catalogue_table_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, 

1751 `catalogue_view_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, 

1752 `rank` int(11) DEFAULT NULL, 

1753 `rankScore` double DEFAULT NULL, 

1754 `search_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, 

1755 `major_axis_arcsec` double DEFAULT NULL, 

1756 `direct_distance` double DEFAULT NULL, 

1757 `direct_distance_scale` double DEFAULT NULL, 

1758 `direct_distance_modulus` double DEFAULT NULL, 

1759 `raDeg` double DEFAULT NULL, 

1760 `decDeg` double DEFAULT NULL, 

1761 `original_search_radius_arcsec` double DEFAULT NULL, 

1762 `catalogue_view_id` int(11) DEFAULT NULL, 

1763 `U` double DEFAULT NULL, 

1764 `UErr` double DEFAULT NULL, 

1765 `B` double DEFAULT NULL, 

1766 `BErr` double DEFAULT NULL, 

1767 `V` double DEFAULT NULL, 

1768 `VErr` double DEFAULT NULL, 

1769 `R` double DEFAULT NULL, 

1770 `RErr` double DEFAULT NULL, 

1771 `I` double DEFAULT NULL, 

1772 `IErr` double DEFAULT NULL, 

1773 `J` double DEFAULT NULL, 

1774 `JErr` double DEFAULT NULL, 

1775 `H` double DEFAULT NULL, 

1776 `HErr` double DEFAULT NULL, 

1777 `K` double DEFAULT NULL, 

1778 `KErr` double DEFAULT NULL, 

1779 `_u` double DEFAULT NULL, 

1780 `_uErr` double DEFAULT NULL, 

1781 `_g` double DEFAULT NULL, 

1782 `_gErr` double DEFAULT NULL, 

1783 `_r` double DEFAULT NULL, 

1784 `_rErr` double DEFAULT NULL, 

1785 `_i` double DEFAULT NULL, 

1786 `_iErr` double DEFAULT NULL, 

1787 `_z` double DEFAULT NULL, 

1788 `_zErr` double DEFAULT NULL, 

1789 `_y` double DEFAULT NULL, 

1790 `_yErr` double DEFAULT NULL, 

1791 `G` double DEFAULT NULL, 

1792 `GErr` double DEFAULT NULL, 

1793 `W1` double DEFAULT NULL, 

1794 `W1Err` double DEFAULT NULL, 

1795 `unkMag` double DEFAULT NULL, 

1796 `unkMagErr` double DEFAULT NULL, 

1797 `dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 

1798 `updated` tinyint(4) DEFAULT '0', 

1799 `classificationReliability` tinyint(4) DEFAULT NULL, 

1800 `transientAbsMag` double DEFAULT NULL, 

1801 `merged_rank` tinyint(4) DEFAULT NULL, 

1802 PRIMARY KEY (`id`), 

1803 KEY `key_transient_object_id` (`transient_object_id`), 

1804 KEY `key_catalogue_object_id` (`catalogue_object_id`), 

1805 KEY `idx_separationArcsec` (`separationArcsec`), 

1806 KEY `idx_rank` (`rank`) 

1807) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

1808 

1809CREATE TABLE IF NOT EXISTS `sherlock_classifications` ( 

1810 `transient_object_id` bigint(20) NOT NULL, 

1811 `classification` varchar(45) DEFAULT NULL, 

1812 `annotation` TEXT COLLATE utf8_unicode_ci DEFAULT NULL, 

1813 `summary` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL, 

1814 `separationArcsec` DOUBLE DEFAULT NULL, 

1815 `matchVerified` TINYINT NULL DEFAULT NULL, 

1816 `developmentComment` VARCHAR(100) NULL, 

1817 `dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP, 

1818 `dateCreated` datetime DEFAULT CURRENT_TIMESTAMP, 

1819 `updated` varchar(45) DEFAULT '0', 

1820 PRIMARY KEY (`transient_object_id`), 

1821 KEY `key_transient_object_id` (`transient_object_id`), 

1822 KEY `idx_summary` (`summary`), 

1823 KEY `idx_classification` (`classification`), 

1824 KEY `idx_dateLastModified` (`dateLastModified`) 

1825) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

1826 

1827""" % locals() 

1828 

1829 # A FIX FOR MYSQL VERSIONS < 5.6 

1830 triggers = [] 

1831 if float(self.dbVersions["transients"][:3]) < 5.6: 

1832 createStatement = createStatement.replace( 

1833 "`dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,", "`dateLastModified` datetime DEFAULT NULL,") 

1834 createStatement = createStatement.replace( 

1835 "`dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,", "`dateCreated` datetime DEFAULT NULL,") 

1836 

1837 triggers.append(""" 

1838CREATE TRIGGER dateCreated 

1839BEFORE INSERT ON `%(crossmatchTable)s` 

1840FOR EACH ROW 

1841BEGIN 

1842 IF NEW.dateCreated IS NULL THEN 

1843 SET NEW.dateCreated = NOW(); 

1844 SET NEW.dateLastModified = NOW(); 

1845 END IF; 

1846END""" % locals()) 

1847 

1848 try: 

1849 writequery( 

1850 log=self.log, 

1851 sqlQuery=createStatement, 

1852 dbConn=self.transientsDbConn, 

1853 Force=True 

1854 ) 

1855 except: 

1856 self.log.info( 

1857 "Could not create table (`%(crossmatchTable)s`). Probably already exist." % locals()) 

1858 

1859 sqlQuery = u""" 

1860 SHOW TRIGGERS; 

1861 """ % locals() 

1862 rows = readquery( 

1863 log=self.log, 

1864 sqlQuery=sqlQuery, 

1865 dbConn=self.transientsDbConn, 

1866 ) 

1867 

1868 # DON'T ADD TRIGGERS IF THEY ALREADY EXIST 

1869 for r in rows: 

1870 if r["Trigger"] in ("sherlock_classifications_BEFORE_INSERT", "sherlock_classifications_AFTER_INSERT"): 

1871 return None 

1872 

1873 triggers.append("""CREATE TRIGGER `sherlock_classifications_BEFORE_INSERT` BEFORE INSERT ON `sherlock_classifications` FOR EACH ROW 

1874BEGIN 

1875 IF new.classification = "ORPHAN" THEN 

1876 SET new.annotation = "The transient location is not matched against any known catalogued source", new.summary = "No catalogued match"; 

1877 END IF; 

1878END""" % locals()) 

1879 

1880 triggers.append("""CREATE TRIGGER `sherlock_classifications_AFTER_INSERT` AFTER INSERT ON `sherlock_classifications` FOR EACH ROW 

1881BEGIN 

1882 update `%(transientTable)s` set `%(transientTableClassCol)s` = new.classification 

1883 where `%(transientTableIdCol)s` = new.transient_object_id; 

1884END""" % locals()) 

1885 

1886 for t in triggers: 

1887 try: 

1888 writequery( 

1889 log=self.log, 

1890 sqlQuery=t, 

1891 dbConn=self.transientsDbConn, 

1892 Force=True 

1893 ) 

1894 except: 

1895 self.log.info( 

1896 "Could not create trigger (`%(crossmatchTable)s`). Probably already exist." % locals()) 

1897 

1898 self.log.debug('completed the ``_create_tables_if_not_exist`` method') 

1899 return None 

1900 

1901 # use the tab-trigger below for new method 

1902 def generate_match_annotation( 

1903 self, 

1904 match, 

1905 updatePeakMagnitudes=False): 

1906 """*generate a human readale annotation for the transient-catalogue source match* 

1907 

1908 **Key Arguments** 

1909 

1910 - ``match`` -- the source crossmatched against the transient 

1911 - ``updatePeakMagnitudes`` -- update the peak magnitudes in the annotations to give absolute magnitudes. Default *False* 

1912 

1913 

1914 **Return** 

1915 

1916 - None 

1917 

1918 

1919 **Usage** 

1920 

1921 

1922 

1923 ```python 

1924 usage code 

1925 ``` 

1926 

1927 --- 

1928 

1929 ```eval_rst 

1930 .. todo:: 

1931 

1932 - add usage info 

1933 - create a sublime snippet for usage 

1934 - write a command-line tool for this method 

1935 - update package tutorial with command-line tool info if needed 

1936 ``` 

1937 """ 

1938 self.log.debug('starting the ``generate_match_annotation`` method') 

1939 

1940 if "catalogue_object_subtype" not in match: 

1941 match["catalogue_object_subtype"] = None 

1942 catalogue = match["catalogue_table_name"] 

1943 objectId = match["catalogue_object_id"] 

1944 objectType = match["catalogue_object_type"] 

1945 objectSubtype = match["catalogue_object_subtype"] 

1946 catalogueString = catalogue 

1947 if "catalogue" not in catalogueString.lower(): 

1948 catalogueString = catalogue + " catalogue" 

1949 if "/" in catalogueString: 

1950 catalogueString += "s" 

1951 

1952 if "ned" in catalogue.lower(): 

1953 objectId = objectId.replace("+", "%2B") 

1954 objectId = '''<a href="https://ned.ipac.caltech.edu/cgi-bin/objsearch?objname=%(objectId)s&extend=no&hconst=73&omegam=0.27&omegav=0.73&corr_z=1&out_csys=Equatorial&out_equinox=J2000.0&obj_sort=RA+or+Longitude&of=pre_text&zv_breaker=30000.0&list_limit=5&img_stamp=YES">%(objectId)s</a>''' % locals() 

1955 elif "sdss" in catalogue.lower(): 

1956 objectId = "http://skyserver.sdss.org/dr12/en/tools/explore/Summary.aspx?id=%(objectId)s" % locals( 

1957 ) 

1958 

1959 ra = self.converter.ra_decimal_to_sexegesimal( 

1960 ra=match["raDeg"], 

1961 delimiter="" 

1962 ) 

1963 dec = self.converter.dec_decimal_to_sexegesimal( 

1964 dec=match["decDeg"], 

1965 delimiter="" 

1966 ) 

1967 betterName = "SDSS J" + ra[0:9] + dec[0:9] 

1968 objectId = '''<a href="%(objectId)s">%(betterName)s</a>''' % locals() 

1969 elif "milliquas" in catalogue.lower(): 

1970 thisName = objectId 

1971 objectId = objectId.replace(" ", "+") 

1972 objectId = '''<a href="https://heasarc.gsfc.nasa.gov/db-perl/W3Browse/w3table.pl?popupFrom=Query+Results&tablehead=name%%3Dheasarc_milliquas%%26description%%3DMillion+Quasars+Catalog+%%28MILLIQUAS%%29%%2C+Version+4.8+%%2822+June+2016%%29%%26url%%3Dhttp%%3A%%2F%%2Fheasarc.gsfc.nasa.gov%%2FW3Browse%%2Fgalaxy-catalog%%2Fmilliquas.html%%26archive%%3DN%%26radius%%3D1%%26mission%%3DGALAXY+CATALOG%%26priority%%3D5%%26tabletype%%3DObject&dummy=Examples+of+query+constraints%%3A&varon=name&bparam_name=%%3D%%22%(objectId)s%%22&bparam_name%%3A%%3Aunit=+&bparam_name%%3A%%3Aformat=char25&varon=ra&bparam_ra=&bparam_ra%%3A%%3Aunit=degree&bparam_ra%%3A%%3Aformat=float8%%3A.5f&varon=dec&bparam_dec=&bparam_dec%%3A%%3Aunit=degree&bparam_dec%%3A%%3Aformat=float8%%3A.5f&varon=bmag&bparam_bmag=&bparam_bmag%%3A%%3Aunit=mag&bparam_bmag%%3A%%3Aformat=float8%%3A4.1f&varon=rmag&bparam_rmag=&bparam_rmag%%3A%%3Aunit=mag&bparam_rmag%%3A%%3Aformat=float8%%3A4.1f&varon=redshift&bparam_redshift=&bparam_redshift%%3A%%3Aunit=+&bparam_redshift%%3A%%3Aformat=float8%%3A6.3f&varon=radio_name&bparam_radio_name=&bparam_radio_name%%3A%%3Aunit=+&bparam_radio_name%%3A%%3Aformat=char22&varon=xray_name&bparam_xray_name=&bparam_xray_name%%3A%%3Aunit=+&bparam_xray_name%%3A%%3Aformat=char22&bparam_lii=&bparam_lii%%3A%%3Aunit=degree&bparam_lii%%3A%%3Aformat=float8%%3A.5f&bparam_bii=&bparam_bii%%3A%%3Aunit=degree&bparam_bii%%3A%%3Aformat=float8%%3A.5f&bparam_broad_type=&bparam_broad_type%%3A%%3Aunit=+&bparam_broad_type%%3A%%3Aformat=char4&bparam_optical_flag=&bparam_optical_flag%%3A%%3Aunit=+&bparam_optical_flag%%3A%%3Aformat=char3&bparam_red_psf_flag=&bparam_red_psf_flag%%3A%%3Aunit=+&bparam_red_psf_flag%%3A%%3Aformat=char1&bparam_blue_psf_flag=&bparam_blue_psf_flag%%3A%%3Aunit=+&bparam_blue_psf_flag%%3A%%3Aformat=char1&bparam_ref_name=&bparam_ref_name%%3A%%3Aunit=+&bparam_ref_name%%3A%%3Aformat=char6&bparam_ref_redshift=&bparam_ref_redshift%%3A%%3Aunit=+&bparam_ref_redshift%%3A%%3Aformat=char6&bparam_qso_prob=&bparam_qso_prob%%3A%%3Aunit=percent&bparam_qso_prob%%3A%%3Aformat=int2%%3A3d&bparam_alt_name_1=&bparam_alt_name_1%%3A%%3Aunit=+&bparam_alt_name_1%%3A%%3Aformat=char22&bparam_alt_name_2=&bparam_alt_name_2%%3A%%3Aunit=+&bparam_alt_name_2%%3A%%3Aformat=char22&Entry=&Coordinates=J2000&Radius=Default&Radius_unit=arcsec&NR=CheckCaches%%2FGRB%%2FSIMBAD%%2BSesame%%2FNED&Time=&ResultMax=1000&displaymode=Display&Action=Start+Search&table=heasarc_milliquas">%(thisName)s</a>''' % locals() 

1973 

1974 if objectSubtype and str(objectSubtype).lower() in ["uvs", "radios", "xray", "qso", "irs", 'uves', 'viss', 'hii', 'gclstr', 'ggroup', 'gpair', 'gtrpl']: 

1975 objectType = objectSubtype 

1976 

1977 if objectType == "star": 

1978 objectType = "stellar source" 

1979 elif objectType == "agn": 

1980 objectType = "AGN" 

1981 elif objectType == "cb": 

1982 objectType = "CV" 

1983 elif objectType == "unknown": 

1984 objectType = "unclassified source" 

1985 

1986 sep = match["separationArcsec"] 

1987 if match["classificationReliability"] == 1: 

1988 classificationReliability = "synonymous" 

1989 psep = match["physical_separation_kpc"] 

1990 if psep: 

1991 location = '%(sep)0.1f" (%(psep)0.1f Kpc) from the %(objectType)s core' % locals( 

1992 ) 

1993 else: 

1994 location = '%(sep)0.1f" from the %(objectType)s core' % locals( 

1995 ) 

1996 else: 

1997 # elif match["classificationReliability"] in (2, 3): 

1998 classificationReliability = "possibly associated" 

1999 n = float(match["northSeparationArcsec"]) 

2000 if n > 0: 

2001 nd = "S" 

2002 else: 

2003 nd = "N" 

2004 e = float(match["eastSeparationArcsec"]) 

2005 if e > 0: 

2006 ed = "W" 

2007 else: 

2008 ed = "E" 

2009 n = math.fabs(float(n)) 

2010 e = math.fabs(float(e)) 

2011 psep = match["physical_separation_kpc"] 

2012 if psep: 

2013 location = '%(n)0.2f" %(nd)s, %(e)0.2f" %(ed)s (%(psep)0.1f Kpc) from the %(objectType)s centre' % locals( 

2014 ) 

2015 else: 

2016 location = '%(n)0.2f" %(nd)s, %(e)0.2f" %(ed)s from the %(objectType)s centre' % locals( 

2017 ) 

2018 location = location.replace("unclassified", "object's") 

2019 

2020 best_mag = None 

2021 best_mag_error = None 

2022 best_mag_filter = None 

2023 filters = ["R", "V", "B", "I", "J", "G", "H", "K", "U", 

2024 "_r", "_g", "_i", "_g", "_z", "_y", "_u", "W1", "unkMag"] 

2025 for f in filters: 

2026 if f in match and match[f] and not best_mag: 

2027 best_mag = match[f] 

2028 try: 

2029 best_mag_error = match[f + "Err"] 

2030 except: 

2031 pass 

2032 subfilter = f.replace( 

2033 "_", "").replace("Mag", "") 

2034 best_mag_filter = f.replace( 

2035 "_", "").replace("Mag", "") + "=" 

2036 if "unk" in best_mag_filter: 

2037 best_mag_filter = "" 

2038 subfilter = '' 

2039 

2040 if not best_mag_filter: 

2041 if str(best_mag).lower() in ("8", "11", "18"): 

2042 best_mag_filter = "an " 

2043 else: 

2044 best_mag_filter = "a " 

2045 else: 

2046 if str(best_mag_filter)[0].lower() in ("r", "i", "h"): 

2047 best_mag_filter = "an " + best_mag_filter 

2048 else: 

2049 best_mag_filter = "a " + best_mag_filter 

2050 if not best_mag: 

2051 best_mag = "an unknown-" 

2052 best_mag_filter = "" 

2053 else: 

2054 best_mag = "%(best_mag)0.2f " % locals() 

2055 

2056 distance = None 

2057 if "direct_distance" in match and match["direct_distance"]: 

2058 d = match["direct_distance"] 

2059 distance = "distance of %(d)0.1f Mpc" % locals() 

2060 

2061 if match["z"]: 

2062 z = match["z"] 

2063 distance += "(z=%(z)0.3f)" % locals() 

2064 elif "z" in match and match["z"]: 

2065 z = match["z"] 

2066 distance = "z=%(z)0.3f" % locals() 

2067 elif "photoZ" in match and match["photoZ"]: 

2068 z = match["photoZ"] 

2069 zErr = match["photoZErr"] 

2070 if not zErr: 

2071 distance = "photoZ=%(z)0.3f" % locals() 

2072 else: 

2073 distance = "photoZ=%(z)0.3f (&plusmn%(zErr)0.3f)" % locals() 

2074 

2075 if distance: 

2076 distance = "%(distance)s" % locals() 

2077 

2078 distance_modulus = None 

2079 if match["direct_distance_modulus"]: 

2080 distance_modulus = match["direct_distance_modulus"] 

2081 elif match["distance_modulus"]: 

2082 distance_modulus = match["distance_modulus"] 

2083 

2084 if updatePeakMagnitudes: 

2085 if distance: 

2086 absMag = match["transientAbsMag"] 

2087 absMag = """ A host %(distance)s implies a transient <em>M =</em> %(absMag)s mag.""" % locals( 

2088 ) 

2089 else: 

2090 absMag = "" 

2091 else: 

2092 if distance and distance_modulus: 

2093 absMag = "%(distance_modulus)0.2f" % locals() 

2094 absMag = """ A host %(distance)s implies a <em>m - M =</em> %(absMag)s.""" % locals( 

2095 ) 

2096 else: 

2097 absMag = "" 

2098 

2099 annotation = "The transient is %(classificationReliability)s with <em>%(objectId)s</em>; %(best_mag_filter)s%(best_mag)smag %(objectType)s found in the %(catalogueString)s. It's located %(location)s.%(absMag)s" % locals() 

2100 summary = '%(sep)0.1f" from %(objectType)s in %(catalogue)s' % locals( 

2101 ) 

2102 

2103 self.log.debug('completed the ``generate_match_annotation`` method') 

2104 return annotation, summary, sep 

2105 

2106 # use the tab-trigger below for new method 

2107 # xt-class-method 

2108 

2109 

2110def _crossmatch_transients_against_catalogues( 

2111 transientsMetadataListIndex, 

2112 log, 

2113 settings, 

2114 colMaps): 

2115 """run the transients through the crossmatch algorithm in the settings file 

2116 

2117 **Key Arguments** 

2118 

2119 

2120 - ``transientsMetadataListIndex`` -- the list of transient metadata lifted from the database. 

2121 - ``colMaps`` -- dictionary of dictionaries with the name of the database-view (e.g. `tcs_view_agn_milliquas_v4_5`) as the key and the column-name dictary map as value (`{view_name: {columnMap}}`). 

2122 

2123 **Return** 

2124 

2125 - ``crossmatches`` -- a list of dictionaries of the associated sources crossmatched from the catalogues database 

2126 

2127 

2128 .. todo :: 

2129 

2130 - update key arguments values and definitions with defaults 

2131 - update return values and definitions 

2132 - update usage examples and text 

2133 - update docstring text 

2134 - check sublime snippet exists 

2135 - clip any useful text to docs mindmap 

2136 - regenerate the docs and check redendering of this docstring 

2137 """ 

2138 

2139 from fundamentals.mysql import database 

2140 from sherlock import transient_catalogue_crossmatch 

2141 

2142 global theseBatches 

2143 

2144 log.debug( 

2145 'starting the ``_crossmatch_transients_against_catalogues`` method') 

2146 

2147 # SETUP ALL DATABASE CONNECTIONS 

2148 

2149 transientsMetadataList = theseBatches[transientsMetadataListIndex] 

2150 

2151 dbConn = database( 

2152 log=log, 

2153 dbSettings=settings["database settings"]["static catalogues"] 

2154 ).connect() 

2155 

2156 cm = transient_catalogue_crossmatch( 

2157 log=log, 

2158 dbConn=dbConn, 

2159 transients=transientsMetadataList, 

2160 settings=settings, 

2161 colMaps=colMaps 

2162 ) 

2163 crossmatches = cm.match() 

2164 

2165 log.debug( 

2166 'completed the ``_crossmatch_transients_against_catalogues`` method') 

2167 

2168 return crossmatches