Functions

In computer programming, a function is a mini-program that takes
a given input, does something, and returns an output. In a database
search, functions have the same structure: they take inputs, execute a
mini-program, and return some output. The
Schema Browser lists over 100 functions. This tutorial will
cover two important functions.

Position Searches and fGetNearbyObjEq

In this tutorial, you have done several searches for objects near
a specific position, using the BETWEEN command in the where block.
But a function called fGetNearbyObjEq gives you more control over
where in the sky you search. The function fGetNearbyObjEq takes three
inputs: an RA, a dec, and a radius in arcminutes (the full moon is
about 30 arcminutes across). It returns all objects within the
circular area with a center at the specified (ra,dec) point and
a radius of the radius entered.

Because fGetNearbyObjEq returns a list of objects, you can search
through the list just like a table. You can include fGetNearbyObjEq
in the from block of your queries, and join it to a table or view using the
ObjID column. To search the sky for all galaxies within 5 arcminutes of
ra = 140, dec = 20, use this query:

Flags and Photo Flag Functions

For every object identified in an image, the SDSS stores a
set of flags, yes/no data telling whether some attribute is
true for that object. Flags are available for objects too close to the
edge of a frame, for objects whose images were saturated (so bright that
some light was not detected), or for objects with some pixels not detected
by the camera.

Flags are stored in the flags column of the PhotoObj
table (and its related views). Take a moment to look in the
Schema Browser to learn what the photo flags are. Then, search for
photo flags with the command "select flags from photoObj". What do you get?

You get nothing but a long, confusing number! Each object in the
PhotoObj table may have many true/false flags associated with it. To
save memory, the flags are not stored as true/false lists, but as components
of a long number - each flag is stored as a specific value at a specific
position.

Unfortunately, while this technique may save memory, it makes the
flags impossible for humans to understand. Fortunately, SkyServer
includes a function, fPhotoFlagsN, to translate photo flags
from confusing numbers into English names.

The syntax for calling a function in SQL is to type "dbo." - the letters
dbo and a period - in front of the function name. Then type the name, then
enclose the function's input(s) in parentheses. The function fPhotoFlagsN
takes one input, and it is (almost) always the flags column of the
PhotoObj table. So the syntax for calling the function would be:

dbo.fPhotoFlagsN(flags)

For example, you might want to find English names for flags of
all stars around the point 175,1. The query would look like this:

The query returns flags as both numbers and names. You can see that the
names are much easier to understand!

Sometimes, instead of looking at all flags for many objects, you might want to
select only objects that meet certain flags. In other words, you might
want to use a flag as part of a where block. Unfortunately, fPhotoFlagsN
can't search for a flag - it can only list flags that are present
for a certain object. Fortunately, SkyServer includes another function
that can help you search for flags - the similarly named fPhotoFlags.

To understand how fPhotoFlags works, remember that a flag is a
true/false statement. If a flag is true, it will have some
positive value, which will depend on the flag. But if the flag is
false, its value will be zero, and always zero. So to find out if a
flag is true for a given object, just compare its value to zero. If
the flag's value is greater than zero, the flag is true; if the flag's value
is equal to zero, it is false.

But how do you know which flag you are checking? The function
fPhotoFlags translates the name of a single flag.
For example, if you wanted to look for all unsaturated objects - all
objects with the SATURATED flag set to false - you could use
fPhotoFlags('SATURATED').

But remember that fPhotoFlags only translates the name
of the flag from computer-speak to English. To actually check the
value of the flag, you still need to search the flags column of the
photoObj table. To search records for the presence of an English-language
flag using fPhotoFlags, you need to use an ampersand (&). The syntax
is "where (flags & dbo.fPhotoFlags(name)) [> or =] 0". So to search
for all unsaturated objects near the point (175,1), you would use the
following query:

Be sure to enclose the name of the flag in single quotes. The equals sign
means you are searching for objects with the SATURATED flag set to
false. To search for saturated objects instead, change "=0" to
"> 0".

To summarize the difference between fPhotoFlagsN and fPhotoFlags, use
fPhotoFlagsN in the select block to view a record's flags in English.
Use fPhotoFlags in the where block, with an & sign, to search
all records for a specific flag.

To search a different area of the sky, just change the ra and
dec inputs to fGetNearbyObjEq. SkyServer also includes a similar
function called f.GetNearestObjEq that returns only the object
nearest to the coordinates you entered.

Try It!

Try pressing the buttons "Query 1," "Query 2," and "Query 3" below. These
buttons will make the three queries above (in the purple boxes) appear in the
query window. Press Submit to execute the queries.

Try running Query 1. Look at the ra and dec of the results to verify that
the query is looking in the right region of sky. How many objects are
in this region? How bright is the brightest one?

Next, try running Query 2. What are some of the most common flags in
the data? Why do you think they are so common?

Now, try running Query 3. Verify that you have selected only
unsaturated objects. Try changing the name of the flag to search for
objects with other features.

When you are ready to move on, click Next to get some more practice with
functions.

Format

HTML

XML

CSV

Enter your SQL query in the text box.
The query is limited to 10 minutes and 100,000 rows.