Selecting by location

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Selecting by location

Daniel Ence
Hi, I'm wondering about the best way to do a chado query to get features based on which scaffold they're on. The chado wiki talks about the feature_loc table, but that all seems like relative distances and locations. 

Thanks,
Daniel 

Daniel Ence
Graduate Student
Eccles Institute of Human Genetics
University of Utah
15 North 2030 East, Room 2100
Salt Lake City, UT 84112-5330

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_jan
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Selecting by location

Andy Schroeder
Hi Daniel,

Check out the section on featureloc in Chado Best Practices (and the link to the srcfeature_id column of featureloc).  http://www.gmod.org/wiki/Chado_Best_Practices

Basically all the features on the same scaffold should share the same srcfeature_id in the featureloc table.

cheers,
Andy


On Thu, Jan 31, 2013 at 11:36 AM, Daniel Ence <[hidden email]> wrote:
Hi, I'm wondering about the best way to do a chado query to get features based on which scaffold they're on. The chado wiki talks about the feature_loc table, but that all seems like relative distances and locations. 

Thanks,
Daniel 

Daniel Ence
Graduate Student
Eccles Institute of Human Genetics
University of Utah
15 North 2030 East, Room 2100
Salt Lake City, UT 84112-5330

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_jan
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema



------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_jan
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Selecting by location

Carson Holt-2
In reply to this post by Daniel Ence
Simple queries:

Get contig ID
SELECT f.feature_id FROM feature f WHERE f.uniquename_id=$contig_name;

Get features for that contig
SELECT * FROM feature f INNER JOIN featureloc l ON (l.srcfeature_id=$contig_id AND f.feature_id=l.feature_id) ;



And a more complex query to get GFF3 like column output for the given contig_id
SELECT f.feature_id, r.accession, c.name, l.fmin, l.fmax, 
a.significance, l.strand, l.phase, f.uniquename, f.name, f2.uniquename, 
f3.uniquename, f4.uniquename, l2.fmin, l2.fmax, l2.strand 
FROM feature f 
INNER JOIN featureloc l ON (l.srcfeature_id=$contig_id AND f.feature_id=l.feature_id) 
LEFT OUTER  JOIN feature_dbxref d ON d.feature_id=l.feature_id 
INNER JOIN dbxref r ON r.dbxref_id=d.dbxref_id 
INNER JOIN db b ON (b.name='GFF_source' AND r.db_id=b.db_id) 
LEFT OUTER JOIN cvterm c ON f.type_id=c.cvterm_id 
LEFT OUTER JOIN analysisfeature a ON a.feature_id=f.feature_id 
LEFT OUTER JOIN feature_relationship pt ON pt.subject_id=f.feature_id 
LEFT OUTER JOIN cvterm c2 ON pt.type_id=c2.cvterm_id 
LEFT OUTER JOIN dbxref d2 ON d2.dbxref_id=c2.dbxref_id 
LEFT OUTER JOIN feature f2 ON (c2.name='part_of' AND f2.feature_id=pt.object_id) 
LEFT OUTER JOIN feature f3 ON (c2.name='derives_from' AND f3.feature_id=pt.object_id) 
LEFT OUTER JOIN featureloc l2 ON (f.feature_id=l2.feature_id AND l2.srcfeature_id != l.srcfeature_id) 
LEFT OUTER JOIN feature f4 ON l2.srcfeature_id=f4.feature_id 
ORDER BY f.feature_id;


Replace $contig_id and $contig_name with appropriate values in all queries.

Thanks,
Carson



From: Daniel Ence <[hidden email]>
Date: Thursday, 31 January, 2013 11:36 AM
To: Carson Holt <[hidden email]>, "[hidden email]" <[hidden email]>
Subject: Selecting by location

Hi, I'm wondering about the best way to do a chado query to get features based on which scaffold they're on. The chado wiki talks about the feature_loc table, but that all seems like relative distances and locations. 

Thanks,
Daniel 

Daniel Ence
Graduate Student
Eccles Institute of Human Genetics
University of Utah
15 North 2030 East, Room 2100
Salt Lake City, UT 84112-5330

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_jan
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema