Hello Pete, Because the GenBank tables needed to link in organism are very large, using the Table browser for this query is likely not going to work. I just attempted a query, limited to only chr22, for the track "Other mRNA" and it failed (timed out).
A better choice is to use the files on hgdownload and shell commands or a script to join/filter the data or the public mySQL server. For the public mySQL server, I can give you a bit of help to get started. These tables are large, so please do not overuse this query or add it to an automated script that runs frequently. A good mySQL help site is: http://dev.mysql.com/doc/refman/5.0/en/ 1) set up your system to access the server. Be sure to create the file .hg.conf with the three lines and permissions 600, as specified in this FAQ: http://genome.ucsc.edu/FAQ/FAQdownloads.html#download29 2) use the following query as a baseline to understand the links between the tables to obtain the data (same links are true for a file based method as well). $ mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A mysql> select xenoMrna.qName, xenoMrna.tName, organism.name from xenoMrna, gbCdnaInfo, organism where xenoMrna.qName = gbCdnaInfo.acc and gbCdnaInfo.organism = organism.id and organism.name like "Gallus%" limit 5; +----------+-------+---------------+ | qName | tName | name | +----------+-------+---------------+ | AJ720178 | chr1 | Gallus gallus | | BX935275 | chr1 | Gallus gallus | | BX933926 | chr1 | Gallus gallus | | AJ720450 | chr1 | Gallus gallus | | AY332742 | chr1 | Gallus gallus | +----------+-------+---------------+ 5 rows in set (2.96 sec) To obtain all of the data for the xenoMrna table, change the query to be like this (below) for the "Other mRna" track. Swap in xenoEst for the "Other EST" track. The query is formatted to make it easier to understand and change. When you enter it at the unix prompt, put everything into one continuous line. select xenoMrna.<field1>, xenoMrna.<field2>, <keep adding more until all columns desired are listed> from xenoMrna, gbCdnaInfo, organism where xenoMrna.qName = gbCdnaInfo.acc and gbCdnaInfo.organism = organism.id and organism.name like "Gallus%"; 3) run the query at the unix prompt (terminal, xterm) - not within mySQL - to have the output send to a file. % hgsql -e "your mySQL query" hg19 > out_file If you just use the fields from the primary table as labeled in the Table browser "view schema" link, the output will be in psl format. You can use the utility pslToBed from the kent source or load as a custom track and the Table browser can convert the format. http://genome.ucsc.edu/goldenPath/help/hgTracksHelp.html#CustomTracks Hopefully this will get you the data. Good luck with your project, Jennifer --------------------------------- Jennifer Jackson UCSC Genome Informatics Group http://genome.ucsc.edu/ On 4/7/10 12:37 PM, Pete Lovell wrote: > Hi, > > I am trying to export a BED file containing the alignmens for all of the > chicken ESTs/mRNAs that map to the human genome (assembly hg19). It looks as > if this should be possible through the hg19 table browser when appropriate > filters are applied, but I can¹t seem to find any valid literature > discussing how this might be done. I did search your mailing list archives > and discovered a thread from 2004 (see cut/paste below) that used the > free-form query box and: > > type = "EST" and (organism = 318 or organism = 7952 or organism = 6744) > > but Œtype¹ doesn¹t appear to be a valid MySQL field for the latest release > of the human genome assembly. I also attempted to retrieve alignments by > uploading a list chicken ESTs (GenbankIDs) that I downloaded from the > chicken genome assembly, but since a good number of these don¹t map to the > human genome, I get an error message indicating that one or more identifier > has no match in table xenoEST, field qName. The browser doesn¹t seem to > want me to retrieve thost that do match. If I could somehow establish a set > of chicken ESTs that do map to the human genome I¹d be home free, but alas I > just can¹t figure out a way to derive this information. Not unexpectedly, my > attempts to open ³all² XenoEsts in Excel (for the purposes of > cross-referencing with my chicken EST list) don¹t work when the file is > 1.2GB+! > > Can you help?? > Regards, > Pete > > Dr. Peter V. Lovell > 503-418-2651 > Dept. of Behavioral Neuroscience > Oregon Health& Science Univ > 3181 SW Sam Jackson Park Road L470 > Portland, OR 97239-3098 > > > > https://lists.soe.ucsc.edu/pipermail/genome/2004-June/004823.html > [Genome] table browser, filter hg16 data v. chicken ESTs > > > _______________________________________________ > Genome maillist - [email protected] > https://lists.soe.ucsc.edu/mailman/listinfo/genome _______________________________________________ Genome maillist - [email protected] https://lists.soe.ucsc.edu/mailman/listinfo/genome
