spb/datacenterindex

1/**2 * Shared "what is around this point" helper (facilities, projects, IXPs, cloud regions, metros) — haversine on a bbox3 * pre-filter, no PostGIS. Layers without a connector (cable landing stations, substations, power plants) are returned4 * EMPTY with a note: nothing is ever faked.5 */6import type { NearbyInfrastructure } from "@dci/core";7import { pg, facilityJoins, facilitySummaryCols, projectJoins, projectSummaryCols, cloudRegionCols, haversineFor, withinBbox, projectLive, type Fragment } from "./sql.js";8import { num, reqStr, round, type Row } from "./rows.js";9import { cloudRegionSummary, facilitySummary, ixpSummary, projectSummary } from "./dto.js";1011export const NEARBY_TYPES = ["facilities", "projects", "ixps", "cloud_regions", "metros"] as const;12export type NearbyType = (typeof NEARBY_TYPES)[number];13export const NEARBY_MAX_RADIUS_KM = 200;14export const NEARBY_NOTE = "Distances are great-circle (haversine) from the centre; city-level coordinates are approximate (see geoPrecision). IXPs without published coordinates are placed at their metro reference point (lat/lng from the metro, flagged in `note`). Cable landing stations, substations and power plants are empty until a licensed connector for those layers exists — they are never inferred.";1516export interface NearbyOptions {17  lat: number;18  lng: number;19  radiusKm?: number;20  types?: NearbyType[];21  /** exclude the entity the query is made for */22  excludeFacilityId?: string | null;23  excludeProjectId?: string | null;24  limitPerType?: number;25}2627export async function nearby(o: NearbyOptions): Promise<NearbyInfrastructure> {28  const sql = pg();29  const radiusKm = Math.min(NEARBY_MAX_RADIUS_KM, Math.max(0.1, o.radiusKm ?? 25));30  const types = new Set<NearbyType>(o.types?.length ? o.types : NEARBY_TYPES);31  const limit = Math.min(500, Math.max(1, o.limitPerType ?? 50));32  const { lat, lng } = o;33  const fDist = haversineFor(sql, lat, lng, sql`f.lat`, sql`f.lng`);34  const pDist = haversineFor(sql, lat, lng, sql`p.lat`, sql`p.lng`);35  const rDist = haversineFor(sql, lat, lng, sql`r.lat`, sql`r.lng`);36  const mDist = haversineFor(sql, lat, lng, sql`m.lat`, sql`m.lng`);37  const xLat: Fragment = sql`coalesce(xm.lat, xm2.lat)`;38  const xLng: Fragment = sql`coalesce(xm.lng, xm2.lng)`;39  const xDist = haversineFor(sql, lat, lng, xLat, xLng);40  const none = Promise.resolve([] as Row[]);41  const [facs, prjs, ixps, crs, mets] = await Promise.all([42    types.has("facilities")43      ? sql<Row[]>`select ${facilitySummaryCols(sql)}, ${fDist} as distance_km from facilities f ${facilityJoins(sql)}44          where f.merged_into is null and f.lat is not null and f.lng is not null ${o.excludeFacilityId ? sql`and f.id <> ${o.excludeFacilityId}` : sql``}45            and ${withinBbox(sql, lat, lng, radiusKm, sql`f.lat`, sql`f.lng`)} and ${fDist} <= ${radiusKm}46          order by ${fDist} asc limit ${limit}`47      : none,48    types.has("projects")49      ? sql<Row[]>`select ${projectSummaryCols(sql)}, ${pDist} as distance_km from projects p ${projectJoins(sql)}50          where ${projectLive(sql)} and p.lat is not null and p.lng is not null ${o.excludeProjectId ? sql`and p.id <> ${o.excludeProjectId}` : sql``}51            and ${withinBbox(sql, lat, lng, radiusKm, sql`p.lat`, sql`p.lng`)} and ${pDist} <= ${radiusKm}52          order by ${pDist} asc limit ${limit}`53      : none,54    types.has("ixps")55      ? sql<Row[]>`select x.id, x.slug, x.name, x.name_long, x.city, x.country_iso2, x.website, x.network_count,56            (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count,57            ${xLat} as lat, ${xLng} as lng, ${xDist} as distance_km, (xm.id is not null) as via_metro,58            coalesce(xm.id, xm2.id) as met_id, coalesce(xm.slug, xm2.slug) as met_slug, coalesce(xm.name, xm2.name) as met_name59          from ixps x60          left join metros xm on xm.id = x.metro_id61          left join lateral (select m2.id, m2.slug, m2.name, m2.lat, m2.lng from metros m2 where x.metro_id is null and x.country_iso2 = m2.country_iso2 and x.city is not null and (lower(m2.name) = lower(x.city) or exists (select 1 from unnest(m2.aliases) a where lower(a) = lower(x.city))) limit 1) xm2 on true62          where ${xLat} is not null and ${withinBbox(sql, lat, lng, radiusKm, xLat, xLng)} and ${xDist} <= ${radiusKm}63          order by ${xDist} asc limit ${limit}`64      : none,65    types.has("cloud_regions")66      ? sql<Row[]>`select ${cloudRegionCols(sql)}, ${rDist} as distance_km from cloud_regions r join operators pr on pr.id = r.provider_id67          where r.lat is not null and r.lng is not null and ${withinBbox(sql, lat, lng, radiusKm, sql`r.lat`, sql`r.lng`)} and ${rDist} <= ${radiusKm}68          order by ${rDist} asc limit ${limit}`69      : none,70    types.has("metros")71      ? sql<Row[]>`select m.id, m.slug, m.name, m.country_iso2, ${mDist} as distance_km from metros m72          where ${withinBbox(sql, lat, lng, radiusKm, sql`m.lat`, sql`m.lng`)} and ${mDist} <= ${radiusKm}73          order by ${mDist} asc limit ${limit}`74      : none,75  ]);76  const d = (r: Row) => round(num(r.distance_km), 2) ?? 0;77  const ixpViaMetro = ixps.filter((r) => r.via_metro === true).length;78  return {79    center: { lat, lng },80    radiusKm,81    facilities: facs.map((r) => ({ ...facilitySummary(r), distanceKm: d(r) })),82    projects: prjs.map((r) => ({ ...projectSummary(r), distanceKm: d(r) })),83    ixps: ixps.map((r) => ({ ...ixpSummary(r), lat: num(r.lat), lng: num(r.lng), distanceKm: d(r) })),84    cloudRegions: crs.map((r) => ({ ...cloudRegionSummary(r), distanceKm: d(r) })),85    metros: mets.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), countryIso2: reqStr(r.country_iso2), distanceKm: d(r) })),86    landingStations: [],87    substations: [],88    powerPlants: [],89    note: `${NEARBY_NOTE}${ixpViaMetro ? ` ${ixpViaMetro} IXP${ixpViaMetro > 1 ? "s" : ""} placed at metro coordinates (no published IXP location).` : ""}`,90  };91}9293export function parseNearbyTypes(v: string | undefined): NearbyType[] | undefined {94  if (!v) return undefined;95  const out = v.split(",").map((s) => s.trim().toLowerCase().replace(/-/g, "_")).filter((s): s is NearbyType => (NEARBY_TYPES as readonly string[]).includes(s));96  return out.length ? out : undefined;97}98