Search Postgresql Archives

Using Full Text Search to create TurnItIn lite

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I've been having my students post journal entries to a Drupal site, and we just noticed that some of them have been copying from each other (surprise, surprise).  If this was done with reports or other submissions, we'd just submit the deliverables to TurnItIn.com and go from there.

In this case I'd rather run the similarity checking on our local server, and my guess is that Full Text Search is the way to go.  I've found a couple of online posts about such things, but they're not quite as clear as I was hoping.  I'm hoping that the list can help me out (and keep this from becoming an O(n^4) challenge).

What I have is essentially a table containing an ID and some TEXT.

My first attempt used the similarity() function from pg_trgm as follows:

select
   aid
  ,bid
  ,similarity(source,dest) sim
from
(
   select 
      a.id aid
     ,a.body source
     ,b.id bid
     ,b.body dest 
   from
     posts a
   cross join 
     posts b
   where
     and a.id != b.id
) as inside 
order by sim desc;

I haven't been thinking in SQL terms for a while, so I didn't come up with a way to eliminate half of my checks by exploiting symmetry (a simple way to accomplish this would be helpful).  Unfortunately this solution will take way too long to execute.

I'm hoping that the full text features can be shoehorned into this problem, but from what little I've seen it will be tricky because comparing a tsvector to another tsvector isn't supported.  Plus there's weird (to the novice) indexing that will (I hope) help to keep the complexity down.

Thoughts from the list are most welcome, and I'll write up a HOWTO once I get this working.

Thanks!

Jason
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux