<div dir="ltr"><div dir="ltr">Hi Devs,<div><br></div><div>I'm trying to programmatically fetch allele and genotype frequency data provided by 1000 Genomes for a (large) list of SNPs, which is available on the website (e.g. <a href="http://grch37.ensembl.org/Homo_sapiens/Variation/Population?db=core;r=3:41610728-41611728;v=rs7627367;vdb=variation;vf=323376947" target="_blank">http://grch37.ensembl.org/Homo_sapiens/Variation/Population?db=core;r=3:41610728-41611728;v=rs7627367;vdb=variation;vf=323376947</a>)</div><div><br></div><div>After digging into the documentations I have found that there are three ways to retrieve large amount of data from the ensembl server:</div><div>1. Rest API, which does contain all the information I need, however it also includes a lot of other data in the same endpoint and is thus relatively slow;</div><div>2. Perl API, but I'm not familiar with Perl, and it seems what it does is basically accessing the MySQL database;</div><div>3. Public MySQL server</div><div><br></div><div>So I decided to connect to the public MySQL servers.</div><div>However, while I can find the table (`population_genotype`) that should contain the information, as well as the 1000 Genomes references (`population`), I cannot find the data that I need.</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">SELECT<br>    `p`.`population_id`,<br>    `p`.`name`,<br>    `p`.`size`,<br>    `p`.`description`<br>FROM `population` AS `p`<br>WHERE<br>    `p`.`name` LIKE '1000GENOMES:phase_3:%'<br>ORDER BY `p`.`population_id`;</blockquote><div>gives me the 32 populations I need, however</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">SELECT<br>    `pg`.*,<br>    `p`.`name` AS `population_name`<br>FROM `population_genotype` AS `pg`<br>JOIN `population` AS `p`<br>    ON `p`.`population_id` = `pg`.`population_id`<br>WHERE<br>    `p`.`name` LIKE '1000GENOMES:phase_3:%';</blockquote><div>would return empty set.</div><div><br></div><div>Similar situation exists for the `sample` and `sample_genotype_multiple_bp` tables:</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">SELECT<br>    `s`.`sample_id`,<br>    `s`.`name` AS `sample_name`,<br>    `ind`.`individual_id`,<br>    `ind`.`name` AS `individual_name`,<br>    `ind`.`gender`<br>FROM `sample` AS `s`<br>JOIN `individual` AS `ind`<br>    ON `ind`.`individual_id` = `s`.`individual_id`<br>WHERE<br>    `s`.`name` LIKE '1000GENOMES:phase_3:%'</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">ORDER BY `s`.`sample_id`; </blockquote><div> returns 2504 records, which is the correct number, but</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">SELECT<br>    `sg`.*,<br>    `s`.`name`<br>FROM `sample_genotype_multiple_bp` AS `sg`<br>JOIN `sample` AS `s`<br>    ON `s`.`sample_id` = `sg`.`sample_id` </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">WHERE</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">    `s`.`name` LIKE '1000GENOMES:phase_3:%'; </blockquote><div>returns empty set.</div><div><br></div><div>I tried connecting to both <a href="http://ensembldb.ensembl.org:3337/homo_sapiens_variation_97_37" target="_blank">ensembldb.ensembl.org:3337/homo_sapiens_variation_97_37</a> and <a href="http://ensembldb.ensembl.org:3306/homo_sapiens_variation_97_38" target="_blank">ensembldb.ensembl.org:3306/homo_sapiens_variation_97_38</a>, and the situation is the same for both.</div><div><br></div><div>I'm genuinely at a loss here, because I can clearly see the data on the website. Is it retrieved from somewhere else other than the database I'm looking at?</div><div><br></div><div>I'm relatively new to Ensembl so I might be looking at the wrong place. Thanks in advance.</div><div><br></div><div>Yours,</div><div>Zheng Jin</div></div><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
</blockquote></div></div>