<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">Hi Mark,<div class=""><br class=""></div><div class="">we store 1000 Genomes phase 3 genotypes in VCF files (location here <a href="ftp://ftp.ensembl.org/pub/variation_genotype/homo_sapiens/" class="">ftp://ftp.ensembl.org/pub/variation_genotype/homo_sapiens/</a>). If the data is requested we compute frequencies on the fly. The best way of getting the data is by using our API. You just need to set a flag letting the API know also to look up data in VCF files. Here are some code examples: <a href="http://www.ensembl.org/info/docs/api/variation/variation_tutorial.html" class="">http://www.ensembl.org/info/docs/api/variation/variation_tutorial.html</a></div><div class=""><a href="http://www.ensembl.org/info/docs/api/variation/variation_tutorial.html#population_genotypes" class="">http://www.ensembl.org/info/docs/api/variation/variation_tutorial.html#population_genotypes</a></div><div class=""><br class=""></div><div class="">If you need to retrieve frequencies for all 1000 Genomes phase 3 variants we would advice to use vcftools and work directly with the provided VCF files. </div><div class=""><br class=""></div><div class="">We do provide data dumps for allele frequencies of 1000 Genomes phase 3 variants from the continental super populations (AFR, AMR, EAS, EUR, SAS)</div><div class=""><a href="ftp://ftp.ensembl.org/pub/release-84/variation/vcf/homo_sapiens/1000GENOMES-phase_3.vcf.gz" class="">ftp://ftp.ensembl.org/pub/release-84/variation/vcf/homo_sapiens/1000GENOMES-phase_3.vcf.gz</a></div><div class=""><br class=""></div><div class="">HTH,</div><div class="">Anja</div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""><div><blockquote type="cite" class=""><div class="">On 8 Jul 2016, at 22:18, Mark Miller <<a href="mailto:Mark.Miller@instem.com" class="">Mark.Miller@instem.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="WordSection1" style="page: WordSection1; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><p style="margin-right: 0in; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">Also posted to Biostars<span class="Apple-converted-space"> </span><a href="https://www.biostars.org/p/200847/" style="color: rgb(149, 79, 114); text-decoration: underline;" class="">https://www.biostars.org/p/200847/</a><o:p class=""></o:p></p><p style="margin-right: 0in; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><o:p class=""> </o:p></p><p style="margin-right: 0in; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">How would I write an SQL query to retrieve all of the frequencies, form all providers, such as seen on a page like<span class="Apple-converted-space"> </span><a href="http://useast.ensembl.org/Homo_sapiens/Variation/Population?db=core;r=16:89919209-89920209;v=rs1805007;vdb=variation;vf=1232953" style="color: rgb(149, 79, 114); text-decoration: underline;" class="">http://useast.ensembl.org/Homo_sapiens/Variation/Population?db=core;r=16:89919209-89920209;v=rs1805007;vdb=variation;vf=1232953</a><span class="Apple-converted-space"> </span>?<o:p class=""></o:p></p><p style="margin-right: 0in; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">Connection info:<span class="Apple-converted-space"> </span><a href="http://useast.ensembl.org/info/data/mysql.html" style="color: rgb(149, 79, 114); text-decoration: underline;" class="">http://useast.ensembl.org/info/data/mysql.html</a><o:p class=""></o:p></p><p style="margin-right: 0in; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">I'm<span class="Apple-converted-space"> </span><em class=""><b class="">especially</b></em><span class="Apple-converted-space"> </span>interested in the 1000 genomes frequencies.<o:p class=""></o:p></p><p style="margin-right: 0in; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">I tried this (among many other queries), but it doesn't seem to include the 1000 Genomes Phase 3 data (for example, the frequency for '1000GENOMES:phase_3:TSI', population ID 373537, should be 0.023 )<o:p class=""></o:p></p><pre style="margin: 0in 0in 0.0001pt; font-size: 10pt; font-family: 'Courier New';" class=""><code style="font-family: 'Courier New';" class="">SELECT * FROM allele<o:p class=""></o:p></code></pre><pre style="margin: 0in 0in 0.0001pt; font-size: 10pt; font-family: 'Courier New';" class=""><code style="font-family: 'Courier New';" class="">left </code><span class="token">join</span><code style="font-family: 'Courier New';" class=""> population<o:p class=""></o:p></code></pre><pre style="margin: 0in 0in 0.0001pt; font-size: 10pt; font-family: 'Courier New';" class=""><code style="font-family: 'Courier New';" class="">on allele.population_id </code><span class="token">=</span><code style="font-family: 'Courier New';" class=""> population.population_id<o:p class=""></o:p></code></pre><pre style="margin: 0in 0in 0.0001pt; font-size: 10pt; font-family: 'Courier New';" class=""><code style="font-family: 'Courier New';" class="">left </code><span class="token">join</span><code style="font-family: 'Courier New';" class=""> variation<o:p class=""></o:p></code></pre><pre style="margin: 0in 0in 0.0001pt; font-size: 10pt; font-family: 'Courier New';" class=""><code style="font-family: 'Courier New';" class="">on allele.variation_id </code><span class="token">=</span><code style="font-family: 'Courier New';" class=""> variation.variation_id<o:p class=""></o:p></code></pre><pre style="margin: 0in 0in 0.0001pt; font-size: 10pt; font-family: 'Courier New';" class=""><code style="font-family: 'Courier New';" class="">where variation.name </code><span class="token">=</span><code style="font-family: 'Courier New';" class=""> </code><span class="token">'rs1805007'</span><code style="font-family: 'Courier New';" class=""><o:p class=""></o:p></code></pre><ul type="disc" style="margin-bottom: 0in;" class=""><li class="MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">Do I need to learn about subsnps?<span class="Apple-converted-space"> </span><o:p class=""></o:p></li><li class="MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">Is the data masked for privacy? There sure are a lot of NULLs.<o:p class=""></o:p></li><li class="MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">Do I just need to keep staring at the Ensembl ERD? Like maybe I need to look in some of the sample or individual tables?<span class="Apple-converted-space"> </span><a href="http://useast.ensembl.org/info/docs/api/variation/variation_schema.html" style="color: rgb(149, 79, 114); text-decoration: underline;" class="">http://useast.ensembl.org/info/docs/api/variation/variation_schema.html</a><o:p class=""></o:p></li></ul><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="color: rgb(31, 73, 125);" class=""><o:p class=""> </o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="color: rgb(31, 73, 125);" class="">Mark Miller<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="color: rgb(31, 73, 125);" class="">Instem<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="color: rgb(31, 73, 125);" class="">Head of Bioinformatics & SRS Product Manager<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="color: rgb(31, 73, 125);" class="">W +1 610 941 0990 x131<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="color: rgb(31, 73, 125);" class=""><o:p class=""> </o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><b class=""><i class=""><span style="color: rgb(31, 73, 125);" class="">NEW MOBILE NUMBER: +1 215 421 5294</span></i></b><b class=""><i class=""><o:p class=""></o:p></i></b></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-align: justify;" class=""><span style="font-size: 8pt; font-family: Arial;" class="">The contents of this e-mail message, including any attachments, are intended solely for the use of the person or entity to which the e-mail was addressed. If you are not the intended recipient of this message, be advised that any dissemination, distribution, or use of the contents of this message is strictly prohibited. If you received this e-mail message in error, please e-mail<span class="Apple-converted-space"> </span><a href="x-msg://125/is@instem.com" ?="" style="color: rgb(149, 79, 114); text-decoration: underline;" class="">is@instem.com</a><span class="Apple-converted-space"> </span>and contact the sender by reply e-mail. Please also permanently delete all copies of the original e-mail and any attached documentation. Thank you. Copyright 2016 Instem Group of Companies. For any other correspondence please write to Instem plc. a company registered in England and Wales, number 07148099, with registered office at Diamond Way, Stone Business Park, Stone, Staffordshire, ST15 0SD England.</span></div><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">_______________________________________________</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Dev mailing list </span><a href="mailto:Dev@ensembl.org" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">Dev@ensembl.org</a><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Posting guidelines and subscribe/unsubscribe info:<span class="Apple-converted-space"> </span></span><a href="http://lists.ensembl.org/mailman/listinfo/dev" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">http://lists.ensembl.org/mailman/listinfo/dev</a><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Ensembl Blog:<span class="Apple-converted-space"> </span></span><a href="http://www.ensembl.info/" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">http://www.ensembl.info/</a></div></blockquote></div><br class=""></div></body></html>