How To Intersect Two Tracks In Ucsc Table Browser And Get Fields From Both?

Question

[Nachocab]

In the UCSC Table browser, I'm trying to intersect a custom track (with genomic coordinates and a name field) and the knownGenes track.

What I'm getting are the lines in my custom track that contain a knownGene, but it doesn't tell me which gene. If I reverse the intersection, I get the genes that contain one of my custom lines, but not the name field of my custom track.

What am I doing wrong?

Answer 1

[Alex Reynolds]

One way to do this is to export your custom and knownGenes tracks to BED files, using an application like bedmap to report gene IDs from knownGenes, which overlap custom track regions by one or more bases, e.g.:

$ bedmap --echo --echo-map-id --delim '\t' custom.bed knownGenes.bed > answer.bed

The answer.bed file will contain results of the form:

[ custom-element-1 ] \t [ semi-colon-delimited list of known genes overlapping element 1 ]
[ custom-element-2 ] \t [ semi-colon-delimited list of known genes overlapping element 2 ]
...
[ custom-element-N ] \t [ semi-colon-delimited list of known genes overlapping element N ]

I think the UCSC browser exports sorted BED data, so there shouldn't be a need to pre-sort the BED inputs.

[Nachocab]

I tried using your tool, but I kept getting errors like this one:

dyld: lazy symbol binding failed: Symbol not found: __ZNSt8__detail15_List_node_base7_M_hookEPS0_
  Referenced from: /Users/nacho/Documents/Code/BioTools/bedops/bedmap
  Expected in: /usr/lib/libstdc++.6.dylib

[Alex Reynolds]

You could build BEDOPS from source (so that the build takes into account your local versions of kernel and core libraries) or just use the solution you found. If you're interested in the first option, see: http://code.google.com/p/bedops/source/checkout

[Alex Reynolds]

I have created a beta v2.1 BEDOPS installer for Mac OS X Intel 10.5-10.8, which attempts to address this specific issue. If you are interested, I would welcome feedback on it. It is available here: https://dl.dropbox.com/u/31495717/BEDOPS%20v2p1p0%20%28beta%29.mpkg.zip

Answer 2

[Aaronquinlan]

I don't think the UCSC Tables interface has this functionality. Under the assumption that you'd prefer to work in a browser as opposed to the command line, I would recommend using Galaxy - specifically the join intervals tool. Using the Get Data -> UCSC Main interface you can grab the knownGenes track in the manner you are used to. And using the Upload data interface you can upload your custom track.

Answer 3

[Nachocab]

I ended up downloading the UCSC table (gencode v14) and doing

intersectBed -a custom.bed -b gencode_exons.bed -wb

Answer 4

[max]

Use the UCSC Data Integrator to intersect two tables and keep both fields:

https://genome.ucsc.edu/cgi-bin/hgIntegrator