0 votes

I'm looking for a query to filter out unwanted objects from a crossmatch result and I'm not sure how to do it since NOIRLab doesn't support ADQL geometry functions (e.g. CONTAINS).

The resulting crossmatch has columns with ra & dec values (ra, dec, w1mpro, w2mpro, japermag3). I'm looking for a solution to throw these results into the query and remove all objects that are:

  • listed in Gaia DR3 within 0.0006 deg (~ 2 arcsec)
  • or have a magnitude listed in NSC DR2 g- or r-band within 0.0006 deg (~ 2 arcsec)

I couldn't find an example in the 'notebooks-latest' about removing unwanted objects from a crossmatch so I would be thankful for a little hint. :)

by martinkb (720 points) | 309 views

1 Answer

0 votes
Best answer
Hi Martin, thanks for reaching out. How large is the table that you want to rid of some rows? (according to the criteria you mention) If it's not "many millions or billions" of rows, then the simplest solution I can think of would be to load the table into a Pandas dataframe, and then use Panda's built-in filtering capabilities to keep only the rows you want. Some examples of Pandas' row filtering are for instance here: https://blog.hubspot.com/website/filter-rows-pandas

The selection can be probably done with SQL, in your original query, but that query might be too slow. Hard to tell before trying. If you think the Pandas route won't work, and you want to try SQL, can you please post the exact crossmatch query you have so far? (the one that also returns rows that you don't want).

by robertdemo (8.1k points)
selected by 0
Glad you figured out the table name issue. Regarding q3c index: they are not propagated if you create a new table using "SELECT * from .." from a table that had the index. For small tables (a few thousand rows) having the q3c index or not should not make much of a difference (as long as you feed the non-indexed ra and dec columns first to e.g. q3c_radial_query(). Otherwise, it's a good habit to quickly compute the q3c index.
Thanks a lot for all the hints/tips Robert. :)

I was finally able to run my little python script and condensed the final table down to 175 rows. Did indeed find a new object with brown dwarf characteristics. Yay! :D (https://www.zooniverse.org/projects/marckuchner/backyard-worlds-planet-9/talk/666/2985419?comment=4903916)

I discovered a problem with the script though when I tried to run it on another dataset. It turns out that NSC DR2 doesn't use 'NaN' to indicate a missing magnitude for an object but instead lists a value of '99.990000'. In order to get only those objects that don't have a magnitude listed in g- or r-mag I used the condition 'dfclean = df[(df['nsc_gmag'] == 99.99) & (df['nsc_rmag'] == 99.99)]'.

The new dataset though returns only 'NaN' values when crossmatching in a 2./3600 radius with NSC DR2 which results in an empty pandas dataframe. That's when I noticed I'm loosing all the Non-matches from the crossmatch with my approach which is not what I intended.

So that's where I'm at right now. I'm looking for a solution to get:
 • all objects that crossmatch with NSC DR2 and don't have a magnitude in g- AND r-band
 • additionally keep the ones that don't crossmatch with NSC DR2 (for further checkup)

Currently reading Pandas documentation, StackOverflow, Google, … to come up with a better solution.
After some testing I think I figured it out. The new condition I'm using is:
dfclean = df[((df['nsc_gmag'] == 99.99) & (df['nsc_rmag'] == 99.99)) | ((df['nsc_gmag'].isna()) & (df['nsc_rmag'].isna()))]

I've just added another 'OR' branch after the prior condition to test for 'isna()' in the nsc_gmag and nsc_rmag columns. In this way I'll also keep all the non-matches. Seems to work. :)

On a sidenote, there seems to be something wrong with the 'Question2Answer' software running here at NOIRLab. I've never gotten an email notification although I add my email address down below most of the time where it says "Email me at this address if a comment is added after mine:"
Hi Martin, sorry for the delay, I also didn't get a notification that there were new comments on this thread. We looked into this issue over the past few days, and tested (successfully): when you check the box "I want to be notified", and you enter your email, then you should be getting notifications. Is it possible that you had one or the other, but not both, on that occasion?
> when you check the box "I want to be notified", and you enter your email, then you should be getting notifications.

The box is usually pre-checked and I only put my email address in. But I wouldn't rule out this has happend in the past. However, I've found the last notification email now buried deep in my spam filter. It seems the feature is working properly then. :)

421 questions

437 answers


640 users

Welcome to Data Lab Help Desk, where you can ask questions and receive answers from other members of the community.