Boolean Search Syntax¶
Marvin Queries use a pseudo-natural language SQL syntax. This is a short form version of SQL statements used to query
databases. SQL statements generally are of the form select
other tables where
filter conditions. This syntax is designed to simplify how much SQL you need to write. Rather than submitting the full SQL statement, you submit only a simplified where clause and an optional list of properties to return. This eliminates the need to have detailed knowledge of the MaNGA database schema, table design, available columns, and the keys needed to join the tables.
The syntax for Marvin Query where clauses is a boolean search string. Boolean search strings consist of a parameter-operand-value combination (e.g.,
a > 5), where
parameter is the variable name,
operand must be
==finds exact matches whereas
=finds elements that contain the value.
value can be a float, integer, or string.
Strings with spaces must be enclosed in quotes.
*acts a wildcard.
These parameter-operand-value combinations can be joined with the boolean operands (in order of descending precedence):
and grouped with parentheses
(). For example,:
a = 5 or b = 7 and not c = 7
is equivalent to:
a = 5 or (b = 7 and (not c = 7))
In addition to the standard operands, there are special operands as well.
between: selects within a range, e.g.
a between 1 and 2, which is equivalent to
a >= 1 and a <= 2
&: a bitwise operator to perform bitwise and selections, e.g.
a & 256, which selects rows where the maskbit value
& ~: a not bitwise operator, e.g.
a & ~256, which selects rows where the maskbit value
adoes not include 256.
Marvin provides two special function syntax strings to provide specific queries, the radial function, and the npergood function.
The radial function allows for cone searches to be performed around a given RA, Dec. The syntax is
radial(RA, Dec, radius). RA and Dec are given in decimal degrees and radius is in units of degrees. The radial function can be combined with other conditions:
# create filter to perform a radial cone search around a given RA, Dec point my_filter = 'radial(232.5447, 48.6902, 0.5)' # combine it with another selection on redshift my_filter = 'nsa.z < 0.1 and radial(232.5447, 48.6902, 0.5)'
The npergood function takes the form
npergood(condition) operand value, where condition can be a standard single condition of
parameter operand value. The npergood condition equates to “galaxies where the number of spaxels that meet a given condition is above some percentage threshold.” For example:
# create an npergood filter. my_filter = 'npergood(emline_gflux_ha_6564 > 25) > 20'
selects galaxies that have H-alpha flux values > 25 in more than 20% of their spaxels. The npergood condition cannot be combined with other conditions.
In MaNGA, parameter names have hierarchical dotted field names, and are structured as schema.table.parameter, e.g.
mangadatadb.cube.plateifu. If a parameter name only exists in one column in one database table, it is considered unique.
# The NSA Sersic log stellar mass (sersic_logmass) is a unique parameter in the NSA table my_filter = 'sersic_logmass < 1'
Many parameters are not unique. In this case, you must go one level up and specify the table name as well. It is best practice however to adopt the syntax of table.parameter to ensure a fully unique parameter selection.
# RA, Dec are not unique parameter names my_filter = 'ra > 180' query = Query(search_filter=my_filter) MarvinError: Could not set parameters. Multiple entries found for key. Be more specific: 'ra matches multiple parameters in the lookup table: mangasampledb.nsa.ra, mangadatadb.cube.ra'. # Correct filter my_filter = 'cube.ra > 180'
Example SQL Constructions¶
# Filter string my_filter = "nsa.z < 0.02 and ifu.name = 19*" # Converts to and_(nsa.z<0.02, ifu.name=19*) # SQL syntax mangasampledb.nsa.z < 0.02 AND lower(mangadatadb.ifudesign.name) LIKE lower('19%')
# Filter string my_filter = 'cube.plate < 8000 and ifu.name = 19 or not (nsa.z > 0.1 or not cube.ra > 225.)' # Converts to or_(and_(cube.plate<8000, ifu.name=19), not_(or_(nsa.z>0.1, not_(cube.ra>225.)))) # SQL syntax mangadatadb.cube.plate < 8000 AND lower(mangadatadb.ifudesign.name) LIKE lower(('%' || '19' || '%')) OR NOT (mangasampledb.nsa.z > 0.1 OR mangadatadb.cube.ra <= 225.0)
# Filter string my_filter = 'nsa.z < 0.1 or (nsa.sersic_logmass between 9.5 and 11)' # Converts to or_(nsa.z<0.1, nsa.sersic_logmassbetween9.5and11) # SQL syntax (mangasampledb.nsa.z < 0.1 OR CAST(CASE WHEN (mangasampledb.nsa.sersic_mass > 0.0) THEN log(mangasampledb.nsa.sersic_mass) WHEN (mangasampledb.nsa.sersic_mass = 0.0) THEN 0.0 END AS FLOAT) BETWEEN 9.5 AND 11.0)
For more details on boolean search string syntax see the ` original SQLAlchemy-boolean-search documentation <http://sqlalchemy-boolean-search.readthedocs.io/en/latest/>`_.