spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1/**2 * /explore — structured query over facilities or live projects with facets, charts (containment-aware MW) and3 * map points. Facets and charts are computed on the FULL filtered set; `items` is one page.4 */5import type { ExploreQuery, ExploreResponse, MapPoint } from "@dci/core";6import { pg, andAll, facilityJoins, facilitySummaryCols, facilityView, knownMwAgg, pipelineMwAgg, countedAgg, hasMwAgg, mwExpr, projectJoins, projectSummaryCols, projectLive, yearExpr, likePattern, page, PIPELINE_SET, OPERATIONAL_SET, type Fragment, type Sql } from "../lib/sql.js";7import { int, num, reqStr, str, type Row } from "../lib/rows.js";8import { asPrecision, asStatus, asType, facilitySummary, projectSummary, round2, share } from "../lib/dto.js";9import { csv } from "../lib/http.js";10import { facilityConds, toFacilityQuery } from "./facilities.js";11import { MAX_POINTS } from "./map.js";1213export const EXPLORE_METHODOLOGY = "Facets and charts cover the whole filtered set (items are one page). Facility MW in charts is containment-aware (a campus and its buildings are never both summed) and uses published figures only: IT capacity, else total power, else planned power. Project MW is the planned MW published on live project records (false positives hidden by review and merged duplicates excluded). mwCoverage is the share of rows with any MW figure. Map points are capped at 5 000 (degraded = true when the cap was hit).";1415const AI_LEVELS_FOR: Record<string, string[]> = { confirmed: ["confirmed"], likely: ["confirmed", "likely"], associated: ["confirmed", "likely", "associated"] };1617function aiCond(sql: Sql, alias: Fragment, ai: ExploreQuery["ai"], legacyFlag: Fragment): Fragment | null {18 if (!ai) return null;19 if (ai === "any") return sql`(${alias}.ai_evidence <> 'unknown' or ${legacyFlag})`;20 const levels = AI_LEVELS_FOR[ai] ?? ["confirmed", "likely"];21 return ai === "confirmed" ? sql`${alias}.ai_evidence = any(${levels})` : sql`(${alias}.ai_evidence = any(${levels}) or ${legacyFlag})`;22}2324function facilityExploreConds(sql: Sql, q: ExploreQuery): Fragment[] {25 const conds = facilityConds(sql, toFacilityQuery({ q: q.q, country: q.country, metro: q.metro, operator: q.operator, status: q.status, type: q.type, min_mw: q.min_mw, max_mw: q.max_mw, hyperscale: q.hyperscale, has_mw: q.has_mw, confidence: q.confidence, opened_from: q.opened_from, opened_to: q.opened_to }));26 const ai = aiCond(sql, sql`f`, q.ai, sql`f.is_ai`);27 if (ai) conds.push(ai);28 const prec = csv(q.location_precision);29 if (prec.length) conds.push(sql`f.geo_precision = any(${prec})`);30 const expected = yearExpr(sql, sql`coalesce(f.opened_on, f.construction_started_on, f.announced_on)`);31 if (q.expected_before != null) conds.push(sql`f.status = any(${PIPELINE_SET}) and ${expected} <= ${q.expected_before}`);32 if (q.expected_after != null) conds.push(sql`f.status = any(${PIPELINE_SET}) and ${expected} >= ${q.expected_after}`);33 if (q.announced_since) conds.push(sql`f.announced_on >= ${q.announced_since}`);34 return conds;35}3637function projectExploreConds(sql: Sql, q: ExploreQuery): Fragment[] {38 const conds: Fragment[] = [projectLive(sql)];39 const statuses = csv(q.project_status ?? q.status);40 if (statuses.length) conds.push(sql`p.status = any(${statuses})`);41 const countries = csv(q.country).map((c) => c.toUpperCase());42 if (countries.length) conds.push(sql`p.country_iso2 = any(${countries})`);43 if (q.metro) conds.push(sql`(m.slug = ${q.metro} or m.id = ${q.metro})`);44 if (q.operator) conds.push(sql`(o.slug = ${q.operator} or o.id = ${q.operator})`);45 if (q.min_mw != null) conds.push(sql`p.planned_mw >= ${q.min_mw}`);46 if (q.max_mw != null) conds.push(sql`p.planned_mw <= ${q.max_mw}`);47 const ai = aiCond(sql, sql`p`, q.ai, sql`p.is_ai`);48 if (ai) conds.push(ai);49 if (q.hyperscale === true) conds.push(sql`exists (select 1 from operators ho where ho.id = p.operator_id and ho.kind = 'hyperscaler')`);50 const expected = yearExpr(sql, sql`p.expected_opening`);51 if (q.expected_before != null) conds.push(sql`${expected} <= ${q.expected_before}`);52 if (q.expected_after != null) conds.push(sql`${expected} >= ${q.expected_after}`);53 if (q.announced_since) conds.push(sql`p.announced_on >= ${q.announced_since}`);54 const conf = csv(q.confidence);55 if (conf.length) conds.push(sql`p.confidence = any(${conf})`);56 const cls = csv(q.project_class);57 if (cls.length) conds.push(sql`p.project_class = any(${cls})`);58 const prec = csv(q.location_precision);59 if (prec.length) conds.push(sql`p.geo_precision = any(${prec})`);60 if (q.has_mw === true) conds.push(sql`p.planned_mw is not null`);61 if (q.has_mw === false) conds.push(sql`p.planned_mw is null`);62 if (q.q) conds.push(sql`(p.name ilike ${likePattern(q.q)} or o.name ilike ${likePattern(q.q)} or p.city ilike ${likePattern(q.q)})`);63 return conds;64}6566function facilityOrder(sql: Sql, sort: string | undefined, order: "asc" | "desc" | undefined): Fragment {67 const asc = (order ?? (sort === "name" ? "asc" : "desc")) === "asc";68 const dir = asc ? sql`asc` : sql`desc`;69 switch (sort) {70 case "name": return sql`f.name ${dir}, f.id`;71 case "mw": return sql`${mwExpr(sql)} ${dir} nulls last, f.name`;72 case "opened": case "opening": return sql`f.opened_on ${dir} nulls last, f.name`;73 case "announced": return sql`f.announced_on ${dir} nulls last, f.name`;74 case "completeness": return sql`f.completeness ${dir}, f.name`;75 default: return sql`f.updated_at ${dir}, f.id`;76 }77}7879function projectOrder(sql: Sql, sort: string | undefined, order: "asc" | "desc" | undefined): Fragment {80 const asc = (order ?? (sort === "name" ? "asc" : "desc")) === "asc";81 const dir = asc ? sql`asc` : sql`desc`;82 switch (sort) {83 case "name": return sql`p.name ${dir}, p.id`;84 case "mw": return sql`p.planned_mw ${dir} nulls last, p.name`;85 case "announced": return sql`p.announced_on ${dir} nulls last, p.name`;86 case "opening": case "opened": return sql`p.expected_opening ${dir} nulls last, p.name`;87 default: return sql`p.last_update ${dir}, p.id`;88 }89}9091export async function explore(q: ExploreQuery): Promise<ExploreResponse> {92 const sql = pg();93 const pg_ = page(q.page, q.per_page, 100, 24);94 const entity = q.entity ?? "facilities";95 if (entity === "projects") {96 const where = andAll(sql, projectExploreConds(sql, q));97 const [items, facets, charts, pts, cov] = await Promise.all([98 sql<Row[]>`select ${projectSummaryCols(sql)}, count(*) over() as total from projects p ${projectJoins(sql)} where ${where} order by ${projectOrder(sql, q.sort, q.order)} limit ${pg_.perPage} offset ${pg_.offset}`,99 Promise.all([100 sql<Row[]>`select p.status as key, count(*)::int as n from projects p ${projectJoins(sql)} where ${where} group by 1 order by n desc`,101 sql<Row[]>`select p.country_iso2 as key, c.name, count(*)::int as n from projects p ${projectJoins(sql)} left join countries c on c.iso2 = p.country_iso2 where ${where} and p.country_iso2 is not null group by 1, 2 order by n desc limit 30`,102 sql<Row[]>`select o.slug as key, o.name, count(*)::int as n from projects p ${projectJoins(sql)} where ${where} and o.id is not null group by 1, 2 order by n desc limit 15`,103 sql<Row[]>`select p.ai_evidence as key, count(*)::int as n from projects p ${projectJoins(sql)} where ${where} group by 1 order by n desc`,104 ]),105 Promise.all([106 sql<Row[]>`select p.status as key, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p ${projectJoins(sql)} where ${where} group by 1 order by n desc`,107 sql<Row[]>`select p.country_iso2 as key, c.name, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p ${projectJoins(sql)} left join countries c on c.iso2 = p.country_iso2 where ${where} and p.country_iso2 is not null group by 1, 2 order by n desc limit 15`,108 sql<Row[]>`select ${yearExpr(sql, sql`p.expected_opening`)} as year, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p ${projectJoins(sql)} where ${where} and p.expected_opening ~ '^\\d{4}' group by 1 order by 1`,109 ]),110 sql<Row[]>`select p.id, p.slug, p.name, o.name as op_name, p.lat, p.lng, p.status, p.planned_mw, p.geo_precision, p.is_ai, p.ai_evidence, p.country_iso2, ${yearExpr(sql, sql`p.expected_opening`)} as y from projects p ${projectJoins(sql)} where ${where} and p.lat is not null and p.lng is not null order by p.planned_mw desc nulls last limit ${MAX_POINTS + 1}`,111 sql<Row[]>`select count(*)::int as n, count(*) filter (where p.planned_mw is not null)::int as with_mw from projects p ${projectJoins(sql)} where ${where}`,112 ]);113 const [fStatus, fCountry, fOperator, fAi] = facets;114 const [cStatus, cCountry, cYear] = charts;115 const degraded = pts.length > MAX_POINTS;116 const points: MapPoint[] = (degraded ? [] : pts).map((r) => {117 const p: MapPoint = { id: reqStr(r.id), slug: reqStr(r.slug), n: reqStr(r.name), o: str(r.op_name), lat: num(r.lat) ?? 0, lng: num(r.lng) ?? 0, s: asStatus(r.status), t: "unknown", mw: num(r.planned_mw), p: asPrecision(r.geo_precision), c: str(r.country_iso2), k: "project" };118 const ai = str(r.ai_evidence);119 if (r.is_ai === true || ai === "confirmed" || ai === "likely") p.ai = 1;120 const y = num(r.y);121 if (y != null) p.y = y;122 return p;123 });124 return {125 query: q,126 total: items.length ? int(items[0]!.total) : int(cov[0]?.n),127 items: items.map(projectSummary),128 facets: {129 status: fStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n) })),130 country: fCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n) })),131 operator: fOperator.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name), count: int(r.n) })),132 ai: fAi.map((r) => ({ key: reqStr(r.key, "unknown"), count: int(r.n) })),133 },134 charts: {135 byStatus: cStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n), mw: round2(num(r.mw)) })),136 byCountry: cCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n), mw: round2(num(r.mw)) })),137 byYear: cYear.map((r) => ({ year: int(r.year), count: int(r.n), mw: round2(num(r.mw)) })).filter((x) => x.year > 0),138 },139 map: { points, total: degraded ? int(cov[0]?.n) : points.length, degraded },140 mwCoverage: share(int(cov[0]?.with_mw), int(cov[0]?.n)),141 };142 }143144 const conds = facilityExploreConds(sql, q);145 const where = andAll(sql, conds);146 const known = knownMwAgg(sql), pipe = pipelineMwAgg(sql), counted = countedAgg(sql), hasMw = hasMwAgg(sql);147 const chartMw = sql`sum(case when f.status = any(${OPERATIONAL_SET}) then ${known} else ${pipe} end)::float`;148 const [items, facets, charts, pts, cov] = await Promise.all([149 sql<Row[]>`select ${facilitySummaryCols(sql)}, count(*) over() as total from facilities f ${facilityJoins(sql)} where ${where} order by ${facilityOrder(sql, q.sort, q.order)} limit ${pg_.perPage} offset ${pg_.offset}`,150 Promise.all([151 sql<Row[]>`select f.status as key, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,152 sql<Row[]>`select f.country_iso2 as key, c.name, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} and f.country_iso2 is not null group by 1, 2 order by n desc limit 30`,153 sql<Row[]>`select o.slug as key, o.name, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} and o.id is not null group by 1, 2 order by n desc limit 15`,154 sql<Row[]>`select f.facility_type as key, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,155 sql<Row[]>`select f.ai_evidence as key, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,156 ]),157 Promise.all([158 sql<Row[]>`select f.status as key, count(*) filter (where ${counted})::int as n, ${chartMw} as mw from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,159 sql<Row[]>`select f.country_iso2 as key, c.name, count(*) filter (where ${counted})::int as n, ${chartMw} as mw from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where} and f.country_iso2 is not null group by 1, 2 order by n desc limit 15`,160 sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${counted})::int as n, sum(${known})::float as mw from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,161 ]),162 sql<Row[]>`select f.id, f.slug, f.name, o.name as op_name, f.lat, f.lng, f.status, f.facility_type, ${mwExpr(sql)} as mw, f.geo_precision, f.is_ai, f.ai_evidence, f.is_hyperscale, f.country_iso2, f.record_scope, ${yearExpr(sql, sql`f.opened_on`)} as y from facilities f ${facilityJoins(sql)} where ${where} and f.lat is not null and f.lng is not null order by ${mwExpr(sql)} desc nulls last, f.id limit ${MAX_POINTS + 1}`,163 sql<Row[]>`select count(*) filter (where ${counted})::int as n, count(*) filter (where ${counted} and ${hasMw})::int as with_mw, count(*)::int as rows from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where}`,164 ]);165 const [fStatus, fCountry, fOperator, fType, fAi] = facets;166 const [cStatus, cCountry, cYear] = charts;167 const degraded = pts.length > MAX_POINTS;168 const points: MapPoint[] = (degraded ? [] : pts).map((r) => {169 const p: MapPoint = { id: reqStr(r.id), slug: reqStr(r.slug), n: reqStr(r.name), o: str(r.op_name), lat: num(r.lat) ?? 0, lng: num(r.lng) ?? 0, s: asStatus(r.status), t: asType(r.facility_type), mw: num(r.mw), p: asPrecision(r.geo_precision), c: str(r.country_iso2) };170 const ai = str(r.ai_evidence);171 if (r.is_ai === true || ai === "confirmed" || ai === "likely") p.ai = 1;172 if (r.is_hyperscale === true) p.hs = 1;173 const y = num(r.y);174 if (y != null) p.y = y;175 const rs = str(r.record_scope);176 if (rs === "building" || rs === "campus") p.rs = rs;177 return p;178 });179 return {180 query: q,181 total: items.length ? int(items[0]!.total) : int(cov[0]?.rows),182 items: items.map(facilitySummary),183 facets: {184 status: fStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n) })),185 country: fCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n) })),186 operator: fOperator.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name), count: int(r.n) })),187 type: fType.map((r) => ({ key: reqStr(r.key), count: int(r.n) })),188 ai: fAi.map((r) => ({ key: reqStr(r.key, "unknown"), count: int(r.n) })),189 },190 charts: {191 byStatus: cStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n), mw: round2(num(r.mw)) })),192 byCountry: cCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n), mw: round2(num(r.mw)) })),193 byYear: cYear.map((r) => ({ year: int(r.year), count: int(r.n), mw: round2(num(r.mw)) })).filter((x) => x.year > 0),194 },195 map: { points, total: degraded ? int(cov[0]?.rows) : points.length, degraded },196 mwCoverage: share(int(cov[0]?.with_mw), int(cov[0]?.n)),197 };198}199