def main(self): # Band abbreviation, band_id mapping band_mapping = { "g": "SDSS g", "r": "SDSS r", "i": "SDSS i", "Clear": "Clear", "J": "UKIRT J" } detector_mapping = { "s": "SWOPE", "t": "THACHER", "a": "ANDICAM", "n": "NICKEL", "m": "MOSFIRE", "k": "KAIT", "si": "SINISTRO" } is_error = False # Parameter checks if self.options.gw_id == "": is_error = True print("GWID is required.") if self.options.healpix_file == "": is_error = True print("Healpix file is required.") if self.options.tile_file == "": is_error = True print("Tile file is required.") if self.options.tele == "": is_error = True print("Telescope abbreviation is required.") if is_error: print("Exiting...") return 1 formatted_healpix_dir = self.options.healpix_dir if "{GWID}" in formatted_healpix_dir: formatted_healpix_dir = formatted_healpix_dir.replace( "{GWID}", self.options.gw_id) formatted_tile_dir = self.options.tile_dir if "{GWID}" in formatted_tile_dir: formatted_tile_dir = formatted_tile_dir.replace( "{GWID}", self.options.gw_id) hpx_path = "%s/%s" % (formatted_healpix_dir, self.options.healpix_file) tile_path = "%s/%s" % (formatted_tile_dir, self.options.tile_file) # Check if the above files exist... if not os.path.exists(hpx_path): is_error = True print("Healpix file `%s` does not exist." % hpx_path) if not os.path.exists(tile_path): is_error = True print("Tile file `%s` does not exist." % tile_path) if self.options.tele not in detector_mapping: is_error = True print("Unknown telescope abbreviation: %s " % self.options.tele) # TODO: STANDARDIZE FILE FORMATTING # God damn you Charlie, you beautiful son of a bitch. # tile_files = [] # for file in os.listdir(formatted_tile_dir): # if file.endswith(".dat"): # tile_files.append("%s/%s" % (formatted_tile_dir, file)) # if len(tile_files) <= 0: # is_error = True # print("There are no tiles to process.") if is_error: print("Exiting...") return 1 print("\tLoading NSIDE 128 pixels...") nside128 = 128 N128_dict = None with open('N128_dict.pkl', 'rb') as handle: N128_dict = pickle.load(handle) del handle print("\tLoading existing EBV...") ebv = None with open('ebv.pkl', 'rb') as handle: ebv = pickle.load(handle) # Get Map ID healpix_map_select = "SELECT id, NSIDE FROM HealpixMap WHERE GWID = '%s' and Filename = '%s'" healpix_map_id = int( query_db([ healpix_map_select % (self.options.gw_id, self.options.healpix_file) ])[0][0][0]) healpix_map_nside = int( query_db([ healpix_map_select % (self.options.gw_id, self.options.healpix_file) ])[0][0][1]) print("Get map pixel") map_pixel_select = "SELECT id, HealpixMap_id, Pixel_Index, Prob, Distmu, Distsigma, Distnorm, Mean, Stddev, Norm, N128_SkyPixel_id FROM HealpixPixel WHERE HealpixMap_id = %s;" q = map_pixel_select % healpix_map_id map_pixels = query_db([q])[0] print("Retrieved %s map pixels..." % len(map_pixels)) # Initialize map pix dict for later access map_pixel_dict = {} for p in map_pixels: map_pixel_dict[int(p[2])] = p band_select = "SELECT id, Name, F99_Coefficient FROM Band WHERE `Name`='%s'" detector_select_by_name = "SELECT id, Name, Deg_width, Deg_height, Deg_radius, Area, MinDec, MaxDec FROM Detector WHERE Name='%s'" # print("Processing %s tiles" % len(tile_files)) # observed_tiles = [] obs_tile_insert_data = [] detectors = {} tele_name = detector_mapping[self.options.tele] detector_result = query_db([detector_select_by_name % tele_name])[0][0] detector = Detector(detector_result[1], float(detector_result[2]), float(detector_result[2])) detector.id = int(detector_result[0]) detector.area = float(detector_result[5]) if detector.name not in detectors: detectors[detector.name] = detector print("Processing `%s` for %s" % (tile_path, detector.name)) # Iterate over lines of a tile with open(tile_path, 'r') as csvfile: # Read CSV lines csvreader = csv.reader(csvfile, delimiter=' ', skipinitialspace=True) for row in csvreader: file_name = row[0] field_name = row[1] ra = float(row[2]) dec = float(row[3]) mjd = float(row[4]) band = row[5].strip() exp_time = None try: exp_time = float(row[6]) if exp_time <= 0.0: exp_time = None except: pass mag_lim = None try: mag_lim = float(row[7]) except: pass # Get Band_id band_map = band_mapping[band] band_results = query_db([band_select % band_map])[0][0] band_id = band_results[0] band_name = band_results[1] band_F99 = float(band_results[2]) c = coord.SkyCoord(ra, dec, unit=(u.deg, u.deg)) n128_index = hp.ang2pix(nside128, 0.5 * np.pi - c.dec.radian, c.ra.radian) # theta, phi n128_id = N128_dict[n128_index] t = Tile(c.ra.degree, c.dec.degree, detector.deg_width, detector.deg_height, int(healpix_map_nside)) t.field_name = field_name t.N128_pixel_id = n128_id t.N128_pixel_index = n128_index t.mwe = ebv[n128_index] * band_F99 t.mjd = mjd t.exp_time = exp_time t.mag_lim = mag_lim # observed_tiles.append(t) obs_tile_insert_data.append(( detector.id, t.field_name, t.ra_deg, t.dec_deg, "POINT(%s %s)" % (t.dec_deg, t.ra_deg - 180.0 ), # Dec, RA order due to MySQL convention for lat/lon t.query_polygon_string, str(t.mwe), t.N128_pixel_id, band_id, t.mjd, t.exp_time, t.mag_lim, healpix_map_id)) # iterate over tiles in tile_files # for tf in tile_files: # # Read Tile File CSV and get the telescope (by file naming convention) # file_name = tf.split("/")[-1] # tele_abbr = file_name.split("_")[0] # tele_name = detector_mapping[tele_abbr] # detector_result = query_db([detector_select_by_name % tele_name])[0][0] # detector = Detector(detector_result[1], float(detector_result[2]), float(detector_result[2])) # detector.id = int(detector_result[0]) # detector.area = float(detector_result[5]) # if detector.name not in detectors: # detectors[detector.name] = detector # print("Processing `%s` for %s" % (file_name, detector.name)) # with open(tf,'r') as csvfile: # # Read CSV lines # csvreader = csv.reader(csvfile, delimiter=' ',skipinitialspace=True) # for row in csvreader: # file_name = row[0] # field_name = row[1] # ra = float(row[2]) # dec = float(row[3]) # # ra = row[2] # # dec = row[3] # exp_time = float(row[4]) # mjd = float(row[5]) # band = row[6] # mag_lim = float(row[7]) if row[7] != '___' else None # # mag_lim = None # # Get Band_id # band_map = band_mapping[band] # band_results = query_db([band_select % band_map])[0][0] # band_id = band_results[0] # band_name = band_results[1] # band_F99 = float(band_results[2]) # c = coord.SkyCoord(ra, dec, unit=(u.deg, u.deg)) # # c = coord.SkyCoord(ra, dec, unit=(u.hour, u.deg)) # n128_index = hp.ang2pix(nside128, 0.5*np.pi - c.dec.radian, c.ra.radian) # theta, phi # n128_id = N128_dict[n128_index] # t = Tile(c.ra.degree, c.dec.degree, detector.deg_width, detector.deg_height, int(healpix_map_nside)) # t.field_name = field_name # t.N128_pixel_id = n128_id # t.N128_pixel_index = n128_index # t.mwe = ebv[n128_index]*band_F99 # t.mjd = mjd # t.exp_time = exp_time # t.mag_lim = mag_lim # # observed_tiles.append(t) # obs_tile_insert_data.append(( # detector.id, # t.field_name, # t.ra_deg, # t.dec_deg, # "POINT(%s %s)" % (t.dec_deg, t.ra_deg - 180.0), # Dec, RA order due to MySQL convention for lat/lon # t.query_polygon_string, # str(t.mwe), # t.N128_pixel_id, # band_id, # t.mjd, # t.exp_time, # t.mag_lim, # healpix_map_id)) insert_observed_tile = ''' INSERT INTO ObservedTile (Detector_id, FieldName, RA, _Dec, Coord, Poly, EBV, N128_SkyPixel_id, Band_id, MJD, Exp_Time, Mag_Lim, HealpixMap_id) VALUES (%s, %s, %s, %s, ST_PointFromText(%s, 4326), ST_GEOMFROMTEXT(%s, 4326), %s, %s, %s, %s, %s, %s, %s) ''' print("Inserting %s tiles..." % len(obs_tile_insert_data)) batch_insert(insert_observed_tile, obs_tile_insert_data) print("Done...") # Associate map pixels with observed tiles print("Building observed tile-healpix map pixel relation...") obs_tile_select = ''' SELECT id, Detector_id, FieldName, RA, _Dec, Coord, Poly, EBV, N128_SkyPixel_id, Band_id, MJD, Exp_Time, Mag_Lim, HealpixMap_id FROM ObservedTile WHERE Detector_id = %s and HealpixMap_id = %s ''' # Obtain the tile with id's so they can be used in later INSERTs tile_pixel_data = [] for d_name, d in detectors.items(): obs_tiles_for_detector = query_db( [obs_tile_select % (d.id, healpix_map_id)])[0] print("Observed Tiles for %s: %s" % (d.name, len(obs_tiles_for_detector))) for otfd in obs_tiles_for_detector: ot_id = int(otfd[0]) ra = float(otfd[3]) dec = float(otfd[4]) t = Tile(ra, dec, d.deg_width, d.deg_height, healpix_map_nside) t.id = ot_id for p in t.enclosed_pixel_indices: tile_pixel_data.append((t.id, map_pixel_dict[p][0])) print("Length of tile_pixel_data: %s" % len(tile_pixel_data)) tile_pixel_upload_csv = "%s/ObservedTiles/%s_tile_pixel_upload.csv" % ( formatted_healpix_dir, detector.name) # Create CSV try: t1 = time.time() print("Creating `%s`" % tile_pixel_upload_csv) with open(tile_pixel_upload_csv, 'w') as csvfile: csvwriter = csv.writer(csvfile) for data in tile_pixel_data: csvwriter.writerow(data) t2 = time.time() print("\n********* start DEBUG ***********") print("Tile-Pixel CSV creation execution time: %s" % (t2 - t1)) print("********* end DEBUG ***********\n") except Error as e: print("Error in creating Tile-Pixel CSV:\n") print(e) print("\nExiting") return 1 # clean up print("freeing `tile_pixel_data`...") del tile_pixel_data print("Bulk uploading `tile_pixel_data`...") ot_hp_upload_sql = """LOAD DATA LOCAL INFILE '%s' INTO TABLE ObservedTile_HealpixPixel FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ObservedTile_id, HealpixPixel_id);""" success = bulk_upload(ot_hp_upload_sql % tile_pixel_upload_csv) if not success: print("\nUnsuccessful bulk upload. Exiting...") return 1 try: print("Removing `%s`..." % tile_pixel_upload_csv) os.remove(tile_pixel_upload_csv) print("... Done") except Error as e: print("Error in file removal") print(e) print("\nExiting") return 1
def main(self): is_error = False # Parameter checks if self.options.gw_id == "": is_error = True print("GWID is required.") formatted_healpix_dir = self.options.healpix_dir if "{GWID}" in formatted_healpix_dir: formatted_healpix_dir = formatted_healpix_dir.replace( "{GWID}", self.options.gw_id) hpx_path = "%s/%s" % (formatted_healpix_dir, self.options.healpix_file) tile_file_path = "%s/ObservedTiles/%s" % (formatted_healpix_dir, self.options.tile_file) if self.options.healpix_file == "": is_error = True print("You must specify which healpix file to process.") if self.options.tile_file == "": is_error = True print("You must specify which tile file to process.") if self.options.tile_filter == "": is_error = True print("You must specify which filter to use with tiles.") if is_error: print("Exiting...") return 1 print("\tLoading NSIDE 128 pixels...") nside128 = 128 N128_dict = None with open('N128_dict.pkl', 'rb') as handle: N128_dict = pickle.load(handle) del handle print("\tLoading existing EBV...") ebv = None with open('ebv.pkl', 'rb') as handle: ebv = pickle.load(handle) # Get Map ID healpix_map_select = "SELECT id, NSIDE FROM HealpixMap WHERE GWID = '%s' and Filename = '%s'" healpix_map_id = query_db([ healpix_map_select % (self.options.gw_id, self.options.healpix_file) ])[0][0][0] healpix_map_nside = query_db([ healpix_map_select % (self.options.gw_id, self.options.healpix_file) ])[0][0][1] # Get Band_id band_select = "SELECT id, Name, F99_Coefficient FROM Band WHERE `Name`='%s'" q = band_select % self.options.tile_filter band_results = query_db([q])[0][0] band_id = band_results[0] band_name = band_results[1] band_F99 = float(band_results[2]) print("Filter: %s" % band_name) print("Get map pixel") map_pixel_select = "SELECT id, HealpixMap_id, Pixel_Index, Prob, Distmu, Distsigma, Distnorm, Mean, Stddev, Norm, N128_SkyPixel_id FROM HealpixPixel WHERE HealpixMap_id = %s;" q = map_pixel_select % healpix_map_id map_pixels = query_db([q])[0] print("Retrieved %s map pixels..." % len(map_pixels)) # Initialize map pix dict for later access map_pixel_dict = {} for p in map_pixels: map_pixel_dict[int(p[2])] = p # Read Tile File CSV detector_select = "SELECT id, Name, Deg_width, Deg_height, Deg_radius, Area, MinDec, MaxDec FROM Detector WHERE Name='%s'" detector = None observed_tiles = OrderedDict() with open(tile_file_path, 'r') as csvfile: # Read Telescope Name first_line = csvfile.readline() detector_name = first_line.replace("# ", "").strip() print("Detector: %s" % detector_name) q = detector_select % detector_name detector_result = query_db([q])[0][0] detector = Detector(detector_result[1], float(detector_result[2]), float(detector_result[2])) detector.id = int(detector_result[0]) detector.area = float(detector_result[5]) # Read CSV lines csvreader = csv.reader(csvfile, delimiter='\t', skipinitialspace=True) next(csvreader) # Skip header for row in csvreader: field_name = row[0] print(field_name) ra = row[1] dec = row[2] c = coord.SkyCoord(ra, dec, unit=(u.deg, u.deg)) n128_index = hp.ang2pix(nside128, 0.5 * np.pi - c.dec.radian, c.ra.radian) # theta, phi n128_id = N128_dict[n128_index] t = Tile(c.ra.degree, c.dec.degree, detector.deg_width, detector.deg_height, int(healpix_map_nside)) t.field_name = field_name t.N128_pixel_id = n128_id t.N128_pixel_index = n128_index t.mwe = ebv[n128_index] * band_F99 observed_tiles[t.field_name] = t insert_observed_tile = ''' INSERT INTO ObservedTile (Detector_id, FieldName, RA, _Dec, Coord, Poly, EBV, N128_SkyPixel_id, Band_id, HealpixMap_id) VALUES (%s, %s, %s, %s, ST_PointFromText(%s, 4326), ST_GEOMFROMTEXT(%s, 4326), %s, %s, %s, %s) ''' # Build Insert Data obs_tile_insert_data = [] for f_name, t in observed_tiles.items(): obs_tile_insert_data.append(( detector.id, t.field_name, t.ra_deg, t.dec_deg, "POINT(%s %s)" % (t.dec_deg, t.ra_deg - 180.0), # Dec, RA order due to MySQL convention for lat/lon t.query_polygon_string, str(t.mwe), t.N128_pixel_id, band_id, healpix_map_id)) print("Inserting %s %s tiles..." % (len(obs_tile_insert_data), detector.name)) batch_insert(insert_observed_tile, obs_tile_insert_data) print("Done...") # Associate map pixels with observed tiles print("Building observed tile-healpix map pixel relation...") obs_tile_select = ''' SELECT id, Detector_id, FieldName, RA, _Dec, Coord, Poly, EBV, N128_SkyPixel_id, Band_id, HealpixMap_id FROM ObservedTile WHERE Detector_id = %s and HealpixMap_id = %s and Band_id = %s ''' obs_tile_results = query_db( [obs_tile_select % (detector.id, healpix_map_id, band_id)])[0] for otr in obs_tile_results: tile_id = int(otr[0]) tile_field_name = otr[2] observed_tiles[tile_field_name].id = tile_id tile_pixel_upload_csv = "%s/ObservedTiles/%s_tile_pixel_upload.csv" % ( formatted_healpix_dir, detector.name) # Insert Tile/Healpix pixel relations tile_pixel_data = [] for f_name, t in observed_tiles.items(): for p in t.enclosed_pixel_indices: tile_pixel_data.append((t.id, map_pixel_dict[p][0])) # Create CSV try: t1 = time.time() print("Creating `%s`" % tile_pixel_upload_csv) with open(tile_pixel_upload_csv, 'w') as csvfile: csvwriter = csv.writer(csvfile) for data in tile_pixel_data: csvwriter.writerow(data) t2 = time.time() print("\n********* start DEBUG ***********") print("Tile-Pixel CSV creation execution time: %s" % (t2 - t1)) print("********* end DEBUG ***********\n") except Error as e: print("Error in creating Tile-Pixel CSV:\n") print(e) print("\nExiting") return 1 # clean up print("freeing `tile_pixel_data`...") del tile_pixel_data print("Bulk uploading `tile_pixel_data`...") ot_hp_upload_sql = """LOAD DATA LOCAL INFILE '%s' INTO TABLE ObservedTile_HealpixPixel FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ObservedTile_id, HealpixPixel_id);""" success = bulk_upload(ot_hp_upload_sql % tile_pixel_upload_csv) if not success: print("\nUnsuccessful bulk upload. Exiting...") return 1 try: print("Removing `%s`..." % tile_pixel_upload_csv) os.remove(tile_pixel_upload_csv) print("... Done") except Error as e: print("Error in file removal") print(e) print("\nExiting") return 1