Spaces:
Running
Running
File size: 3,879 Bytes
1dc52fb | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | -- ============================================================================
-- Google Luma Cache Schema
-- Run this SQL in Supabase Dashboard → SQL Editor → New Query → Run
-- ============================================================================
-- Base road network graph registry
-- Stores metadata about cached OSMnx graphs; actual .graphml.gz lives in Storage
CREATE TABLE IF NOT EXISTS region_graphs (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
center_lat FLOAT NOT NULL,
center_lon FLOAT NOT NULL,
radius_km INT NOT NULL,
node_count INT,
edge_count INT,
storage_path TEXT NOT NULL,
file_size_bytes BIGINT DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '30 days'),
UNIQUE(center_lat, center_lon, radius_km)
);
-- Static features cache (lighting, crime, POI, vegetation — excludes weather/time)
-- Links to a region_graph; actual .parquet.gz lives in Storage
CREATE TABLE IF NOT EXISTS cached_features (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
graph_id UUID REFERENCES region_graphs(id) ON DELETE CASCADE,
storage_path TEXT NOT NULL,
edge_count INT,
created_at TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '24 hours'),
UNIQUE(graph_id)
);
-- Computed route cache (keyed by origin/dest/mode/time/weather)
CREATE TABLE IF NOT EXISTS route_cache (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
origin_lat FLOAT NOT NULL,
origin_lon FLOAT NOT NULL,
dest_lat FLOAT NOT NULL,
dest_lon FLOAT NOT NULL,
mode TEXT NOT NULL,
time_context TEXT NOT NULL, -- 'day' or 'night'
weather_bucket TEXT NOT NULL DEFAULT 'clear',
route_geometry JSONB NOT NULL,
estimated_time_seconds FLOAT,
average_safety_score FLOAT,
total_cost FLOAT,
graph_id UUID REFERENCES region_graphs(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '1 hour')
);
CREATE INDEX IF NOT EXISTS idx_route_lookup
ON route_cache(origin_lat, origin_lon, dest_lat, dest_lon, mode, time_context);
-- POI cache (OSM Overpass results per bounding box)
CREATE TABLE IF NOT EXISTS poi_cache (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
bbox_key TEXT UNIQUE NOT NULL,
poi_count INT,
poi_data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '7 days')
);
-- VIIRS pre-sampled brightness tile registry
CREATE TABLE IF NOT EXISTS viirs_tiles (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
city_name TEXT UNIQUE NOT NULL,
center_lat FLOAT,
center_lon FLOAT,
bbox_north FLOAT,
bbox_south FLOAT,
bbox_east FLOAT,
bbox_west FLOAT,
grid_resolution_m INT DEFAULT 50,
storage_path TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ML model registry (XGBoost models per region)
CREATE TABLE IF NOT EXISTS ml_models (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
region_key TEXT NOT NULL,
model_type TEXT DEFAULT 'xgboost',
storage_path TEXT NOT NULL,
training_edges INT,
feature_importance JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(region_key, model_type)
);
-- KDE model registry (crime density models)
CREATE TABLE IF NOT EXISTS kde_models (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
data_hash TEXT UNIQUE NOT NULL,
bandwidth FLOAT,
point_count INT,
storage_path TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Enable Row Level Security (optional — service_role bypasses RLS)
-- ALTER TABLE region_graphs ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE cached_features ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE route_cache ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE poi_cache ENABLE ROW LEVEL SECURITY;
|