USGS Thesaurus and Enterprise Web Document Catalog

The thesaurus table describes formal thesauri. It identifies the thesauri that are available and, for each thesaurus, the database tables that contain its terms, both preferred and non-preferred, and the nonhierarchical relationships.

// Given the name of a thesaurus, determine its identifying code
// and the names of the database tables that will be used in SQL
// statements retrieving its terms

$name = "USGS Thesaurus";

$query = "select tag,tblname,nonpref,relterm from thesaurus where name='".addslashes($name)."'";
if (($result = mysql_query ($query,$dbh)) != False) {
	$n = mysql_num_rows ($result);

	// Since it's possible to have more than one version of a thesaurus
	// in the catalog, we specify that we want the last (most recent)
	// version.

	mysql_data_seek ($result,$n-1);

	$row = mysql_fetch_row ($result);
	$th_code    = $row[0];
	$th_name    = $name;
	$th_table   = $row[1];
	$th_uftable = $row[2];
	$th_rttable = $row[3];
	mysql_free_result ($result);
	}

Preferred thesaurus terms are stored in the table named in the field tblname. That table will have these fields:

// Obtain information about a given term:

$query = "select code,name,parent,scope from term where code=$term";
if (($result = mysql_query ($query,$dbh)) != False) {
    $row = mysql_fetch_row ($result);
    $code   = $row[0];
    $name   = $row[1];
    $parent = $row[2];
    $notes  = $row[3];
    mysql_free_result ($result);
    }

// Obtain the narrower terms of a given term:

$query = "select code,name from term where parent=$term";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $ntcode[$i] = $row[0];
        $ntname[$i] = $row[1];
        }
    mysql_free_result ($result);
    }

// Obtain all of the broader terms of a given term:

$parent = $code;
while ($parent != 0) {
    $query = "select code,name,parent from term where code=$parent";
    if (($result = mysql_query ($query,$dbh)) != False) {
        $row = mysql_fetch_row ($result);
        $pcode[$depth] = $row[0];
        $pname[$depth] = $row[1];
        $parent        = $row[2];
        mysql_free_result ($result);
        }
    }

Non-preferred terms are texts representing concepts that are subsumed in the preferred terms. Ordinarily each non-preferred term points to only one preferred term. Our thesaurus contains a few non-preferred terms that represent pre-coordination of two preferred terms, for example geologic maps combines the concepts of geology and maps and atlases; the non-preferred term geologic maps thus points to both of these preferred terms. Conjunctions like this are primarily used as aids in composing search terms. Non-preferred terms are stored in the table named in the field nonpref. That table has these fields:

// Given a non-preferred term, find the preferred term to which it points
$query = "select code,name from term where name like '%$searchtext%'";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $tcode[$i] = $row[0];
        $tname[$i] = $row[1];
        }
    mysql_free_result ($result);
    }

// Find all non-preferred terms for which this term
// is one of the preferred descriptors
$uf = array();
$query = "select name from nonpref where code=$term";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $uf[$i] = $row[0];
        }
    mysql_free_result ($result);
    }


// With the added complexity of the 'also' field, it is
// practical to determine the fuller meaning of the
// non-preferred term, but a more complex data structure
// must be used to keep related information together:

class NonPreferredTerm {
    var $name;
    var $acode;
    var $aname;
    function NonPreferredTerm ($the_name,$the_also) {
        $this->name  = $the_name;
        $this->acode = $the_also;
        $this->aname = '';
        }
    }

$uf = array();
$query = "select name,also from $nonpref where code=$term";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    if ($n > 0)
        for ($i=0; $i < $n; $i++) {
            $row = mysql_fetch_row ($result);
            $name = $row[0];
            $also = $row[1];
            $uf[$i] = new NonPreferredTerm ($name,$also);
            if ($also != '') {
                $q = "select name from $table where code=$also";
                if (($r = mysql_query ($q,$dbh)) != False) {
                    if (mysql_num_rows ($r) > 0) {
                        $rr = mysql_fetch_row ($r);
                        $uf[$i]->aname = $rr[0];
                        }
                    mysql_free_result ($r);
                    }
                }
            }
    mysql_free_result ($result);
    }

Non-hierarchical term relationships are stored in the table named in the field relterm. That table has fields

// For a given term, find the related terms

$query = "select b from relterm where a=$term";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $rtcode[$i] = $row[0];

        /* Look up the term record using its code */

        $q = "select name from term where code=$code";
        if (($r = mysql_query ($q,$dbh)) != False) {
            $rr = mysql_fetch_row ($r);
            $rtname[$i] = $rr[0];
            mysql_free_result ($r);
            }
        }
    mysql_free_result ($result);
    }

In addition to geographic overlaps, the database also contains a table specifying the bounding rectangles for each area (an area may have many bounding rectangles) and a table indicating which areas are adjacent. At this writing these tables are not used in the catalog interfaces.

Table bbox: Bounding coordinates for geographic areas

Table adjacent: Adjacent geographic areas

In this table, values of tag are unique.

In this table, values of tag are unique.

This document refers to a "catalog" using the analogy of a library card catalog; each card in the catalog describes a book on the shelf in the library. In the same manner, each Web document is described by a row of the table (cite) which contains bibliographic information. The fields of this table generally match elements of the FGDC metadata standard:

People or organizations to be cited as authors, editors, or sponsors of an information resource are identified in the associative table originator

// Obtain the citation information for a resource

$query = "select tag,title,pubdate,sername,issue,publish,onlink,email,descript,mdate,mtime,userid from cite where tag=$cite";
if (($result = mysql_query ($query,$dbh)) != False) {
    $row = mysql_fetch_row ($result);
    $tag      = $row[0];
    $title    = $row[1];
    $pubdate  = $row[2];
    $sername  = $row[3];
    $issue    = $row[4];
    $publish  = $row[5];
    $onlink   = $row[6];
    $email    = $row[7];
    $descript = $row[8];
    $mdate    = $row[9];
    $mtime    = $row[10];
    $userid   = $row[11];
    mysql_free_result ($result);

    // Get originators in sequence

    $query = "select name,role from originator where cite=$tag order by sequence";
    if (($result = mysql_query ($query,$dbh)) != False) {
        $n = mysql_num_rows ($result);
        for ($i=0; $i < $n; $i++) {
            $row = mysql_fetch_row ($result);
            $name[$i] = $row[0];
            $role[$i] = $row[1];
            }
        mysql_free_result ($result);
        }

    // Get series name from authority list

    $series_name = '';
    $query = "select name from series where tag=$sername";
    if (($result = mysql_query ($query,$dbh)) != False) {
        $row = mysql_fetch_row ($result);
        $series_name = $row[0];
        mysql_free_result ($result);
        }

    // Get publisher name from authority list

    $publisher_name = '';
    $query = "select name from publisher where tag=$publish";
    if (($result = mysql_query ($query,$dbh)) != False) {
        $row = mysql_fetch_row ($result);
        $publisher_name = $row[0];
        mysql_free_result ($result);
        }
    }

The assignment of an index term to a document is indicated by a row of the table keywords:

// Given a document, retrieve the index terms assigned to it

$query = "select term from keywords where cite=$cite and thesaurus=$thcode order by term";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $term[$i] = $row[0];
        }
    mysql_free_result ($result);
    }

// Given an index term, find the documents to which that
// term has been assigned

$query = "select k.cite,k.term,k.thesaurus,c.tag,c.title,c.onlink from keywords k,cite c where k.thesaurus=$thcode and k.term='$thisterm' and k.cite=c.tag";
if (($result = mysql_query ($query,$dbh)) != False) {
    $n = mysql_num_rows ($result);
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $cite[$i]   = $row[0];
        $title[$i]  = $row[4];
        $onlink[$i] = $row[5];
        }
    mysql_free_result ($result);
    }

To improve the responsiveness of the interface, the database has two tables that summarize the documents related to a term. The table inside contains records identifying an index term using its thesaurus and the term's code, and giving the number of records that have been assigned that term or any of the terms that are narrower than it (children, grandchildren, and so on, in the term hierarchy). The table within identifies terms in the same way but provides a reference to each document categorized using that term or any of the terms that are narrower than it. By consulting these tables, the interface can quickly present all of the documents that are relevant to a given thesaurus concept. This idea relies firmly on the notion that hierarchical relationships in the thesauri are "is a" relationships ("is a part of", "is a type of").

Table inside

Table within

// Given an index term, find the documents to which that
// term or any of its narrower terms (recursively) has
// been assigned

$query = "select cite from within where thesaurus=$thcode and code='$code'";
if (($result = mysql_query ($query,$dbh)) != False) {
    for ($i=0; $i < $n; $i++) {
        $row = mysql_fetch_row ($result);
        $doc[$i] = $row[0];
        }
    mysql_free_result ($result);
    }

Accessibility FOIA Privacy Policies and Notices

U.S. Department of the Interior | U.S. Geological Survey
URL: http://geo-nsdi.er.usgs.gov/thesaurus/catalog/documentation.html
Page Contact Information: Peter Schweitzer
Page Last Modified: Wednesday, 15-Mar-2006 17:51:47 EST