Using Structure Search with PostgreSQL in Spotfire Lead Discovery Premium

Keywords: PostgreSQL, structure search, RDKit, Spotfire, Lead Discovery Premium, parameterized information links, SQL queries, chemical structures, SMILES.

Description

This article provides guidance on how to perform structure searches in Spotfire Lead Discovery Premium when using a PostgreSQL database. The native structure search functionality in Spotfire Lead Discovery Premium is designed for Oracle databases, so this workaround is necessary for PostgreSQL users. By leveraging the RDKit Extension for PostgreSQL, users can achieve similar functionality to the Oracle Cartridge used in the native implementation.

Solution:

1. Install the RDKit Extension

Install the RDKit Extension in your PostgreSQL database. This extension provides the necessary chemical structure search capabilities.

2. Create Parameterized Information Links

Create parameterized information links in Spotfire using SQL queries that leverage RDKit functions.

3. Implement Query Types

Use the following query types in your information links to perform structure searches:

• Exact Match
• Substructure Search
• Similarity Search

4. Use Information Links in Spotfire

Once the information links are created, use them in Spotfire to perform structure searches.

Example Queries

Below are examples of SQL queries that can be used in parameterized information links after installing the RDKit Extension. These queries use SMILES (TEXT) data for chemical structures.

1. Exact Match

SELECT * 
FROM LeadDiscovery_Table 
WHERE rdkit_mol(chemical_structure) = rdkit_mol('CC(=O)OC1=CC=CC=C1C(O)=O');

2. Substructure Search

SELECT * 
FROM LeadDiscovery_Table 
WHERE rdkit_mol(chemical_structure) @> rdkit_mol('O=C(C)Oc1ccccc1C(=O)O');

3. Similarity Search

SELECT lead_id, lead_name, chemical_structure, 
rdkit_similarity(rdkit_mol(chemical_structure), rdkit_mol('CC(=O)OC1=CC=CC=C1C(O)=O')) AS similarity 
FROM LeadDiscovery_Table 
WHERE rdkit_similarity(rdkit_mol(chemical_structure), rdkit_mol('CC(=O)OC1=CC=CC=C1C(O)=O')) > 0.7 
ORDER BY similarity DESC;

Note: Replace 'CC(=O)OC1=CC=CC=C1C(O)' with the desired search structure or parameter.

Points to Consider

• Performance: Query performance may differ from the native Oracle-based solution. Optimize your queries and database configuration for better performance.
• RDKit Compatibility: Ensure that the RDKit Extension is properly installed and configured in your PostgreSQL database.
• Parameterization: Use parameterized information links to make your queries dynamic and reusable.

Outstanding Actions

• Customer Implementation: The customer is responsible for implementing the suggested SQL queries using parameterized information links in their Spotfire environment.

Additional Notes

• Workaround Necessity: This workaround is necessary because the native structure search functionality in Spotfire Lead Discovery Premium is designed for Oracle databases.
• RDKit Functionality: The RDKit Extension for PostgreSQL provides similar functionality to the Oracle Cartridge used in the native implementation.