clustering (editing of records in some order) of DB

User 247c00dc1d

30-07-2012 09:38:34



Please, tell me the solution to my problem.

I have a DB where a lot of different compounds from all types: different amines, alcohols, acids, nitriles, thioles, ketones etc. I need to arrange them in a logical order - firstly by the classes of compounds, and within each class, for example aliphatic amines should be in order: primary aliphatic, then primary amines with the trifluoromethyl group, then the secondary aliphatic, etc. and so in each classes.


ChemAxon 60ee1f1328

30-07-2012 10:06:50

What a pleasurable conundrum !

The first thing to consider is that in order to "order by" these need to be real fields and not calculated ones.

One option to consider is add two new flags

CHEMICAL_CLASS VARCHAR (X) <= some TLA or abbreviation of your choice

CHEMICAL_ORDER NUMBER (2) <= for primary, secondary, tertiary (1,2,3)

Then run a series of updates using SQL that updates them correctly.

[You need to make sure your happy with the outcome of your SSS results of course !]

You could maintain these columns in future using triggers but such object could have quite a lot of overhead, for example I think trigger with such logic will slow down a large import significantly.

Of course we have not considered the realistic scenario of molecule records that can fall into many of these categories - does one functional group have priority over the other!?  Perhaps a series of CHEMICAL_CLASS  Boolean flags (and associated CHEMICAL_CLASS_ORDER), one for each chemical category is more appropriate in this scenario...


User 247c00dc1d

30-07-2012 10:15:02

Daniels, thanks for this answer!

But how to realize it in the IJC?



ChemAxon 60ee1f1328

30-07-2012 10:43:39

Depending upon which case you decide you will likely need some small change to your data model, probably on the structures table and then you will need a sqlplus script with some update statements, however I have now realised that it is quite possible you are not using Oracle...if you are using Derby I suppose you might achieve the same with a Java class...Directly in sounds quite manual, perhaps this could be done with a calculated field that either set the flag or generate dynamic SQL which you can extract and run <= I will need to check out the last ideas and get back to you !


User 247c00dc1d

30-07-2012 10:49:16

OK! Will be waiting for you:)

ChemAxon 60ee1f1328

30-07-2012 10:50:33

How about the Chemical terms match function - I guess this could be used to create boolean flags?

ChemAxon 60ee1f1328

30-07-2012 10:56:25

Yes - this is the way to do it...

For example please see screenshot for example...

User 247c00dc1d

01-08-2012 12:22:55

DanielSButler , but it's not exactly what I wanted.

I need in INJ realize the process that in JC doing by this script: "jklustor input.sdf-c sphex: 0.6-o wrmols: sdf: Output.sdf

or it's impossible and every time when I need clustering of Lists from DB I have to  export my structures-list to sdf, followed run the script and again import obtained clustered sdf into DB?

ChemAxon 60ee1f1328

01-08-2012 12:53:56



I think this is another requirement again - I am checking with my colleague as to the status of this, since I am sure it is in the pipeline for integration/scripting...



ChemAxon fa971619eb

07-08-2012 14:02:32

Hi, currently JKlustor is not integrated into IJC, and can only be run externally. But we are wanting to provide some integration for simple cases such as sphere exclusion clustering, but nothing is underway yet. If you'd be interested in trying this out once its done, or have further ideas of what might be useful then please let us know.