Hi Diego,
To answer your questions:
1. The standard way to detect the overlap between a query interval and a
positional element:|
queryEnd > chromStart and chromEnd > queryStart
2. It looks like you are trying to query every single table in the
database for anything in a region. There are many tables with many
different structures, so this query would take a very long time. We
suggest you make a specific query to a small set of tables to retrieve
the information you are looking for.
|
I hope this helps. Please contact us again at [email protected] if you
have any further questions.
---
Luvina Guruvadoo
UCSC Genome Bioinformatics Group
On 9/6/2011 5:38 AM, Diego F Pereira wrote:
> Hello again,
>
> I have two basic questions:
>
> 1. How should I write the SQL sentence in order to get all the elements that
> match a particular interval?
>
> For example:
> Let Es = start position of an element, Ee = end position of an element, Qs =
> start position of my query interval, Qe = end position of my query interval.
>
> If I use chromStart and chromEnd, in order to find all the elements that
> match a particular interval I would have to use 4 different parameters in
> the WHERE clause
> a. Es1 > Qs AND Ee1 > Qe
> b. Es2 > Qs AND Ee2 < Qe
> c. Es3 < Qs AND Ee3 < Qe
> d. Es4 < Qs AND Ee4 > Qe
>
> However, I think there should be an easier way to obtain the same result...
>
> 2. I'm trying to run the next nested query:
>
> SELECT * FROM (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
> table_schema = 'hg19') WHERE chrom=1 AND chromStart >= 1 AND chromEnd <= 10;
>
> ... but I get and error-> Error Code: 1248. Every derived table must have
> its own alias.
>
> What I want with this query is to get all the elements and their related
> information for a particular interval.
>
> In VBA I would use a recordset, a loop and a query to solve this, but inside
> MySQL I don't know how to solve it.
>
> Any comments are much appreciated.
>
> Regards,
>
> Diego
>
> -----Original Message-----
> From: Hiram Clawson [mailto:[email protected]]
> Sent: Monday, August 22, 2011 1:48 PM
> To: Diego F Pereira
> Cc: 'Jeff Murray'; [email protected]
> Subject: Re: [Genome] Retrieving data from UCSC
>
> Good Morning Diego:
>
> You do not need special software interfaces to fetch data from the UCSC
> MySQL database. As mentioned in the FAQ:
>
> http://genome-test.cse.ucsc.edu/FAQ/FAQdownloads.html#download29
>
> the simple command line operation mysql:
>
> mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -e "select count(*)
> from snp131;" hg19
>
> can talk to the database and fetch whatever you want from any table.
>
> To view the description of any table's contents:
>
> mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -e "desc snp131;"
> hg19
>
> You can feed the output of these commands into any processing software, or
> embed the commands into your system. Yes, you will need to decide what it
> is you want to fetch from any particular table and how you would like to
> relate it to another table. The UCSC Table Browser can also perform these
> functions, and it can display the relationships of the tables with each
> other. Your experiment design will clarify what relationships you desire to
> highlight and extract.
> When you work within the table browser, you can also view your results in
> the genome browser.
>
> You can also use the Galaxy interface: http://galaxy.psu.edu/ to organize
> the workflow of your experiments. The Galaxy system cooperates with the
> UCSC genome browser and many other bioinformatic tools to provide an
> integrated experiment work-flow design procedure.
>
> --Hiram
>
> Diego F Pereira wrote:
>
>> Hello Vanessa,
>> Is there a way in which I can connect a MSAccess database to your
>> MySQL database?
>> Or
>> Should I use PHP and MySQL on my computer to pull the data I need from
>> your MySQL database?
>> I have some experience with MSAccess, but by checking PHP that seems
>> several times easier to work with than VBA.
>> I had avoided to learn MySQL for long time, but if you think this is
>> the best way I'll definitely learn it.
>> Of course if you know a better/easier approach please let me know.
>> Many thanks in advance,
>> Diego
>>
>> PS:
>> I believe this is going to be very interesting for all the people who
>> is doing in-depth analyzes of the data displayed by the UCSC browser.
>> Therefore, even if I'm not a computer scientist I think it worth the
>>
> effort.
>
>> If this works I'll be happy of sharing it with you and all the people.
>>
>
> _______________________________________________
> Genome maillist - [email protected]
> https://lists.soe.ucsc.edu/mailman/listinfo/genome
>
_______________________________________________
Genome maillist - [email protected]
https://lists.soe.ucsc.edu/mailman/listinfo/genome
