Re: [genome] USCS Retrieving Data Using SQL

[Jairo Navarro Gonzalez]

Dear Rajan,

Thank you for using the UCSC Genome Browser and your question about retrieving genes upstream and downstream for a set of coordinates.
Here is an example exercise for finding nearby genes using the MySQL server:

http://genomewiki.ucsc.edu/index.php/Finding_nearby_genes

I hope this is helpful. If you have any further questions, please reply to gen...@soe.ucsc.edu.
All messages sent to that address are archived on a publicly-accessible Google Groups forum.
If your question includes sensitive data, you may send it instead to genom...@soe.ucsc.edu.

Jairo Navarro 
UCSC Genomics Institute

On Thu, Mar 23, 2017 at 7:48 PM, Rajan Joshi <rhj...@asu.edu> wrote:

Hello, 

I am doing a small project for the biology lab I volunteer for at Arizona State. I have a quick question on how to retrieve genes up stream and down stream of a given set of (start,stop) coordinates. 

I connected to the USCS genome-MySQL database using the following with the JDBC driver with Java in Eclipse: 

JDBC_DRIVER = "com.mysql.jdbc.Driver";
DB_URL = "jdbc:mysql://genome-mysql.cse.ucsc.edu";

Then I continue with reading a txt file and running a query where input[0], input[1], and input[2] are all variablesI read from the txt file (chromosome, start, and end). 

String sql = "SELECT name, name2, chrom, txStart, txEnd, strand FROM hg19.refGene WHERE chrom=\"" + input[0] + "\" AND txStart<=" + input[1] + " AND txEnd>=" + input[2];

ResultSet gene = stmt.executeQuery(sql);

I am able to retrieve data and store it into an output file using a print writer. All of my code works! However, this only gets the matching overlapping genes that fall within the specified region. From my project I also want to extract genes surrounding this region. I already thought of assigning a 5kb threshold to find genes within that region; however, I wanted to ask about any other optimized suggestions from anyone at USCS! Any help is much apprecited, I have already checked out the online google forum but it did not help. 

Best,

----------------------------------------------------------

Rajan Joshi

College of Letters and Sciences

Interdisciplinary Studies: Biological Sciences & Informatics

E-mail: rhj...@asu.edu

Mobile: (480) 448-5291

--

---
You received this message because you are subscribed to the Google Groups "UCSC Genome Browser discussion list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to genome+un...@soe.ucsc.edu.