Mapping genomic patches to chromosomes

Entering edit mode

10.7 years ago

Khader Shameer 18k

As part of a large-scale genomic integration project, I ended up with chromosome positions mapped to human genome patches like:

HG104_HG975_PATCH
HG1257_PATCH
HG19_PATCH
HSCHR10_1_CTG2
HSCHR17_1

I need to map these patch IDs back to chromosome numbers to unify them with rest of the database. While the last two cases indicates chromosome numbers as 10 and 17 respectively, am looking for a strategy to map the first three.

Full list of patch IDs that we have is uploaded here.

Thoughts?

genome mapping genome-patch • 4.9k views

ADD COMMENT • link updated 3.3 years ago by Ram 45k • written 10.7 years ago by Khader Shameer 18k

Entering edit mode

10.7 years ago

Bert Overduin ★ 3.7k

Here you go.

Note that Ensembl version 75 is based on the GRCh37.14 assembly. Version 76, expected later this month, will be based on GRCh38.

mysql -h ensembldb.ensembl.org -u anonymous
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 659408
Server version: 5.1.72-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use homo_sapiens_core_75_37;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT DISTINCT s1.name AS patch, s2.name AS chromosome, a.exc_seq_region_start AS start, a.exc_seq_region_end AS end FROM assembly_exception AS a, seq_region AS s1, seq_region AS s2 WHERE a.seq_region_id = s1.seq_region_id AND a.exc_seq_region_id = s2.seq_region_id AND a.exc_type IN ('PATCH_FIX', 'PATCH_NOVEL');

	+--------------------------+------------+-----------+-----------+
	\| patch \| chromosome \| start \| end \|
	+--------------------------+------------+-----------+-----------+
	\| HG989_PATCH \| 1 \| 31872759 \| 32017063 \|
	\| HG79_PATCH \| 9 \| 136049442 \| 136369192 \|
	\| HG104_HG975_PATCH \| 8 \| 144743526 \| 145146062 \|
	\| HG183_PATCH \| 17 \| 62273514 \| 62649312 \|
	\| HG186_PATCH \| 3 \| 51416109 \| 51584055 \|
	\| HG243_PATCH \| 8 \| 145285645 \| 145659901 \|
	\| HG280_PATCH \| 3 \| 57369478 \| 57399969 \|
	\| HG544_PATCH \| 10 \| 17613209 \| 18252930 \|
	\| HG75_PATCH \| 17 \| 34442621 \| 35005379 \|
	\| HG990_PATCH \| 17 \| 252429 \| 296626 \|
	\| HG991_PATCH \| 3 \| 66270271 \| 66308065 \|
	\| HG995_PATCH \| 10 \| 133258319 \| 133381404 \|
	\| HG996_PATCH \| 12 \| 132806993 \| 132967794 \|
	\| HG998_1_PATCH \| 9 \| 139136890 \| 139166997 \|
	\| HG998_2_PATCH \| 9 \| 139216998 \| 139252828 \|
	\| HG999_1_PATCH \| 1 \| 248865779 \| 248908210 \|
	\| HG999_2_PATCH \| 1 \| 249058211 \| 249098883 \|
	\| HSCHR10_1_CTG2 \| 10 \| 27574584 \| 27706537 \|
	\| HSCHR10_1_CTG5 \| 10 \| 45670681 \| 45964419 \|
	\| HSCHR11_1_CTG1_1 \| 11 \| 25191953 \| 25340626 \|
	\| HSCHR12_1_CTG2 \| 12 \| 28148967 \| 28263711 \|
	\| HSCHR12_1_CTG2_1 \| 12 \| 58326520 \| 58486538 \|
	\| HSCHR12_1_CTG5 \| 12 \| 126711744 \| 126890020 \|
	\| HSCHR12_2_CTG2_1 \| 12 \| 59323046 \| 59454651 \|
	\| HSCHR7_1_CTG6 \| 7 \| 141333209 \| 141446383 \|
	\| HSCHR15_1_CTG4 \| 15 \| 28557187 \| 28842093 \|
	\| HSCHR15_1_CTG8 \| 15 \| 66200521 \| 66577156 \|
	\| HSCHR16_1_CTG3_1 \| 16 \| 55822434 \| 56002460 \|
	\| HSCHR16_2_CTG3_1 \| 16 \| 69174054 \| 69258593 \|
	\| HSCHR17_1_CTG4 \| 17 \| 39463362 \| 39589187 \|
	\| HSCHR17_2_CTG4 \| 17 \| 68302419 \| 68520360 \|
	\| HSCHR17_3_CTG4 \| 17 \| 75216812 \| 75295408 \|
	\| HSCHR18_1_CTG1_1 \| 18 \| 47818564 \| 48101162 \|
	\| HSCHR18_1_CTG2 \| 18 \| 70600357 \| 70692016 \|
	\| HSCHR18_1_CTG2_1 \| 18 \| 76253467 \| 76412030 \|
	\| HSCHR18_2_CTG1_1 \| 18 \| 49189306 \| 49348012 \|
	\| HSCHR18_2_CTG2 \| 18 \| 65090960 \| 65219788 \|
	\| HSCHR18_2_CTG2_1 \| 18 \| 76694886 \| 76848997 \|
	\| HSCHR19_1_CTG3 \| 19 \| 20845947 \| 21225187 \|
	\| HSCHR19_1_CTG3_1 \| 19 \| 34643165 \| 34791855 \|
	\| HG1091_PATCH \| 3 \| 60558332 \| 60952039 \|
	\| HSCHR19_3_CTG3 \| 19 \| 22303652 \| 22483468 \|
	\| HSCHR1_1_CTG31 \| 1 \| 153673007 \| 153838214 \|
	\| HSCHR1_2_CTG31 \| 1 \| 155180173 \| 155275036 \|
	\| HSCHR1_3_CTG31 \| 1 \| 198339213 \| 198694304 \|
	\| HSCHR20_1_CTG1 \| 20 \| 17751747 \| 17874115 \|
	\| HSCHR21_1_CTG1_1 \| 21 \| 15796462 \| 15847792 \|
	\| HSCHR21_2_CTG1_1 \| 21 \| 23474793 \| 23669288 \|
	\| HSCHR21_3_CTG1_1 \| 21 \| 34141390 \| 34210247 \|
	\| HSCHR21_4_CTG1_1 \| 21 \| 34777735 \| 34884866 \|
	\| HSCHR22_1_CTG2 \| 22 \| 39280299 \| 39407165 \|
	\| HSCHR2_1_CTG1 \| 2 \| 36453102 \| 36590458 \|
	\| HSCHR2_1_CTG12 \| 2 \| 169686873 \| 169793704 \|
	\| HSCHR3_1_CTG2_1 \| 3 \| 151307154 \| 151477286 \|
	\| HSCHR4_1_CTG12 \| 4 \| 156756530 \| 156908416 \|
	\| HSCHR4_1_CTG6 \| 4 \| 34519577 \| 34885480 \|
	\| HSCHR4_2_CTG9 \| 4 \| 62777687 \| 62877254 \|
	\| HSCHR5_1_CTG2 \| 5 \| 94505561 \| 94590195 \|
	\| HSCHR5_1_CTG5 \| 5 \| 161800673 \| 161968654 \|
	\| HSCHR5_2_CTG1 \| 5 \| 12681538 \| 12744122 \|
	\| HSCHR6_1_CTG5 \| 6 \| 80059725 \| 80156628 \|
	\| HG311_PATCH \| 10 \| 118348517 \| 118544777 \|
	\| HSCHR9_1_CTG1 \| 9 \| 7428994 \| 7577169 \|
	\| HSCHR9_1_CTG35 \| 9 \| 72028659 \| 72092013 \|
	\| HSCHR9_2_CTG35 \| 9 \| 72639029 \| 72804234 \|
	\| HSCHR9_3_CTG35 \| 9 \| 90793962 \| 90842895 \|
	\| HG706_PATCH \| 4 \| 75382210 \| 75689879 \|
	\| HG736_PATCH \| 7 \| 56835596 \| 57190579 \|
	\| HG14_PATCH \| 7 \| 57342227 \| 57586048 \|
	\| HG946_PATCH \| 7 \| 98260131 \| 98556215 \|
	\| HG115_PATCH \| 7 \| 101718951 \| 102072447 \|
	\| HG7_PATCH \| 7 \| 141557850 \| 142778624 \|
	\| HG281_PATCH \| 11 \| 49862648 \| 50121284 \|
	\| HG858_PATCH \| 12 \| 60001 \| 282464 \|
	\| HG531_PATCH \| 13 \| 115085142 \| 115109878 \|
	\| HG185_PATCH \| 17 \| 39869611 \| 40277911 \|
	\| HG686_PATCH \| 2 \| 95326172 \| 95618108 \|
	\| HG1007_PATCH \| 2 \| 243059660 \| 243189373 \|
	\| HG1032_PATCH \| 4 \| 190828226 \| 191044276 \|
	\| HSCHR5_3_CTG1 \| 5 \| 29036605 \| 29254576 \|
	\| HSCHR5_1_CTG1 \| 5 \| 68512646 \| 70910270 \|
	\| HG19_PATCH \| 8 \| 1965390 \| 2498608 \|
	\| HG905_PATCH \| 10 \| 60001 \| 224405 \|
	\| HG325_PATCH \| 3 \| 50267585 \| 50368277 \|
	\| HG536_PATCH \| 11 \| 69614786 \| 69922571 \|
	\| HSCHR12_1_CTG1 \| 12 \| 60001 \| 282464 \|
	\| HG1133_PATCH \| 12 \| 10953894 \| 11330216 \|
	\| HSCHR12_2_CTG2 \| 12 \| 10953894 \| 11330216 \|
	\| HSCHR19LRC_COX1_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_COX2_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_LRC_I_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_LRC_J_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_LRC_S_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_LRC_T_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_PGF1_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR19LRC_PGF2_CTG1 \| 19 \| 54528888 \| 55595686 \|
	\| HG1063_PATCH \| 5 \| 102420838 \| 102687150 \|
	\| HG142_HG150_NOVEL_TEST \| 11 \| 55956728 \| 56150119 \|
	\| HG151_NOVEL_TEST \| 11 \| 56460709 \| 56641043 \|
	\| HG414_PATCH \| 11 \| 76709408 \| 76900812 \|
	\| HG339_PATCH \| 10 \| 97861451 \| 98039369 \|
	\| HG305_PATCH \| 11 \| 102702497 \| 102822936 \|
	\| HG306_PATCH \| 11 \| 112687997 \| 113234431 \|
	\| HG299_PATCH \| 11 \| 118849111 \| 118957986 \|
	\| HG883_PATCH \| 17 \| 26645159 \| 26840019 \|
	\| HSCHR17_4_CTG4 \| 17 \| 39006985 \| 39284660 \|
	\| HSCHR17_6_CTG4 \| 17 \| 39363922 \| 39463361 \|
	\| HSCHR17_5_CTG4 \| 17 \| 39382695 \| 39463361 \|
	\| HG747_PATCH \| 17 \| 65936106 \| 66324470 \|
	\| HG729_PATCH \| 19 \| 8634915 \| 8926179 \|
	\| HSCHR19_2_CTG3 \| 19 \| 21831092 \| 21991834 \|
	\| HG256_PATCH \| 11 \| 13963464 \| 14425744 \|
	\| HG873_PATCH \| 11 \| 14873971 \| 15041407 \|
	\| HG348_PATCH \| 11 \| 94611846 \| 94852618 \|
	\| HG388_HG400_PATCH \| 11 \| 111618733 \| 111945380 \|
	\| HSCHR12_3_CTG2_1 \| 12 \| 101505377 \| 101652073 \|
	\| HG748_PATCH \| 17 \| 77412077 \| 77766016 \|
	\| HG1293_PATCH \| 1 \| 206072708 \| 206940869 \|
	\| HSCHR3_1_CTG1 \| 3 \| 11856742 \| 12027971 \|
	\| HG1322_PATCH \| 6 \| 29725736 \| 29934084 \|
	\| HG1304_PATCH \| 6 \| 112323682 \| 112586593 \|
	\| HG1308_PATCH \| 7 \| 129984424 \| 130721755 \|
	\| HG962_PATCH \| 9 \| 70835469 \| 71130847 \|
	\| HG871_PATCH \| 10 \| 5012527 \| 5450243 \|
	\| HG1211_PATCH \| 10 \| 46224334 \| 51664310 \|
	\| HG271_PATCH \| 17 \| 79319163 \| 79498447 \|
	\| HG1471_PATCH \| 1 \| 205916833 \| 205922707 \|
	\| HG1473_PATCH \| 1 \| 235167042 \| 235192211 \|
	\| HSCHR2_2_CTG12 \| 2 \| 149790583 \| 149880633 \|
	\| HG27_PATCH \| 6 \| 26585843 \| 26826779 \|
	\| HG1257_PATCH \| 7 \| 71935722 \| 75921852 \|
	\| HG1502_PATCH \| 9 \| 132892850 \| 133073060 \|
	\| HG1500_PATCH \| 9 \| 137025633 \| 137041193 \|
	\| HG1501_PATCH \| 9 \| 137091194 \| 137247659 \|
	\| HG1479_PATCH \| 10 \| 133727528 \| 133781577 \|
	\| HG865_PATCH \| 11 \| 70301893 \| 71088948 \|
	\| HG971_PATCH \| 15 \| 99529037 \| 99696045 \|
	\| HG1208_PATCH \| 16 \| 57774022 \| 57871358 \|
	\| HG1146_PATCH \| 17 \| 43663045 \| 44629504 \|
	\| HG144_PATCH \| 20 \| 34883261 \| 34994224 \|
	\| HG506_HG507_HG1000_PATCH \| 20 \| 61031539 \| 61267733 \|
	\| HG1487_PATCH \| 22 \| 16050001 \| 16087693 \|
	\| HG1486_PATCH \| 22 \| 16656462 \| 16697850 \|
	\| HG1488_PATCH \| 22 \| 16847851 \| 16884805 \|
	\| HG329_PATCH \| 22 \| 19134297 \| 19346604 \|
	\| HG497_PATCH \| 22 \| 51203354 \| 51244566 \|
	\| HG1423_PATCH \| X \| 36202464 \| 37098256 \|
	\| HG1424_PATCH \| X \| 37148257 \| 37669766 \|
	\| HG1435_PATCH \| X \| 45534581 \| 45805400 \|
	\| HG1433_PATCH \| X \| 50029886 \| 54424077 \|
	\| HG1437_PATCH \| X \| 62203738 \| 63086230 \|
	\| HG1438_PATCH \| X \| 71530834 \| 72366730 \|
	\| HG1425_PATCH \| X \| 75200416 \| 76653692 \|
	\| HG1439_PATCH \| X \| 100573313 \| 100962928 \|
	\| HG1440_PATCH \| X \| 101383115 \| 101773566 \|
	\| HG1441_PATCH \| X \| 103118514 \| 103552339 \|
	\| HG375_PATCH \| X \| 104423968 \| 104489003 \|
	\| HG1434_PATCH \| X \| 112661943 \| 113517668 \|
	\| HG1462_PATCH \| X \| 113567669 \| 114955282 \|
	\| HG1463_PATCH \| X \| 114955283 \| 115682290 \|
	\| HG1490_PATCH \| X \| 115735813 \| 115918761 \|
	\| HG1442_PATCH \| X \| 119081534 \| 119495352 \|
	\| HG1443_HG1444_PATCH \| X \| 134202887 \| 135196638 \|
	\| HG1453_PATCH \| X \| 139986363 \| 140891303 \|
	\| HG1458_PATCH \| X \| 142299066 \| 143507324 \|
	\| HG1287_PATCH \| 1 \| 143871003 \| 150440213 \|
	\| HSCHR6_2_CTG5 \| 6 \| 169194170 \| 169351741 \|
	\| HG1592_PATCH \| 14 \| 105803171 \| 107268434 \|
	\| HG1079_PATCH \| 19 \| 54528888 \| 55595686 \|
	\| HSCHR22_1_CTG1 \| 22 \| 42473660 \| 42648568 \|
	\| HG29_PATCH \| X \| 46609875 \| 47079810 \|
	\| HG1436_HG1432_PATCH \| X \| 47619971 \| 49974173 \|
	\| HG953_PATCH \| 2 \| 70660851 \| 71260429 \|
	\| HG357_PATCH \| 6 \| 107143253 \| 107495574 \|
	\| HG50_PATCH \| 9 \| 92286339 \| 92528796 \|
	\| HG1074_PATCH \| 10 \| 51028872 \| 51886485 \|
	\| HG944_PATCH \| 20 \| 53978607 \| 54220677 \|
	\| HSCHR22_2_CTG1 \| 22 \| 42473660 \| 42535544 \|
	\| HG1426_PATCH \| X \| 76703693 \| 77450502 \|
	\| HG1472_PATCH \| 1 \| 3823584 \| 3845268 \|
	\| HG1292_PATCH \| 1 \| 120471050 \| 121346350 \|
	\| HG957_PATCH \| 3 \| 53185062 \| 53448933 \|
	\| HG174_HG254_PATCH \| 4 \| 10001 \| 507004 \|
	\| HG1082_HG167_PATCH \| 5 \| 140144410 \| 140687716 \|
	\| HG193_PATCH \| 6 \| 157539699 \| 157789039 \|
	\| HG444_PATCH \| 7 \| 65012083 \| 65286677 \|
	\| HG1699_PATCH \| 8 \| 48176435 \| 48405006 \|
	\| HG418_PATCH \| 8 \| 143711229 \| 143813536 \|
	\| HG979_PATCH \| 10 \| 116786296 \| 117806873 \|
	\| HG122_PATCH \| 11 \| 118245278 \| 118653701 \|
	\| HG344_PATCH \| 12 \| 6907581 \| 7178849 \|
	\| HG1595_PATCH \| 12 \| 121761801 \| 122096824 \|
	\| HG417_PATCH \| 17 \| 1 \| 252428 \|
	\| HSCHR17_1_CTG1 \| 17 \| 1 \| 252428 \|
	\| HG987_PATCH \| 17 \| 21250948 \| 21566608 \|
	\| HG1591_PATCH \| 17 \| 22174115 \| 22263006 \|
	\| HG385_PATCH \| 17 \| 34034852 \| 34246252 \|
	\| HG745_PATCH \| 17 \| 36372617 \| 36711255 \|
	\| HG730_PATCH \| 19 \| 20193557 \| 20845946 \|
	\| HG1350_HG959_PATCH \| 19 \| 41936867 \| 42514711 \|
	\| HG237_PATCH \| 21 \| 45916561 \| 46223846 \|
	\| HG480_HG481_PATCH \| X \| 803878 \| 1227822 \|
	\| HG1459_PATCH \| X \| 143557325 \| 150174083 \|
	\| HG1497_PATCH \| X \| 151734490 \| 154906585 \|
	+--------------------------+------------+-----------+-----------+
	204 rows in set (0.05 sec)

view raw biostars-106362.txt hosted with ❤ by GitHub

ADD COMMENT • link updated 3.3 years ago by Ram 45k • written 10.7 years ago by Bert Overduin ★ 3.7k

Entering edit mode

Thank you Bert!

ADD REPLY • link updated 3.3 years ago by Ram 45k • written 10.7 years ago by Khader Shameer 18k

Entering edit mode

some of the portions in orientation column denoted as 'b', can you please tell why it so?, what it is meant by?

ADD REPLY • link 6.7 years ago by sujasubramanian ▴ 70

Entering edit mode

10.7 years ago

Devon Ryan 105k

I suspect that most, if not all, of those are specified here.

ADD COMMENT • link updated 3.3 years ago by Ram 45k • written 10.7 years ago by Devon Ryan 105k

Entering edit mode

This NCBI FTP area was moved in December 2015, file at the link above was archived here

ADD REPLY • link 9.0 years ago by Louis ▴ 160

Entering edit mode

Thanks Devon!

ADD REPLY • link 10.7 years ago by Khader Shameer 18k