README.md

Genome provider analysis

This is a selection of data and schema which can be used to investigate similarities/differences between the provided genomes files for GRCh38/hg38.

ensembl.primary.report.tsv

ensembl.toplevel.report.tsv

example-queries.sql

-- Do a comparison between the four sets using length
select u.id, ep.id, et.id, n.id, u.length, ep.length, et.length, n.length
from ucsc u
join ensembl_primary ep on (u.length = ep.length)
join ensembl_toplevel et on (u.length = et.length)
join ncbi n on (u.length = n.length)
order by ep.id;


-- Do a comparison between the four sets using md5 as the joining key
select u.id, ep.id, et.id, n.id, u.md5, ep.md5, et.md5, n.md5
from ucsc u
join ensembl_primary ep on (u.md5 = ep.md5)
join ensembl_toplevel et on (u.md5 = et.md5)
join ncbi n on (u.md5 = n.md5)
order by ep.id;

loading.sql

.separator "\t"
.import --skip 1 ucsc.report.tsv ucsc
.import --skip 1 ncbi.report.tsv ncbi
.import --skip 1 ensembl.primary.report.tsv ensembl_primary
.import --skip 1 ensembl.toplevel.report.tsv ensembl_toplevel

ncbi.report.tsv

schema.sql

CREATE TABLE IF NOT EXISTS "ucsc" (
	"id"	TEXT NOT NULL,
	"length"	INTEGER NOT NULL,
	"md5"	TEXT NOT NULL,
	"trunc512"	TEXT NOT NULL,
	"ga4gh"	TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "ensembl_primary" (
	"id"	TEXT NOT NULL,
	"length"	INTEGER NOT NULL,
	"md5"	TEXT NOT NULL,
	"trunc512"	TEXT NOT NULL,
	"ga4gh"	TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "ensembl_toplevel" (
	"id"	TEXT NOT NULL,
	"length"	INTEGER NOT NULL,
	"md5"	TEXT NOT NULL,
	"trunc512"	TEXT NOT NULL,
	"ga4gh"	TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "ncbi" (
	"id"	TEXT NOT NULL,
	"length"	INTEGER NOT NULL,
	"md5"	TEXT NOT NULL,
	"trunc512"	TEXT NOT NULL,
	"ga4gh"	TEXT NOT NULL
);

ucsc.report.tsv