spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1/**2 * Dataset downloads (CSV / JSON / GeoJSON), streamed in 1 000-row batches, ≤ 50 000 rows, with license gating:3 * a row whose ONLY current provenance sources forbid redistribution (sources.redistribution = 'restricted') is4 * excluded and the excluded sources are listed. Nothing is ever blanked silently.5 */6import type { DownloadDataset } from "@dci/core";7import { pg, andAll, facilityJoins, projectLive, yearExpr, type Fragment, type Sql } from "../lib/sql.js";8import { int, num, reqStr, str, type Row } from "../lib/rows.js";9import { csvLine } from "../lib/csv.js";10import { csv } from "../lib/http.js";11import { facilityConds, toFacilityQuery } from "./facilities.js";1213export const DOWNLOAD_KEYS = ["facilities", "projects", "operators", "events", "cloud-regions", "ixps", "countries", "markets"] as const;14export type DownloadKey = (typeof DownloadKeys)[number];15const DownloadKeys = DOWNLOAD_KEYS;16export type DownloadFormat = "csv" | "json" | "geojson";17export const MAX_ROWS = 50_000;18export const DOWNLOAD_LICENSE = "Each row carries the names of the sources it was built from; re-users must keep that attribution and respect each source's licence (see /api/v1/sources). Rows whose only sources forbid redistribution are excluded from every download and listed in excludedSources. Figures are published values only (no estimates unless flagged), with the coverage caveats of the API.";1920export interface DownloadFilters { country?: string; status?: string; type?: string; operator?: string; metro?: string; min_mw?: number; max_mw?: number; ai?: boolean; hyperscale?: boolean; has_mw?: boolean; project_status?: string; expected_from?: number; expected_to?: number; event_type?: string; since?: string; until?: string; min_significance?: number }2122interface Spec { key: DownloadKey; label: string; description: string; formats: DownloadFormat[]; filters: string[]; columns: string[]; entityType: string | null; gated: boolean }2324export const SPECS: Spec[] = [25 { key: "facilities", label: "Facilities", description: "Every indexed data center / campus / building record (merged duplicates excluded) with location, status, capacity ontology, AI evidence and sources.", formats: ["csv", "json", "geojson"], filters: ["country", "status", "type", "operator", "metro", "min_mw", "max_mw", "ai", "hyperscale", "has_mw"], columns: ["id", "slug", "name", "operator", "country_iso2", "city", "metro", "lat", "lng", "geo_precision", "status", "facility_type", "it_capacity_mw", "total_power_mw", "planned_power_mw", "utility_capacity_mw", "grid_connection_mw", "mw_is_estimate", "record_scope", "parent_facility_id", "ai_evidence", "opened_on", "confidence", "completeness", "source_count", "sources", "updated_at"], entityType: "facility", gated: true },26 { key: "projects", label: "Projects", description: "Live infrastructure projects (false positives hidden by review and merged duplicates excluded) with lifecycle dates, planned MW and investment scopes.", formats: ["csv", "json", "geojson"], filters: ["country", "project_status", "operator", "metro", "min_mw", "max_mw", "ai", "expected_from", "expected_to"], columns: ["id", "slug", "name", "operator", "country_iso2", "city", "metro", "lat", "lng", "geo_precision", "status", "project_class", "evidence_level", "announced_on", "permit_filed_on", "approved_on", "construction_started_on", "expected_opening", "opened_on", "planned_mw", "capacity_scope", "investment_usd", "investment_scope", "ai_evidence", "confidence", "sources", "last_update"], entityType: "project", gated: true },27 { key: "operators", label: "Operators", description: "Operators with containment-aware facility counts and known MW (from the worker-refreshed stats).", formats: ["csv", "json"], filters: ["country"], columns: ["id", "slug", "name", "kind", "hq_country_iso2", "website", "is_cloud_provider", "is_carrier", "facility_count", "country_count", "known_mw", "planned_mw", "project_count", "mw_coverage", "sources", "updated_at"], entityType: "operator", gated: true },28 { key: "events", label: "Events", description: "Change feed: detected facility / project / market events with source and significance (rejected events excluded).", formats: ["csv", "json"], filters: ["event_type", "country", "operator", "since", "until", "min_significance"], columns: ["id", "entity_type", "entity_id", "event_type", "detected_at", "effective_date", "title", "url", "source", "source_kind", "significance", "significance_band", "confidence", "country_iso2", "operator", "old_value", "new_value"], entityType: null, gated: true },29 { key: "cloud-regions", label: "Cloud regions", description: "Public cloud regions by provider with city-level coordinates.", formats: ["csv", "json", "geojson"], filters: ["country", "operator"], columns: ["id", "slug", "provider", "code", "name", "city", "country_iso2", "lat", "lng", "geo_precision", "availability_zones", "launched_on", "status", "is_sovereign", "source_url", "sources", "updated_at"], entityType: "cloud_region", gated: true },30 { key: "ixps", label: "Internet exchanges", description: "IXPs with network counts and facility links; coordinates are the metro reference point when known.", formats: ["csv", "json", "geojson"], filters: ["country"], columns: ["id", "slug", "name", "name_long", "city", "country_iso2", "website", "network_count", "facility_count", "metro", "lat", "lng", "sources", "updated_at"], entityType: "ixp", gated: true },31 { key: "countries", label: "Countries", description: "Countries with containment-aware facility totals (worker-refreshed stats) and public energy indicators.", formats: ["csv", "json", "geojson"], filters: [], columns: ["iso2", "iso3", "slug", "name", "region", "subregion", "population", "gdp_usd", "electricity_twh", "renewable_share", "stats_year", "facility_count", "known_mw", "mw_coverage", "lat", "lng", "updated_at"], entityType: null, gated: false },32 { key: "markets", label: "Markets (metros)", description: "Data center markets / metros with reference point, radius and containment-aware totals.", formats: ["csv", "json", "geojson"], filters: ["country"], columns: ["id", "slug", "name", "country_iso2", "region_name", "lat", "lng", "radius_km", "facility_count", "known_mw", "mw_coverage", "operator_count", "updated_at"], entityType: null, gated: false },33];3435export function specFor(key: string): Spec | null {36 return SPECS.find((s) => s.key === key) ?? null;37}3839/** Sources that forbid redistribution (their rows are gated out). */40export async function restrictedSources(): Promise<Array<{ id: string; name: string; reason: string }>> {41 const sql = pg();42 const rows = await sql<Row[]>`select id, name from sources where redistribution = 'restricted' order by name`;43 return rows.map((r) => ({ id: reqStr(r.id), name: reqStr(r.name), reason: "redistribution = restricted" }));44}4546/** A row is gated out when it has provenance and every current source is restricted. */47function gate(sql: Sql, entityType: string, idCol: Fragment): Fragment {48 return sql`not (exists (select 1 from provenance gp where gp.entity_type = ${entityType} and gp.entity_id = ${idCol} and gp.is_current)49 and not exists (select 1 from provenance gp join sources gs on gs.id = gp.source_id where gp.entity_type = ${entityType} and gp.entity_id = ${idCol} and gp.is_current and coalesce(gs.redistribution, 'unknown') <> 'restricted'))`;50}5152function sourcesCol(sql: Sql, entityType: string, idCol: Fragment): Fragment {53 return sql`(select string_agg(distinct s.name, ';' order by s.name) from provenance sp join sources s on s.id = sp.source_id where sp.entity_type = ${entityType} and sp.entity_id = ${idCol} and sp.is_current)`;54}5556/** SELECT for a dataset, columns aliased exactly as in the spec (extra `lat`/`lng` are used for GeoJSON). */57function query(sql: Sql, key: DownloadKey, f: DownloadFilters): Fragment {58 switch (key) {59 case "facilities": {60 const conds = facilityConds(sql, toFacilityQuery({ country: f.country, status: f.status, type: f.type, operator: f.operator, metro: f.metro, min_mw: f.min_mw, max_mw: f.max_mw, ai: f.ai, hyperscale: f.hyperscale, has_mw: f.has_mw }));61 conds.push(gate(sql, "facility", sql`f.id`));62 return sql`select f.id, f.slug, f.name, o.name as operator, f.country_iso2, f.city, m.name as metro, f.lat, f.lng, f.geo_precision, f.status, f.facility_type, f.it_capacity_mw, f.total_power_mw, f.planned_power_mw, f.utility_capacity_mw, f.grid_connection_mw, f.mw_is_estimate, f.record_scope, f.parent_facility_id, f.ai_evidence, f.opened_on, f.confidence, f.completeness, f.source_count, ${sourcesCol(sql, "facility", sql`f.id`)} as sources, f.updated_at63 from facilities f ${facilityJoins(sql)} where ${andAll(sql, conds)} order by f.country_iso2, f.name limit ${MAX_ROWS}`;64 }65 case "projects": {66 const c: Fragment[] = [projectLive(sql), gate(sql, "project", sql`p.id`)];67 const st = csv(f.project_status ?? f.status);68 if (st.length) c.push(sql`p.status = any(${st})`);69 const co = csv(f.country).map((x) => x.toUpperCase());70 if (co.length) c.push(sql`p.country_iso2 = any(${co})`);71 if (f.operator) c.push(sql`(o.slug = ${f.operator} or o.id = ${f.operator})`);72 if (f.metro) c.push(sql`(m.slug = ${f.metro} or m.id = ${f.metro})`);73 if (f.min_mw != null) c.push(sql`p.planned_mw >= ${f.min_mw}`);74 if (f.max_mw != null) c.push(sql`p.planned_mw <= ${f.max_mw}`);75 if (f.ai === true) c.push(sql`(p.is_ai or p.ai_evidence in ('confirmed', 'likely'))`);76 if (f.expected_from != null) c.push(sql`${yearExpr(sql, sql`p.expected_opening`)} >= ${f.expected_from}`);77 if (f.expected_to != null) c.push(sql`${yearExpr(sql, sql`p.expected_opening`)} <= ${f.expected_to}`);78 return sql`select p.id, p.slug, p.name, o.name as operator, p.country_iso2, p.city, m.name as metro, p.lat, p.lng, p.geo_precision, p.status, p.project_class, p.evidence_level, p.announced_on, p.permit_filed_on, p.approved_on, p.construction_started_on, p.expected_opening, p.opened_on, p.planned_mw, p.capacity_scope, p.investment_usd, p.investment_scope, p.ai_evidence, p.confidence, ${sourcesCol(sql, "project", sql`p.id`)} as sources, p.last_update79 from projects p left join operators o on o.id = p.operator_id left join metros m on m.id = p.metro_id where ${andAll(sql, c)} order by p.planned_mw desc nulls last, p.name limit ${MAX_ROWS}`;80 }81 case "operators": {82 const c: Fragment[] = [gate(sql, "operator", sql`o.id`)];83 if (f.country) c.push(sql`(o.hq_country_iso2 = ${f.country.toUpperCase()} or exists (select 1 from facilities x where x.operator_id = o.id and x.country_iso2 = ${f.country.toUpperCase()} and x.merged_into is null))`);84 return sql`select o.id, o.slug, o.name, o.kind, o.hq_country_iso2, o.website, o.is_cloud_provider, o.is_carrier, (o.stats->>'facilityCount')::int as facility_count, (o.stats->>'countryCount')::int as country_count, (o.stats->>'knownMw')::float as known_mw, (o.stats->>'plannedMw')::float as planned_mw, (o.stats->>'projectCount')::int as project_count, (o.stats->>'mwCoverage')::float as mw_coverage, ${sourcesCol(sql, "operator", sql`o.id`)} as sources, o.updated_at85 from operators o where ${andAll(sql, c)} order by (o.stats->>'facilityCount')::int desc nulls last, o.name limit ${MAX_ROWS}`;86 }87 case "events": {88 const c: Fragment[] = [sql`e.review_status <> 'rejected'`, sql`coalesce(s.redistribution, 'unknown') <> 'restricted'`];89 const types = csv(f.event_type);90 if (types.length) c.push(sql`e.event_type = any(${types})`);91 if (f.country) c.push(sql`e.country_iso2 = ${f.country.toUpperCase()}`);92 if (f.operator) c.push(sql`(o.slug = ${f.operator} or o.id = ${f.operator})`);93 if (f.since) c.push(sql`e.detected_at >= ${f.since}::timestamptz`);94 if (f.until) c.push(sql`e.detected_at <= ${f.until}::timestamptz`);95 if (f.min_significance != null) c.push(sql`e.significance >= ${f.min_significance}`);96 return sql`select e.id, e.entity_type, e.entity_id, e.event_type, e.detected_at, e.effective_date, e.title, e.url, s.name as source, coalesce(e.source_kind, s.kind) as source_kind, e.significance, case when e.significance >= 75 then 'major' when e.significance >= 45 then 'medium' else 'minor' end as significance_band, e.confidence, e.country_iso2, o.name as operator, e.old_value, e.new_value97 from events e left join sources s on s.id = e.source_id left join operators o on o.id = e.operator_id where ${andAll(sql, c)} order by e.detected_at desc limit ${MAX_ROWS}`;98 }99 case "cloud-regions": {100 const c: Fragment[] = [gate(sql, "cloud_region", sql`r.id`)];101 if (f.country) c.push(sql`r.country_iso2 = ${f.country.toUpperCase()}`);102 if (f.operator) c.push(sql`(pr.slug = ${f.operator} or pr.id = ${f.operator})`);103 return sql`select r.id, r.slug, pr.name as provider, r.code, r.name, r.city, r.country_iso2, r.lat, r.lng, r.geo_precision, r.availability_zones, r.launched_on, r.status, r.is_sovereign, r.source_url, ${sourcesCol(sql, "cloud_region", sql`r.id`)} as sources, r.updated_at104 from cloud_regions r join operators pr on pr.id = r.provider_id where ${andAll(sql, c)} order by pr.name, r.code limit ${MAX_ROWS}`;105 }106 case "ixps": {107 const c: Fragment[] = [gate(sql, "ixp", sql`x.id`)];108 if (f.country) c.push(sql`x.country_iso2 = ${f.country.toUpperCase()}`);109 return sql`select x.id, x.slug, x.name, x.name_long, x.city, x.country_iso2, x.website, x.network_count, (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count, m.name as metro, m.lat, m.lng, ${sourcesCol(sql, "ixp", sql`x.id`)} as sources, x.updated_at110 from ixps x left join metros m on m.id = x.metro_id where ${andAll(sql, c)} order by x.network_count desc nulls last, x.name limit ${MAX_ROWS}`;111 }112 case "countries":113 return sql`select c.iso2, c.iso3, c.slug, c.name, c.region, c.subregion, c.population, c.gdp_usd, c.electricity_twh, c.renewable_share, c.stats_year, (c.stats->>'facilityCount')::int as facility_count, (c.stats->>'knownMw')::float as known_mw, (c.stats->>'mwCoverage')::float as mw_coverage, c.lat, c.lng, c.updated_at114 from countries c where coalesce((c.stats->>'facilityCount')::int, 0) > 0 or exists (select 1 from cloud_regions r where r.country_iso2 = c.iso2) order by (c.stats->>'facilityCount')::int desc nulls last, c.name limit ${MAX_ROWS}`;115 case "markets": {116 const c: Fragment[] = [sql`true`];117 if (f.country) c.push(sql`m.country_iso2 = ${f.country.toUpperCase()}`);118 return sql`select m.id, m.slug, m.name, m.country_iso2, m.region_name, m.lat, m.lng, m.radius_km, (m.stats->>'facilityCount')::int as facility_count, (m.stats->>'knownMw')::float as known_mw, (m.stats->>'mwCoverage')::float as mw_coverage, (m.stats->>'operatorCount')::int as operator_count, m.updated_at119 from metros m where ${andAll(sql, c)} order by (m.stats->>'facilityCount')::int desc nulls last, m.name limit ${MAX_ROWS}`;120 }121 }122}123124/** Dataset catalogue with live row counts. */125export async function listDatasets(): Promise<DownloadDataset[]> {126 const sql = pg();127 const [counts, excluded, attributions] = await Promise.all([128 sql<Row[]>`select129 (select count(*) from facilities where merged_into is null)::int as facilities,130 (select count(*) from projects p where ${projectLive(sql)})::int as projects,131 (select count(*) from operators)::int as operators,132 (select count(*) from events where review_status <> 'rejected')::int as events,133 (select count(*) from cloud_regions)::int as "cloud-regions",134 (select count(*) from ixps)::int as ixps,135 (select count(*) from countries c where coalesce((c.stats->>'facilityCount')::int, 0) > 0 or exists (select 1 from cloud_regions r where r.country_iso2 = c.iso2))::int as countries,136 (select count(*) from metros)::int as markets`,137 restrictedSources(),138 sql<Row[]>`select p.entity_type, array_agg(distinct s.attribution) filter (where s.attribution is not null) as attributions from provenance p join sources s on s.id = p.source_id where p.is_current group by p.entity_type`,139 ]);140 const attr = new Map<string, string[]>(attributions.map((r) => [reqStr(r.entity_type), ((r.attributions as string[] | null) ?? []).slice(0, 30)]));141 const eventAttr = (await sql<Row[]>`select distinct s.attribution from events e join sources s on s.id = e.source_id where s.attribution is not null limit 30`).map((r) => reqStr(r.attribution));142 const c = counts[0] ?? {};143 return SPECS.map((s) => ({144 key: s.key,145 label: s.label,146 description: s.description,147 formats: s.formats,148 filters: s.filters,149 rows: Math.min(MAX_ROWS, int(c[s.key])),150 license: DOWNLOAD_LICENSE,151 attribution: s.entityType ? attr.get(s.entityType) ?? [] : s.key === "events" ? eventAttr : [],152 excludedSources: s.gated ? excluded : [],153 }));154}155156function cell(v: unknown): unknown {157 if (v == null) return null;158 if (typeof v === "object" && !(v instanceof Date)) return JSON.stringify(v);159 return v;160}161162/** Async generator of chunks for a dataset in the requested format (header first). */163export async function* streamDataset(key: DownloadKey, format: DownloadFormat, f: DownloadFilters, excluded: Array<{ id: string; name: string; reason: string }>): AsyncGenerator<string> {164 const sql = pg();165 const spec = specFor(key)!;166 const cols = spec.columns;167 const q = query(sql, key, f);168 let total = 0, skipped = 0, first = true;169 if (format === "csv") yield csvLine(cols);170 else if (format === "json") yield `{"data":[`;171 else yield `{"type":"FeatureCollection","features":[`;172 const cursor = q.cursor(1000);173 for await (const rows of cursor) {174 let chunk = "";175 for (const r of rows as Row[]) {176 total++;177 if (format === "csv") { chunk += csvLine(cols.map((c) => cell(r[c]))); continue; }178 const obj: Record<string, unknown> = {};179 for (const c of cols) obj[c] = r[c] ?? null;180 if (format === "json") { chunk += `${first ? "" : ","}${JSON.stringify(obj)}`; first = false; continue; }181 const lat = num(r.lat), lng = num(r.lng);182 if (lat == null || lng == null) { skipped++; continue; }183 const { lat: _a, lng: _b, ...props } = obj;184 chunk += `${first ? "" : ","}${JSON.stringify({ type: "Feature", id: str(r.id ?? r.iso2), geometry: { type: "Point", coordinates: [lng, lat] }, properties: props })}`;185 first = false;186 }187 if (chunk) yield chunk;188 }189 const meta = { total: format === "geojson" ? total - skipped : total, rowsWithoutCoordinates: format === "geojson" ? skipped : undefined, excludedSources: excluded, generatedAt: new Date().toISOString(), license: DOWNLOAD_LICENSE, maxRows: MAX_ROWS };190 if (format === "json") yield `],"meta":${JSON.stringify(meta)}}`;191 else if (format === "geojson") yield `],"meta":${JSON.stringify(meta)}}`;192}193194export function contentType(format: DownloadFormat): string {195 return format === "csv" ? "text/csv; charset=utf-8" : format === "json" ? "application/json; charset=utf-8" : "application/geo+json; charset=utf-8";196}197