Database Fields
Database Fields >>
Notes >>
Examples >>
SQL SELECT General Syntax
SELECT [DISTINCT | ALL] {* | <val> [, <val> ...]}
FROM <tableref> [, <tableref> ...]
[WHERE <search_condition>]
[ORDER BY <order_list>]
<val> = {
col | <constant> | <expr> | <function> | NULL
}
<constant> = num | 'string'
<function> = {
COUNT (* | [ALL] <val> | DISTINCT <val>)
| SUM ([ALL] <val> | DISTINCT <val>)
| AVG ([ALL] <val> | DISTINCT <val>)
| MAX ([ALL] <val> | DISTINCT <val>)
| MIN ([ALL] <val> | DISTINCT <val>)
| CAST (<val> AS <datatype>)
| UPPER (<val>)
}
<tableref> = <joined_table> | table [alias]
<joined_table> = <tableref> <join_type> JOIN <tableref>
ON <search_condition> | (<joined_table>)
<join-type> = {[INNER] | {LEFT | RIGHT} [OUTER]} JOIN
<search_condition> = {<val> <operator>
{<val> | (<select_one>)}
| <val> [NOT] BETWEEN <val> AND <val>
| <val> [NOT] LIKE <val> [ESCAPE <val>]
| <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
| <val> IS [NOT] NULL
| <val> {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} (<select_list>)
| EXISTS (<select_expr>)
| SINGULAR (<select_expr>)
| <val> [NOT] CONTAINING <val>
| <val> [NOT] STARTING [WITH] <val>
| (<search_condition>)
| NOT <search_condition>
| <search_condition> OR <search_condition>
| <search_condition> AND <search_condition>}
<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}
<select_one> = SELECT on a single column that returns exactly one row.
<select_list> = SELECT on a single column that returns zero or more rows.
<select_expr> = SELECT on a list of values that returns zero or more rows.
<order_list> =
{col | int} [COLLATE collation] [ASC[ENDING] | DESC[ENDING]]
[, <order_list>]
Argument | Description |
SELECT [DISTINCT | ALL] |
Specifies data to retrieve. DISTINCT prevents duplicate values from being returned.
ALL, the default, retrieves every value. |
{* | <val> [, <val> ...] |
* retrieves all columns for the specified tables. <val> [, <val> ...] retrieves a
specific list of columns and values. |
FROM <tableref> [, <tableref> ...] |
List of tables, views, and stored procedures from which to retrieve data. List can
include joins and joins can be nested. |
table |
Name of a table in the database |
alias |
Brief, alternate name for a table. After declaration in <tableref>,
alias can stand in for subsequent references to a table. |
<joined_table> |
A table reference consisting of a JOIN. |
<join_type> |
Type of join to perform. Default: INNER.
- INNER: Records must exist for both the tables on the left and right of the JOIN.
- LEFT [OUTER]: Records in the table on the left of the JOIN will be returned regardless
of whether there are any corresponding records in the table on the right of the JOIN.
- RIGHT [OUTER]: Records in the table on the right of the JOIN will be returned regardless
of whether there are any corresponding records in the table on the left of the JOIN.
|
WHERE <search_cond> |
Specifies a condition that limits rows retrieved to a subset of all available rows. |
ORDER BY <order_list> |
Specifies the order in which rows are returned. |
Syntax >>
Notes >>
Examples >>
Tables in this Database
Note that in the tables below, with the exception of the key fields mission, roll,
and frame, that fields may have the value of NULL.
Click on the linked field names for more detailed information.
FRAMES
Field Name | Possible Values | Description |
mission |
See the selection list on the query forms that list mission options for the possible values. |
This is the name of the mission using an approved standard for Earth Observations purposes. |
filmms |
See the selection list on the main form for the possible values. |
This is the name of the mission as it is printed on the film. |
roll |
NNNX where NNN is the right-justified numeric portion of the roll, no leading zeros,
and X is an alphabetic extension if available, otherwise it is blank. At least one mission
has odd roll IDs which start a letter. For these missions simply left justify. |
The roll identifies the roll of film the photograph is on.
For digitally-acquired images in the NASA collection, it is E. For digitally-acquired images
from other sources, it takes on other values:
Source | Value |
EarthKam |
ESC[y] where y is the EarthKam mission designation for ISS. Prior to ISS, the y is left off. |
|
frame |
NNNNNNNXX where NNNNNNN is the right-justified numeric portion of the frame, no leading zeros,
and XX is the right-justified alphabet extension on the frame ID e.g. " 1 A" & "1234567AB" |
The frame identifies the photograph in the roll. For digitally-acquired images, it is uniquely assigned
either in downlink sequence or based on the time the image was acquired. |
geon |
See the selection list on the main form for the possible values. |
This usually identifies the country. Sometimes, when a country was unavaliable,
it identifies the predominant feature, such as "CLOUDS" or "OCEAN". |
feat |
75 characters of text. |
This identifies features found in the photograph such as smoke or city names.
Since these values vary in type and order, it is suggested that you use CONTAINING
as the operator for searches. This will allow you to search for substrings and this
operator does a case insensitive search. We recommend you use the most specific terms
possible, and avoid generic terms such as RIVER or R which may be abbreviated, and
will occur in thousands of entries. |
lat |
Signed floating point number. Valid values range in [-90.0,90.0] although the
photographs will be virtually all between -60 and 60. |
This is the latitude of the centerpoint of the photograph. Negative values
indicate South of the equator. |
lon |
Signed floating point number. Valid values range in [-180.0,180.0]. |
This is the longitude of the centerpoint of the photograph. Negative values
indicate West of the prime meridian. |
expo |
"N" (normal), "U" (under-exposed), "O" (over-exposed), "F" (out of focus). |
Indicates the film exposure quality. Not tracked after STS096 (June 1999). |
tilt |
"NV" (near vertical), "LO" (low oblique), "HO" (high oblique), look angle. |
Indicates how the camera was tilted with respect to the Earth. "NV" indicates that
the camera was looking nearly straight down at the Earth. "LO" indicates that some angle
is noticeable but not enough to show the horizon. "HO" indicates that the tilt is great
enough that the horizon is visible. When enough information is available to calculate a
look angle, instead of using the tilt values of "NV" and "LO", the look angle from nadir
to the center of the photo as measured from the camera/spacecraft location is used. |
cldp |
Integer [0, 100]. |
Specifies what percentage of the photograph is clouds. |
steo |
"Y" or "N". |
"Y" means Yes there is an adjacent photograph of the same area.
"N" means No there is not. Not tracked after STS096 (June 1999) |
fclt |
Positive integer. Units are millimeters. |
Indicates the focal length of the camera lens used. Note that this is not
logged by the crew. So the most likely lens size determined by analyzing the
photograph. |
caption |
"Y" or "N". |
"Y" means Yes there is a caption in the captions table for this photograph. "N" means no there is
not. |
image |
Positive integer. |
This is a count of how many images there are for this photograph in the
images table. |
dir |
"N ", "NE", "E ", "SE", "S ", "SW", "W ", or "NW". |
Indicates the direction from the spacecraft nadir (suborbital) point to the center point of the photograph.
"N" stands for North, "S" for South, "E" for East, and "W" for West.
Since the camera can be rotated in any direction without affecting the direction
from the spacecraft nadir point to the center of the photograph, and since photographs
can be viewed at any rotation, this field does not suggest that the top of the photograph
is in the direction specified. This field is most useful for oblique photographs where it
is possible to see the direction from the camera, visible as the edge of the photograph
that appears to be the closest, to the center of the photograph, and thus determine the
compass direction associated with that vector.
|
onc, jnc |
Text fields. |
Specifies the ONC and JNC map identifications for navigational charts
produced by the Defense Mapping Agency. These fields were no longer recorded
beginning with STS093 (July 1999). |
nlat |
Signed floating point number. Valid values range in [-90.0,90.0] although the
photographs will be virtually all between -60 and 60. |
This is the latitude of the nadir point (suborbital point) of the space craft at the time
the photograph was taken. Negative values indicate South of the equator. |
nlon |
Signed floating point number. Valid values range in [-180.0,180.0]. |
This is the longitude of the nadir point (suborbital point) of the space craft at the time
the photograph was taken. Negative values indicate West of the prime meridian. |
pdate |
8 character text field consisting of numeric digits. |
The GMT date the photograph was taken in the form "YYYYMMDD" where YYYY is the year,
MM is the month, and DD is the day of the month. |
ptime |
6 character text field consisting of numeric digits. |
The GMT time the photograph was taken in the form "HHMMSS" where HH is the hour,
MM is the minute, and SS is the second. |
orb |
Positive integer. |
This is the orbit number the space craft was on when the photograph was taken. Note
that this is only meaningful on short duration flights which orbited the Earth. |
azi |
Positive integer in the range [0, 360). |
Specifies the angle in degrees from north to the location of the sun
measured clockwise at the nadir location. |
elev |
Integer in the range [-90, 90]. |
Specifies the sun elevation angle. This is the angle from the horizon to
the sun measured in degrees at nadir. Negative values indicate that the sun
is below the horizon. |
alt |
Positive integer. |
Specifies the altitude above the Earth's surface of the space craft in
nautical miles. |
camera |
This is a two character code. See the metatdata link at the left for possible values.
|
This is the make of camera. |
film |
This is an eight character code. See the metatdata link at the left for possible values. |
For film cameras, this is the type of film. For digital cameras, this gives the imaging format. |
CAPTIONS
Field Name | Possible Values | Description |
mission |
See the selection list on the main form for the possible values. |
This is the name of the mission using an approved standard for Earth Observations purposes. |
roll |
NNNX where NNN is the right-justified numeric portion of the roll, no leading zeros,
and X is an alphabetic extension if available, otherwise it is blank. At least one mission
has odd roll IDs which start a letter. For these missions simply left justify. |
The roll identifies the roll of film the photograph is on.
For digitally-acquired images in the NASA collection, it is E. For digitally-acquired images
from other sources, it takes on other values:
Source | Value |
EarthKam |
ESC[y] where y is the EarthKam mission designation for ISS. Prior to ISS, the y is left off. |
|
frame |
NNNNNNNXX where NNNNNNN is the right-justified numeric portion of the frame, no leading zeros,
and XX is the right-justified alphabet extension on the frame ID e.g. " 1 A" & "1234567AB" |
The frame identifies the photograph in the roll. For digitally-acquired images, it is uniquely assigned
either in downlink sequence or based on the time the image was acquired. |
caption |
Text. |
This contains all the captions along with their HTML in a single 32765 character
text field. |
IMAGES
Field Name | Possible Values | Description |
mission |
See the selection list on the main form for the possible values. |
This is the name of the mission using an approved standard for Earth Observations purposes. |
roll |
NNNX where NNN is the right-justified numeric portion of the roll, no leading zeros,
and X is an alphabetic extension if available, otherwise it is blank. At least one mission
has odd roll IDs which start a letter. For these missions simply left justify. |
The roll identifies the roll of film the photograph is on.
For digitally-acquired images in the NASA collection, it is E. For digitally-acquired images
from other sources, it takes on other values:
Source | Value |
EarthKam |
ESC[y] where y is the EarthKam mission designation for ISS. Prior to ISS, the y is left off. |
|
frame |
NNNNNNNXX where NNNNNNN is the right-justified numeric portion of the frame, no leading zeros,
and XX is the right-justified alphabet extension on the frame ID e.g. " 1 A" & "1234567AB" |
The frame identifies the photograph in the roll. For digitally-acquired images, it is uniquely assigned
either in downlink sequence or based on the time the image was acquired. |
version |
3 character text field. Valid values are quoted positive integers other
than 1 e.g. "2 " and "123". |
This identifies how many different images of a given type (see the type field) there
are for the photograph. If there is only one version, then this field is NULL. |
filename |
Text field of the form mission-roll-frame_version.type where
_version only exists if the version is not NULL. There are no spaces
in this field. |
This is the name of the image file. |
width, height |
Positive integers. |
Specifies the width and height of the image in pixels. |
annotated |
"Y" or "N". |
"Y" means Yes the image is annotated. "N" means No it is not. |
cropped |
"Y" or "N". |
"Y" means Yes the image is cropped. "N" means No it is not. |
directory |
"SCANNED" or "lowres\mission". |
Specifies the directory where the image file is located. |
purpose |
Text. |
This is a brief phrase identifying the purpose for why this image file
was created. |
comments |
Text. |
Any comments deemed appropriate. |
filesize |
positive integer. |
The size of the image file in bytes. |
Syntax >>
Database Fields >>
Examples >>
Notes
- The SQL operators, table names, and field names are not case sensitive.
- LAT, LON, NLAT, and NLON are treated as straight numbers e.g. -50 degrees latitude is
always considered less than -40 degrees latitude even though -50 is at a larger
inclination.
- You can use this interface to query tables other than the frames table. The Web
form bases its queries on the frames table, so to submit queries which do not reference
the frames table, use this interface.
- Be aware that the frames table has a field named "caption" as does the
captions table but that these two fields contain different data and are vastly
different in size. The frames table caption field is only one character whereas
the captions table caption field is over 32000 characters.
- The anchors in the table of results you get when you submit your SQL query will only
show up if your results contain unabiguous values for the fields mission, roll, and frame.
- If you do an OUTER join (LEFT and RIGHT joins are OUTER joins) and use * for the field
list for either table, the fields that exist in both tables that have the same name will show
up twice and one will have values and the other won't if corresponding records do not exist in
both tables. This is because the SQL server will return fields with the same name for each
table in which the name exists. For example,
SELECT f.*,i.* FROM frames f LEFT JOIN images i ON f.mission=i.mission AND f.roll=i.roll AND f.frame=i.frame WHERE f.mission='MER3'
will return two MISSION and ROLL fields and one of them will be blank because those fields
exist by that name in both the frames and images tables but the rolls table contains no
entries for MER3. When this happens, then the table you get that displays your
query results will not have an anchor for viewing more information on the record. This
is because the software detects the NULL values for the key fields.
You can resolve this problem by using the SQL AS operator to cause the returned fields to have
different names in cases where there is name contention. For example:
SELECT f.*,i.mission AS imagems,i.roll AS imageroll,i.width,i.height FROM frames f LEFT JOIN images i ON f.mission=i.mission AND f.roll=i.roll WHERE f.mission="MER3"
Note that on INNER joins, even though duplicate fields will be returned, if you've joined
using the table's key fields then records will be returned only when they exist in both
tables and therefore the key fields will be equal.
- It use to be that data values could be specified in quotation marks (").
Since the database software was updated, the new version reserves the quotation marks for
field and other such token names to allow them to have spaces. So now when you specify
data values in your queries you will need to use apostrophies ('). For example:
SELECT COUNT(*) FROM frames WHERE mission="MER3"
is not valid. Now this syntax is required:
SELECT COUNT(*) FROM frames WHERE mission='MER3'
Syntax >>
Database Fields >>
Notes >>
Example SQL Statements
This selects all fields from records in the images table for a mission that may or
may not exist in the frames table:
SELECT * FROM images WHERE mission='STS089'
This returns a count of how many images there are under the scanned directory of the
images table:
SELECT COUNT(*) FROM images WHERE directory STARTING WITH 'scanned'
This returns a count of how many images there are in the images table:
SELECT COUNT(*) FROM images
This returns a count of how many images there are in the images table that have comments:
SELECT COUNT(comments) FROM images
This returns a list of photo IDs (mission, roll, frame) for images under the scanned directory:
SELECT mission,roll,frame FROM images WHERE directory STARTING WITH 'scanned'
This returns a list of photos that have captions:
SELECT mission,roll,frame,lat,lon,geon,feat,pdate,expo,cldp,tilt,image,caption
FROM frames WHERE caption='Y'
This returns a list of photos that have the word 'Houston' (not case sensitive) in their
caption regardless of whether or not the photo exists in the frames table:
SELECT mission,roll,frame FROM captions WHERE caption CONTAINING 'Houston'
This returns a count of records that have no center point nor nadir point:
SELECT count(*) FROM frames WHERE lat IS NULL AND nlat IS NULL
Only the latitude fields are queried because there should be no cases in which either the
latitude or longitude is NULL but the other is not NULL.
This returns a count of how many records have captions but do not have a corresponding record in the frames table:
SELECT count(*) FROM captions c LEFT JOIN frames f ON c.mission=f.mission AND c.roll=f.roll AND c.frame=f.frame WHERE frames.caption IS NULL
This shows the caption records from the above query that have no frames table record:
SELECT c.mission,c.roll,c.frame FROM captions c LEFT JOIN frames f ON c.mission=f.mission AND c.roll=f.roll AND c.frame=f.frame WHERE f.caption IS NULL
This lists the image records that do not have a corresponding frames record:
SELECT DISTINCT i.mission,i.roll,i.frame FROM images i LEFT JOIN frames f ON i.mission=f.mission AND i.roll=f.roll AND i.frame=f.frame WHERE f.mission IS NULL
This counts how many rolls of film are of type 5017:
SELECT count(*) FROM rolls WHERE film='5017'
This displays all the captions containing the word 'Houston' regardless of case:
SELECT * FROM captions WHERE caption CONTAINING 'Houston'
This lists the distinct FILMMS values for ISS002 along with counts of how many records there are of each:
SELECT filmms,COUNT(filmms) FROM frames WHERE mission='ISS002' GROUP BY filmms
Note that the GROUP BY takes care of providing the distinct list so no DISTINCT keyword is used.
|