USRCAT-ZINC-screen.sql

    WITH conformer AS
         (
          SELECT openeye.omega('CC1=CC(=C(N1C2=CC(=CC(=C2)C(F)(F)F)C(F)(F)F)C)/C=C\3/C(=O)N=C(S3)NC4=CC=CC=C4') as oeb
         ),
         query AS
         (
          SELECT openeye.usrcat(oeb) AS usr_moments
            FROM conformer
         ),
         screen AS
         (
            SELECT zinc_id, 
                   arrayxd_usrcatsim(zm.usr_moments, query.usr_moments, 1.0, 1.0, 1.0, 1.0, 1.0) AS sim
              FROM zinc.moments_conf_idx_0 zm, query
             WHERE zm.usr_space <@ cube_enlarge(cube(query.usr_moments[1:12]), 1.0, 12)
                   AND arrayxd_usrcatsim(zm.usr_moments, query.usr_moments, 1.0, 1.0, 1.0, 1.0, 1.0) >= 0.6
          ORDER BY 2 DESC
             LIMIT 50
         )
  SELECT ism, c.zinc_id, sim
    FROM zinc.compounds c
    JOIN screen ON c.zinc_id = screen.zinc_id
ORDER BY 3 DESC