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:

SELECT
    p.ObjID, p.ra, p.dec, p.u, p.g, p.r, p.i, p.z
FROM photoObj p
JOIN dbo.fGetNearbyObjEq(140,20,5) n ON n.objID = p.objID
WHERE
    p.type = 3

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:

SELECT
    p.objID, p.flags, dbo.fPhotoFlagsN(p.flags)
FROM photoObj p
JOIN dbo.fGetNearbyObjEq(140,20,5) n ON n.objID = p.objID
WHERE
    p.type = 6

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:

SELECT
    p.objID, p.ra, p.dec, dbo.fPhotoFlagsN(p.flags)
FROM photoObj p
JOIN dbo.fGetNearbyObjEq(140,20,5) n ON n.objID = p.objID
WHERE
    (p.flags & dbo.fPhotoFlags('SATURATED')) = 0

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.