Example USRCAT query demonstrating the use of the cube extension

usrcat-query.sql

-- select a query molecule
WITH query AS
(
    SELECT * 
      FROM dude.usr_moments_fnta 
     WHERE category = 'ACTIVE' AND mol_idx = 1 AND conformer = 0
)
-- run the virtual screen with the given query
  SELECT category, mol_idx, MAX(sq.similarity) as similarity
    FROM (
          SELECT t.category,
                 t.mol_idx,
                 arrayxd_usrcatsim(t.usr_moments, query.usr_moments, 1.0, 1.0, 1.0, 1.0, 1.0) as similarity -- function from pgeigen extension
            FROM dude.usr_moments_fnta t, query
            WHERE t.usr_space <@ cube_enlarge(query.usr_space, 1.5, 12) -- cube index
         ) sq
GROUP BY category, sq.mol_idx
ORDER BY 3 DESC
   LIMIT 100