2
$\begingroup$

I am trying to query Gaia archive to get the medium quality sample as mentioned in Reico-Blanco et al. (2022) Gaia Data Release 3: Chemical cartography of the Milky Way. The idea is to query the flags_gspspec string where every letter represents one flag. The criteria for selecting the medium quality flag is as follows,

  1. The first 13 characters are taken into consideration.
  2. The last 4 characters must always be 0.
  3. The seventh character could be one of one of (0,1,2,3).
  4. The remaining eight flags must be either 0 or 1.

The problem gets even more complicated when we look at the allowed values for the flags,

  1. The seventh character could take one of the values from (0,1,2,3,4,5,9).
  2. All the other characters could take one of the values from (0,1,2,9).

I tried writing a Regular expression to match the type and it failed because ADQL does not support Regex. The only option that I could think of is to write a list of all possible combinations and include them in the LIKE clause. The problem is I am getting 1024 such combination from elementary combinatorics and its not practical to write down all the possible combinations.

If there is anybody out there who has worked on a similar problem before, please let me know. I would like to discuss this further.

PS: I do not know whether this is the right forum to post this since the query is more related to a database query and less of an astronomical question. Incase, this is not the right place, please redirect me to the right forum.

Thank you in advance.

Improve this question
$\endgroup$
6
  • 2
    $\begingroup$ Can you provide a link to the paper or preprint please ? Google Scholar and ADS links are only turning up papers on COVID, coffee beans or residential wood burning for me $\endgroup$
    – astrosnapper
    Aug 8, 2022 at 19:12
  • $\begingroup$ @astrosnapper I think I found it (see edit re. Alejandra Recio-Blanco) $\endgroup$
    – uhoh
    Aug 8, 2022 at 20:56
  • $\begingroup$ @astrosnapper: The link to the paper (researchgate.net/publication/…) $\endgroup$
    – CTZenScientist
    Aug 9, 2022 at 14:55
  • 1
    $\begingroup$ Update: I managed to figure out a workaround. Instead of using Regex in the query, I have written a subroutine that uses Regex to filter out the required stars from the population. It worked on a smaller population with 1000 stars. I will be applying the routine on the complete population that contains about 70000 stars. Fingers crossed!!! $\endgroup$
    – CTZenScientist
    Aug 9, 2022 at 15:53
  • 1
    $\begingroup$ A general comment: While SQL and ADQL don't strictly support regex's, they do have LIKE which supports wildcards. There is an example for flags_gspspec, admittedly pretty repetitious, at the Gaia archive (scroll/search to 'Gaia RVS Diffuse Interstellar Bands') and Listing 2 of the paper gives the ADQL queries to replicate the datasets (good!). In the OP's case, best bet is a LIKE to cut down as much as possible at the DB and post processing of results with an actual regex $\endgroup$
    – astrosnapper
    Aug 10, 2022 at 0:03

1 Answer 1

Reset to default
1
$\begingroup$

I figured out the database query. There is a similar example on Gaia archive and a similar code is also available in the Reico Blanco paper linked in my question above. Below is the query that goes in with the WHERE clause.

((param.flags_gspspec LIKE '____________0%') OR (param.flags_gspspec LIKE '____________1%')) 
AND ((param.flags_gspspec LIKE '0%') OR (param.flags_gspspec LIKE '1%')) 
AND ((param.flags_gspspec LIKE '_0%') OR (param.flags_gspspec LIKE '_1%')) 
AND ((param.flags_gspspec LIKE '__0%') OR (param.flags_gspspec LIKE '__1%')) 
AND ((param.flags_gspspec LIKE '___0%') OR (param.flags_gspspec LIKE '___1%')) 
AND ((param.flags_gspspec LIKE '____0%') OR (param.flags_gspspec LIKE '____1%')) 
AND ((param.flags_gspspec LIKE '_____0%') OR (param.flags_gspspec LIKE '_____1%')) 
AND ((param.flags_gspspec LIKE '______0%') OR (param.flags_gspspec LIKE '______1%') OR (param.flags_gspspec LIKE '______2%') OR (param.flags_gspspec LIKE '______3%')) 
AND ((param.flags_gspspec LIKE '_______0%') OR (param.flags_gspspec LIKE '_______1%') OR (param.flags_gspspec LIKE '_______2%'))

One could similarly define queries for the best quality sample too.

Improve this answer
$\endgroup$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .