Open Babel › General discussion

FW: Mychem SQL query for Tanimoto function

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

3 messages Options

Martin Morissette

FW: Mychem SQL query for Tanimoto function

Hi, I would need some help to create a SQL query that returns hits ordered on the tanimoto coefficient of the queried smiles and the fingerprints stored in the database. So far, I was able to set the sql variable as follow :

SET @fp = (SELECT FINGERPRINT2(SMILES_TO_MOLECULE(‘smiles')));

Where the smiles is inserted with php from a JSME input

Then I want to modify the current substructure search query :

$query = "SELECT compounds.name AS No, pdt.fullname as Name FROM pdt,compounds,bin_structures WHERE pdt.name=compounds.name AND compounds.id=bin_structures.compound_id AND MATCH_SUBSTRUCT('".$_POST['smiles']."',obserialized)";

to compute the tanimoto of @fp and bin_structures.fp2

Whenever I try to compute TANIMOTO(@fp, fp2) from bin_structures I get a NULL value.

I tried the TANIMOTO function examples from the documentation but I always get NULL values.

If I use two variables @fp1 and @fp2 and set these values with 2 different smiles fingerprints, I can compute the TANIMOTO(@fp1, @fp2).

I use a LAMP stack on Ubuntu 14.04 64 bits.

Any help is welcome

Thank you

Martin

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
OpenBabel-discuss mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/openbabel-discuss

Fredrik Wallner

Re: FW: Mychem SQL query for Tanimoto function

Hi,

What versions of OpenBabel and Mychem do you use?

I also think that this question is best answered in the Mychem list so I forwarded it there.

Kind regards,

Fredrik

Skickat från min iPhone

20 okt. 2016 kl. 18:44 skrev Martin Morissette <[hidden email]>:

Hi, I would need some help to create a SQL query that returns hits ordered on the tanimoto coefficient of the queried smiles and the fingerprints stored in the database. So far, I was able to set the sql variable as follow :

SET @fp = (SELECT FINGERPRINT2(SMILES_TO_MOLECULE(‘smiles')));

Where the smiles is inserted with php from a JSME input

Then I want to modify the current substructure search query :

$query = "SELECT compounds.name AS No, pdt.fullname as Name FROM pdt,compounds,bin_structures WHERE pdt.name=compounds.name AND compounds.id=bin_structures.compound_id AND MATCH_SUBSTRUCT('".$_POST['smiles']."',obserialized)";

to compute the tanimoto of @fp and bin_structures.fp2

Whenever I try to compute TANIMOTO(@fp, fp2) from bin_structures I get a NULL value.

I tried the TANIMOTO function examples from the documentation but I always get NULL values.

If I use two variables @fp1 and @fp2 and set these values with 2 different smiles fingerprints, I can compute the TANIMOTO(@fp1, @fp2).

I use a LAMP stack on Ubuntu 14.04 64 bits.

Any help is welcome

Thank you

Martin

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot

_______________________________________________
OpenBabel-discuss mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/openbabel-discuss

Martin Morissette

Re: FW: Mychem SQL query for Tanimoto function

Hi Fredrik,

I use OpenBabel 2.3.2 and MyChem 0.9.3

I use :

PHP 5.5.9-1ubuntu4.17

Server version: 5.6.30-0ubuntu0.14.04.1

On a Digital Ocean Ubuntu 14.04 64bits droplet.

I installed openbabel with the following command:

sudo apt-get install openbabel

I can supply my whole installation procedure if you want. I ran the test module also and 2 conversion failed :

Running tests...

Test project /home/studur/mychem-code-0.9.3/build

Start 1: ConversionTest

1/5 Test #1: ConversionTest ...................***Failed 0.18 sec

Start 2: HelperTest

2/5 Test #2: HelperTest ....................... Passed 0.01 sec

Start 3: ModificationTest

3/5 Test #3: ModificationTest ................. Passed 0.02 sec

Start 4: MolmatchTest

4/5 Test #4: MolmatchTest ..................... Passed 0.06 sec

Start 5: PropertyTest

5/5 Test #5: PropertyTest .....................***Failed 0.05 sec

60% tests passed, 2 tests failed out of 5

Total Test time (real) = 0.32 sec

The following tests FAILED:

1 - ConversionTest (Failed)

5 - PropertyTest (Failed)

Errors while running CTest

make: *** [test] Error 8

Here is the log:

Start testing: Oct 20 14:27 EDT

----------------------------------------------------------

1/5 Testing: ConversionTest

1/5 Test: ConversionTest

Command: "/home/studur/mychem-code-0.9.3/build/tests/conversion_test" "-h" "localhost" "-u" "studur" "-p" "********" "-b" "mysql" "-d" "/home/studur/mychem-code-0.9.3/tests/data/"

Directory: /home/studur/mychem-code-0.9.3/build/tests

"ConversionTest" start time: Oct 20 14:27 EDT

Output:

----------------------------------------------------------

Testing molfile_to_molecule ... [OK]

Testing molecule_to_molfile ... [OK]

Testing V3000_to_molecule ... [OK]

Testing molecule_to_V3000 ... [OK]

Testing smiles_to_molecule ... [OK]

Testing molecule_to_smiles ... [OK]

Testing molecule_to_molecule ... [OK]

Testing cml_to_molecule ... [OK]

Testing molecule_to_cml ... [OK]

Testing fingerprint ... [OK]

Testing molecule_to_canonical_smiles ... [OK]

Testing inchi_to_molecule ... [OK]

Testing molecule_to_inchi ... [OK]

Testing molecule_to_serializedOBMol ... [OK]

Testing pdb_to_molecule ... [OK]

Testing mol2_to_molecule ... [FAILED]

Testing molecule_to_mol2 ... [FAILED]

Test time = 0.18 sec

----------------------------------------------------------

Test Failed.

"ConversionTest" end time: Oct 20 14:27 EDT

"ConversionTest" time elapsed: 00:00:00

----------------------------------------------------------

2/5 Testing: HelperTest

2/5 Test: HelperTest

Command: "/home/studur/mychem-code-0.9.3/build/tests/helper_test" "-h" "localhost" "-u" "studur" "-p" "********" "-b" "mysql" "-d" "/home/studur/mychem-code-0.9.3/tests/data/"

Directory: /home/studur/mychem-code-0.9.3/build/tests

"HelperTest" start time: Oct 20 14:27 EDT

Output:

----------------------------------------------------------

Testing mychem_version ... [OK]

Testing openbabel_version ... [OK]

Test time = 0.01 sec

----------------------------------------------------------

Test Passed.

"HelperTest" end time: Oct 20 14:27 EDT

"HelperTest" time elapsed: 00:00:00

----------------------------------------------------------

3/5 Testing: ModificationTest

3/5 Test: ModificationTest

Command: "/home/studur/mychem-code-0.9.3/build/tests/modification_test" "-h" "localhost" "-u" " studur " "-p" "********" "-b" "mysql" "-d" "/home/studur/mychem-code-0.9.3/tests/data/"

Directory: /home/studur/mychem-code-0.9.3/build/tests

"ModificationTest" start time: Oct 20 14:27 EDT

Output:

----------------------------------------------------------

Testing add_hydrogens ... [OK]

Testing remove_hydrogens ... [OK]

Testing strip_salts ... [OK]

Test time = 0.02 sec

----------------------------------------------------------

Test Passed.

"ModificationTest" end time: Oct 20 14:27 EDT

"ModificationTest" time elapsed: 00:00:00

----------------------------------------------------------

4/5 Testing: MolmatchTest

4/5 Test: MolmatchTest

Command: "/home/studur/mychem-code-0.9.3/build/tests/molmatch_test" "-h" "localhost" "-u" " studur " "-p" "********" "-b" "mysql" "-d" "/home/studur/mychem-code-0.9.3/tests/data/"

Directory: /home/studur/mychem-code-0.9.3/build/tests

"MolmatchTest" start time: Oct 20 14:27 EDT

Output:

----------------------------------------------------------

Testing match_substruct ... [OK]

Testing substruct_atom_ids ... [OK]

Testing substruct_count ... [OK]

Testing tanimoto ... [OK]

Testing bit_fp_and ... [OK]

Testing bit_fp_or ... [OK]

Testing bit_fp_count ... [OK]

Test time = 0.06 sec

----------------------------------------------------------

Test Passed.

"MolmatchTest" end time: Oct 20 14:27 EDT

"MolmatchTest" time elapsed: 00:00:00

----------------------------------------------------------

5/5 Testing: PropertyTest

5/5 Test: PropertyTest

Command: "/home/studur/mychem-code-0.9.3/build/tests/property_test" "-h" "localhost" "-u" " studur " "-p" "********" "-b" "mysql" "-d" "/home/studur/mychem-code-0.9.3/tests/data/"

Directory: /home/studur/mychem-code-0.9.3/build/tests

"PropertyTest" start time: Oct 20 14:27 EDT

Output:

----------------------------------------------------------

Testing molweight ... [OK]

Testing exactmass ... [OK]

Testing number_of_atoms ... [OK]

Testing number_of_heavy_atoms ... [OK]

Testing number_of_bonds ... [OK]

Testing number_of_rotable_bonds ... [OK]

Testing total_charge ... [OK]

Testing molformula ... [OK]

Testing number_of_acceptors ... [OK]

Testing number_of_donors ... [OK]

Testing molpsa ... [FAILED]

Testing molmr ... [FAILED]

Testing mollogp ... [FAILED]

Testing is_2D ... [OK]

Testing is_3D ... [OK]

Testing is_chiral ... [OK]

Testing number_of_rings ... [OK]

Test time = 0.05 sec

----------------------------------------------------------

Test Failed.

"PropertyTest" end time: Oct 20 14:27 EDT

"PropertyTest" time elapsed: 00:00:00

----------------------------------------------------------