Cleaning up text for indexing in DB

[TVSET]

Dear Monks,

I am working on a knowledge base for our network operations center. Knowledge base is a kind of collection of categorized questions and answers, sometimes with attachments.

One of the functions of the knowledge base is searching through the items. Results should be sorted by relevancy with the most ranked item up on the list. In order to achieve this, I count the occurancy of every word in the knowledge item text and store this counts in the separate table. The structure is somewhat like this:

+---------+------+-------+
| item_id | word | count |
+---------+------+-------+

Now, the tricky part is that both HTML and text are allowed, which means that I need to somehow identify if the item being entered is a text or HTML (I am thinking of a radio selection), and then cleaning out all HTMl staff if needed, plus all the punctuation and other crap.

All solutions that I have in my mind are somewhat messy and ugly (regexps, etc). HTML parsing modules can be applied to a certain degree, but noone forces people to enter valid HTML. Secondly, all the dots, quotes, semicolumns, etc give a headache.

Any suggestions from the wise ones? :)

Leonid Mamtchenkov aka TVSET

[bobn]

"all the dots, quotes, semicolumns, etc" are usually taken care of by the quote() method within DBI (you are uysing DBI, right?). Though for keywords, you may just what to use what seems to be the Google approach and get rid of all punctuation in your indices and in terms entered for searches.

Why are you letting users enter HTML? If you're getting it from another source, that's different, though, you may be stuck with it.

Do the HTML parsing modules break for you when confronted with invalid HTML? Given the crap floating aroud the web, I'd be surprised, especially since all you want to do is get rid of the tags. Have you tested any of the modules?

My suggestion would be to cobble together some test data and throw it at some modules to see the results.
--Bob Niederman, http://bob-n.com

[TVSET]

"all the dots, quotes, semicolumns, etc" are usually taken care of by the quote() method within DBI

You misunderstood me here a bit. Here's an example: if the text of the new item will be something like "I really, really like this node.", then the index will be something like:

"I" -> 1,
"really," -> 1,
"really" -> 1,
"like" -> 1,
"this" -> 1,
"node." -> 1

I would expect a different result in the finished program, though. :)

Why are you letting users enter HTML? If you're getting it from another source, that's different, though, you may be stuck with it.

Because my users are kind of advanced. :) Sometimes they need plain text, but sometimes they need a few tags to better express themselves. For example, they do tables and links.

After some more thinking (as usualy, after posting), I decided to separate two issues I've raised in the original post. One issue is to get rid of HTML before creating index, and another issue is to get rid of the punctuation (dots, quotes, etc). This is much clearer to me know and I thank you for that. :)

Do the HTML parsing modules break for you when confronted with invalid HTML? Given the crap floating aroud the web, I'd be surprised, especially since all you want to do is get rid of the tags. Have you tested any of the modules?

I haven't tried it yet, but it is about time I do. :)

Leonid Mamtchenkov aka TVSET

[Skeeve]

If it's just for indexing, I'd first try it with...

$_=join('',<INFILE>);
s/\s+/ /g;  # clean all whitespace
s/<[^>]*>//g; # clean all HTML-Like tags
s/[^a-z]/ /gi; # Remove all but letters
grep ++$count{$_} && undef, split;

This will give you a hash of all words.

[halley]

"This won't help key-word scanning in a résumé."

qw(This won t help key word scanning in a r sum)

Apostrophes are part of a word but quotes are not. Hyphens are part of a word but dashes are not. The distinctions in a typed text are subtle. And the definition of a letter is not as simple as m/[a-z]/i.

--
[ e d @ h a l l e y . c c ]

[Cody Pendant]

I decided when working on something similar that a word for me could contain a-z, single-quotes, and hyphens, then had to code around words in single quotes, so it wasn't as simple as /[a-z'-]/.

I think I ended up with

my @words = /(\w[\w'-]*\w|\w+)/g;

or similar.

---




“Every bit of code is either naturally related to the problem at hand, or else it's an accidental side effect of the fact that you happened to solve the problem using a digital computer.”
M-J D

[Skeeve]

I know. Especially because I'm German and our letter-line would have to become (at least) s/[^a-zäöüßÄÖÜ]/ /i because of our Umlaute.

Nevertheless. It's a first shot and it seemed to help TVSET in solving his problem.

[TVSET]

Thanks a lot. That is very close to what I wanted. I'll need to play with the "Remove all but letters" line, but overall it's very near. :)

Leonid Mamtchenkov aka TVSET

[LTjake]

Hi, the process you are talking about is sometimes called "normalization".

We have a catalogue database (of books) parsed from MARC records. When storing things like "title" we also store a normalized value for searching purposes. We follow the NACO normalization rules for our conversion.

Here's the function we currently use. It steals from code from Pod::Escapes to get rid of diacritics... it still needs some work, though (any comments on this function are appreciated).

Note: it doesn't take into account any HTML.

# LUT to convert diacritical and special characters
# Modified from Pod::Escapes
my (%Latin1Code_to_fallback, %Latin1Char_to_fallback);
%Latin1Code_to_fallback = ();
@Latin1Code_to_fallback{0xA0 .. 0xFF} = (
' ', qq{!}, qq{C/}, 'PS', qq{\$?}, qq{Y=}, qq{|}, 'SS', qq{"}, ' ', 'a
+', qq{<<}, qq{!}, "",  ' ', qq{-},
' ', ' ', '2', '3', qq{'}, 'u', 'P', qq{*}, qq{,}, '1', 'o', qq{>>}, q
+q{1/4}, qq{1/2}, qq{3/4}, qq{?},
'A', 'A', 'A', 'A', 'A', 'A', 'AE', 'C', 'E', 'E', 'E', 'E', 'I', 'I',
+ 'I', 'I',
'D', 'N', 'O', 'O', 'O', 'O', 'O', 'x', 'O', 'U', 'U', 'U', 'U', 'U', 
+'Th', 'ss',
'a', 'a', 'a', 'a', 'a', 'a', 'ae', 'c', 'e', 'e', 'e', 'e', 'i', 'i',
+ 'i', 'i',
'd', 'n', 'o', 'o', 'o', 'o', 'o', qq{/}, 'o', 'u', 'u', 'u', 'u', 'y'
+, 'th', 'y',
);

{
  %Latin1Char_to_fallback = ();
  my($k,$v);
  while( ($k,$v) = each %Latin1Code_to_fallback) {
    $Latin1Char_to_fallback{chr $k} = $v;
  }
}

sub normalize {
    my $data = shift;

    # Rules taken from NACO Normalization
    # http://lcweb.loc.gov/catdir/pcc/naco/normrule.html

    # Convert special chars to spaces
    $data =~ s/[\Q!(){}<>-;:.?,\/\\@*%=\$^~\E]/ /g;
    # Delete special chars
    $data =~ s/[\Q'[]|\E]//g;
    # Remove diacritical marks and convert special chars
    my @chars = split(//, $data);
    for (my $i = 0; $i < @chars; $i++) {
        $chars[$i] = $Latin1Char_to_fallback{$chars[$i]} if (ord($char
+s[$i]) >= 160 && ord($chars[$i]) <= 255);
    }
    $data = join('', @chars);
    # Convert lowercase to uppercase.
    $data =~ tr/a-z/A-Z/;
    # Remove leading and trailing spaces
    $data =~ s/^\s+|\s+$//g;
    # Condense multiple spaces
    $data =~ s/\s+/ /g;

    return $data;
}

--
"To err is human, but to really foul things up you need a computer." --Paul Ehrlich

[t'mo]

As other monks suggested in another thread, why not get the text via the program 'lynx', letting it get rid of any HTML-junk for you?

$text = `lynx -dump $url`;

---

...every application I have ever worked on is a glorified munger...

[TVSET]

lynx will not get rid of the punctuation for me :)

Leonid Mamtchenkov aka TVSET

[bfdi533]

One thing that might help witht he possibly invalid html is a project called tidy (http://tidy.sourceforge.net/) and it has a library version as well.

You could call this and pass it the html code and it will clean it up for you before you remove the tags. This would make it easier so that if someone entered invalid html or forgot to close a tag, it would not hose up the regexen.

Ed