<p>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.</p>
<pre><code>((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%'))
</code></pre>
<p>One could similarly define queries for the best quality sample too.</p>

CC BY-SA 4.0
2022-08-30T16:18:36.287Z
2022-08-30T16:18:36.287Z
CTZenScientist
<p>I am trying to query Gaia archive to get the medium quality sample as mentioned in Reico-Blanco et al. (2022) <a href="https://arxiv.org/abs/2206.05534" rel="nofollow noreferrer">Gaia Data Release 3: Chemical cartography of the Milky Way</a>. The idea is to query the <code>flags_gspspec</code> string where every letter represents one flag. The criteria for selecting the medium quality flag is as follows,</p>
<ol>
<li>The first 13 characters are taken into consideration.</li>
<li>The last 4 characters must always be 0.</li>
<li>The seventh character could be one of one of (0,1,2,3).</li>
<li>The remaining eight flags must be either 0 or 1.</li>
</ol>
<p>The problem gets even more complicated when we look at the allowed values for the flags,</p>
<ol>
<li>The seventh character could take one of the values from (0,1,2,3,4,5,9).</li>
<li>All the other characters could take one of the values from (0,1,2,9).</li>
</ol>
<p>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 combinations from elementary combinatorics and its not practical to write down all the possible combinations.</p>
<p>If anybody has worked on a similar problem before, please let me know.</p>
<p>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 is less of an astronomical question. In case this is not the right place, please redirect me to the right forum.</p>

CC BY-SA 4.0
2022-08-08T19:12:54.040Z
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
astrosnapper
CC BY-SA 4.0
2022-08-08T20:56:33.233Z
@astrosnapper I think I found it (see edit re. [Alejandra Recio-Blanco](https://www.oca.eu/en/alejandra-recio-blanco))
uhoh
CC BY-SA 4.0
2022-08-09T14:55:15.697Z
@astrosnapper: The link to the paper (https://www.researchgate.net/publication/361274277_Gaia_Data_Release_3_Analysis_of_RVS_spectra_using_the_General_Stellar_Parametriser_from_spectroscopy)
CTZenScientist
CC BY-SA 4.0
2022-08-09T15:53:09.313Z
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!!!
CTZenScientist
CC BY-SA 4.0
2022-08-09T22:22:35.697Z
@CTZenScientist that's great news! If things work out please feel free to post an answer to your own question, and if there's no further issues in a day or two you can go ahead and accept your own answer, which is always okay in Stack Exchange.
uhoh
CC BY-SA 4.0
2022-08-10T00:03:35.673Z
A general comment: While SQL and ADQL don't strictly support regex's, they do have [LIKE](https://www.w3schools.com/sql/sql_like.asp) which supports wildcards. There is an example for `flags_gspspec`, admittedly pretty repetitious, at the [Gaia archive](https://www.cosmos.esa.int/web/gaia-users/archive/writing-queries) (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
astrosnapper
<observational-astronomy><galactic-dynamics><gaia>
Need help in writing a query using ADQL (Astronomical Data Query Language)
CTZenScientist
# Need help in writing a query using ADQL (Astronomical Data Query Language)

(Asked by: username_0 on 2022-08-08)

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](https://arxiv.org/abs/2206.05534). 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 combinations from elementary combinatorics and its not practical to write down all the possible combinations.

If anybody has worked on a similar problem before, please let me know.

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 is less of an astronomical question. In case this is not the right place, please redirect me to the right forum.

**Tags:** observational-astronomy, galactic-dynamics, gaia
> **Comments:**
> **username_1**: 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  
> **username_2**: @astrosnapper I think I found it (see edit re. [Alejandra Recio-Blanco](https://www.oca.eu/en/alejandra-recio-blanco))  
> **username_0**: @astrosnapper: The link to the paper (https://www.researchgate.net/publication/361274277_Gaia_Data_Release_3_Analysis_of_RVS_spectra_using_the_General_Stellar_Parametriser_from_spectroscopy)  
> **username_0**: 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!!!  
> **username_2**: @CTZenScientist that's great news! If things work out please feel free to post an answer to your own question, and if there's no further issues in a day or two you can go ahead and accept your own answer, which is always okay in Stack Exchange.  
> **username_1**: A general comment: While SQL and ADQL don't strictly support regex's, they do have [LIKE](https://www.w3schools.com/sql/sql_like.asp) which supports wildcards. There is an example for `flags_gspspec`, admittedly pretty repetitious, at the [Gaia archive](https://www.cosmos.esa.int/web/gaia-users/archive/writing-queries) (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  

---

## 1 answers

### Answer by username_0 on 2022-08-30. Score: 1 | Accepted answer

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.


---

