title: Finding overlapping ranges between two interval data
body: <p>I have one table with coordinates (<code>start</code>, <code>end</code>) of ca. 500000 fragments and another table with 60000 single coordinates that I would like to match with the former fragments. I.e., for each record from <code>dtCoords</code> table I need to search a record in <code>dtFrags</code> table having the same <code>chr</code> and <code>start</code>&lt;=<code>coord</code>&lt;=<code>end</code> (and retrieve the <code>type</code> from this record of <code>dtFrags</code>). Is it good idea at all to use R for this, or I should rather look to other languages?</p>
  
  <p>Here is my example:</p>
  
  <pre><code>require(data.table)
  
  dtFrags &lt;- fread(
    "id,chr,start,end,type
   1,1,100,200,exon
   2,2,300,500,intron
   3,X,400,600,intron
   4,2,250,600,exon
  ")
  
  dtCoords &lt;- fread(
  "id,chr,coord
   10,1,150
   20,2,300
   30,Y,500
  ")
  </code></pre>
  
  <p>At the end, I would like to have something like this:</p>
  
  <pre><code>"idC,chr,coord,idF,type
   10,  1,  150,  1, exon
   20,  2,  300,  2, intron
   20,  2,  300,  4, exon
   30,  Y,  500, NA, NA
  "
  </code></pre>
  
  <p>I can simplify a bit the task by splitting the table to subtables by <code>chr</code>, so I would concentrate only on coordinates</p>
  
  <pre><code>setkey(dtCoords, 'chr')
  setkey(dtFrags,  'chr')
  
  for (chr in unique(dtCoords$chr)) {
    dtCoordsSub &lt;- dtCoords[chr];
    dtFragsSub  &lt;-  dtFrags[chr];
    dtCoordsSub[, {
      # ????  
    }, by=id]  
  }
  </code></pre>
  
  <p>but it's still not clear for me how should I work inside... I would be very grateful for any hints.</p>
  
  <p>UPD. just in case, I put my real table in the archive <a href="https://drive.google.com/file/d/0Bx1bFCRksKRHTHAtb0M0dm8taG8/edit?usp=sharing" rel="nofollow">here</a>. After unpacking to your working directory, tables can be loaded with the following code:</p>
  
  <pre><code>dtCoords &lt;- fread("dtCoords.txt", sep="\t", header=TRUE)
  dtFrags  &lt;- fread("dtFrags.txt",  sep="\t", header=TRUE)
  </code></pre>