[Gmod-schema] alignment hits with featureloc entries

[Scott C. <ca...@cs...>]

Hello all,

First, I wanted to announce that I've used SQL provided by Chris to add
alignment hits with locations to featureloc in the most recent port of
gadfly to chado (code I actually used below).  And it appeared to work
correctly; that is, it executed without an error and the pictures that
it creates in gbrowse seem correct (ie, for the most part, the HSPs line
up under annotated exons).  

So, as a proof of principle, I think I can call my gbrowse port to chado
done (after how many months?).  I have to wonder though if there is any
way to check the results of the update of the featureloc table other
than randomly clicking and looking at the picture it creates. Any ideas?

Here is the SQL I used (note that it uses fmin and fmax that I added to
speed up gbrowse)
INSERT INTO featureloc (feature_id, fmin, fmax, srcfeature_id)
 SELECT DISTINCT hit.feature_id,
                  min(hsploc1.fmin), max(hsploc2.fmax),
                  min(hsploc1.srcfeature_id)
  FROM
    feature AS hit
      INNER JOIN
    feature_relationship       ON (hit.feature_id = objfeature_id)
      INNER JOIN
    featureloc AS hsploc1      ON (hsploc1.feature_id = subjfeature_id)
      INNER JOIN
    featureloc AS hsploc2      ON (hsploc2.feature_id = subjfeature_id)
  WHERE hit.type_id = 13
    AND hsploc1.rank = 0
    AND hsploc2.rank = 0
  GROUP BY hit.feature_id

Thanks,
Scott

-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         ca...@cs...
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory