University of Glasgow

Roderic Page

Continuing with my [exploration of the Biodiversity Heritage
Library](http://iphylo.blogspot.com/search?q=bhl) one obstacle to
linking BHL content with nomenclature databases is the lack of a
consistent way to refer to the same bibliographic item (e.g., book or
journal). For example, the Amphibia Species of the World (ASW) page for
[*Gastrotheca
aureomaculata*](http://research.amnh.org/herpetology/amphibia/names.php?taxon=Gastrotheca+aureomaculata)
gives the first reference for this name as:  
  

::: {style="font-family:Courier,fixed;font-size:12px"}
Gastrotheca aureomaculata Cochran and Goin, 1970, Bull. U.S. Natl. Mus.,
288: 177. Holotype: FMNH 69701, by original designation. Type locality:
\"in \[Departamento\] Huila, Colombia, at San Antonio, a small village
25 kilometers west of San Agustín, at 2,300 meters\".
:::

  
  
The journal that ASW abbreviates as \"Bull. U.S. Natl. Mus.\" is in the
BHL, which gives its title as \"Bulletin - United States National
Museum.\". How do I link these two records? In my [bioGUID
OpenURL](http://bioguid.info/openurl) project I\'ve been doing things
like using SQL LIKE statements with periods (.) replaced by wildcards
(\'%\') to find journal titles that match abbreviations (as well as
building a database of these abbreviations). But this is error prone,
and won\'t work for abbreviations such as \"Bull. U.S. Natl. Mus.\"
because the word \"National\" has been abbreviated to \"Natl\", which
isn\'t a substring of \"National\".  
  
After exploring various methods (including longest common subsequences,
and sequence alignment algorithms) I came across a [MySQL plugin for
n-grams](http://sourceforge.net/apps/mediawiki/mysqlftppc/index.php?title=Main_Page).
The plugin tokenises strings into bi-grams (tokens with just two
characters, see the [Wikipedia page on N-grams for more
information](http://en.wikipedia.org/wiki/N-gram)). This means that even
though as words
\"[National](http://www.wolframalpha.com/input/?i=bigrams+%22National%22)\"
and \"[Natl](http://www.wolframalpha.com/input/?i=bigrams+%22Natl%22)\"
are different, they will have some similarity due to the shared bi-grams
\"Na\" and \"at\".  
  
So, I grabbed [the source for the
plugin](http://sourceforge.net/projects/mysqlftppc) and the [ICU
dependency](http://site.icu-project.org/), compiled the plugin and added
it to MySQL (I\'m running MySQL 5.1.34 on Mac OS X 10.5.8). The plugin
can be added while the MySQL server is running using this SQL command:  

``` {.brush: .sql}
INSTALL PLUGIN bigram SONAME 'libftbigram.so';
```

  
Initial experiments seem promising. For the **bhl_title** table I
created a bi-gram index:  

``` {.brush: .sql}
ALTER TABLE `bhl_title` ADD FULLTEXT (`ShortTitle`) WITH PARSER bigram;
```

  
If I then take the abbreviation \"Bull. U.S. Natl. Mus.\", strip out the
punctuation, and search for the resulting string (\"Bull US Natl
Mus\")  

``` {.brush: .sql}
SELECT TitleID, ShortTitle, MATCH(ShortTitle) AGAINST('Bull U S Natl Museum')
AS score FROM bhl_title
WHERE MATCH(ShortTitle) AGAINST('Bull U S Natl Museum') LIMIT 5;
```

  
I get this:

| TitleID | ShortTitle                                         | score            |
|---------|----------------------------------------------------|------------------|
| 7548    | Bulletin - United States National Museum.          | 19.4019603729248 |
| 13855   | Bulletin du Muséum National d\'Histoire Naturelle. | 17.6493873596191 |
| 14964   | Bulletin du Muséum National d\'Histoire Naturelle. | 17.6493873596191 |
| 5943    | Bulletin du Muséum national d\'histoire naturelle. | 17.6493873596191 |
| 12908   | Bulletin du Muséum National d\'Histoire Naturelle. | 17.6493873596191 |

  
  
The journal we want is the top hit (if only just). I\'ll probably have
to do some post-processing to check that the top hit makes sense (e.g.,
is it a supersequence of the search term?) but this looks like a
promising way to match abbreviated journal names and book titles to
records in BHL (and other databases).  
  
  
  


Continuing with my exploration of the Biodiversity Heritage Library one obstacle to linking BHL content with nomenclature databases is the lack of a consistent way to refer to the same bibliographic item (e.g., book or journal). For example, the Amphibia Species of the World (ASW) page for
<em>
 Gastrotheca aureomaculata
</em>
gives the first reference for this name as:  Gastrotheca aureomaculata Cochran and Goin, 1970, Bull. U.S. Natl.

Messaggi di Rogue Scholar

n-gram fulltext indexing in MySQL