similarity search using cartridge index, and return sim

Hi,

Can you remind me the syntax for doing the similarity search using cartrige in SQL? I also want to return hits' tanimoto coeffcient.

Thanks

Dong

Hi Dong,

Two ways to select the first 2000 structures more similar to benzene than 0.8 (assuming that the structures are stored in a VARCHAR2 or CLOB column):

select id,  1 - jc_dissimilarity(structure, 'c1ccccc1', 'dissimilarityMetric:tanimoto') from mytable where
jc_dissimilarity(structure, 'c1ccccc1', 'dissimilarityMetric:tanimoto
maxHitCount:2000') < 0.2;

select id, jc_tanimoto(structure, 'c1ccccc1') from mytable where jc_compare(structure, 'c1ccccc1', 't:i simThreshold:0.8 maxHitCount:2000') = 1;

Peter

I suupose I can use JDBC's prepareStatment to construct a SQL template as

select id, jc_tanimoto(structure) from mytable where jc_compare(structure, '?', 't:i simThreshold:0.8 maxHitCount:2000') = 1;

then substitute ? with a number of queries  mol smiles

Thanks

Dong

I assume you mean something like:

while (myQueryStructures.hasNext()) {
    preparedStatement.setString(1, myQueryStructures.next());
    ResultSet resultSet = preparedStatement.executeQuery();
    processResults(resultSet);
}

?

This should perfectly work.

Peter

Right, I suupose it should work but never try this before.

But when I run following in SQLPlus I am getting following error, I can do substructure search using jc_contains just fine. What did I miss?. smiles is the table name, and column smiles is indexed using cartridge

 

SQL> select id, jc_tanimoto(smiles) from smiles where jc_compare(smiles,'c1ncccc1','t:i simThreshold:0.8 maxHitCount:2000') = 1;

  ERROR at line 1:
ORA-29900: operator binding does not exist
ORA-06553: PLS-306: wrong number or types of arguments in call to 'JC_TANIMOTO'

Thanks

Dong

OK, following works

select id from smiles where jc_compare(smiles,'c1ncccc1','t:i simThreshold:0.8 maxHitCount:2000') = 1

but I really want to know the tanimoto coeffcient of the hits.

 

Dong

I am sorry, my second example above was not correct. You need to specify the query (again) for the jc_tanimoto operator:

select id, jc_tanimoto(structure, 'c1ccccc1') from mytable where jc_compare(structure, 'c1ccccc1', 't:i simThreshold:0.8 maxHitCount:2000') = 1;

(I have corrected my previous post to show the correct SQL.)

( You'll then have to have two place holders in your JDBC SQL and two calls to PreparedStatement.setString(int,String): setString(1, 'c1ccccc1') and setString(2, 'c1ccccc1') )

Peter

Thanks Peter!!!

Dong