root
59 lines
2.1 KiB
SQL
59 lines
2.1 KiB
SQL
-- Таблица для хранения сырых документов
|
|
CREATE TABLE IF NOT EXISTS raw_documents (
|
|
id SERIAL PRIMARY KEY,
|
|
filename VARCHAR(255) NOT NULL,
|
|
raw_text TEXT NOT NULL,
|
|
extracted_json JSONB,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Таблица для хранения структурированных данных о случаях
|
|
CREATE TABLE IF NOT EXISTS cases (
|
|
id SERIAL PRIMARY KEY,
|
|
raw_document_id INTEGER REFERENCES raw_documents(id),
|
|
|
|
-- Данные о пропавшем
|
|
age INTEGER,
|
|
gender VARCHAR(50),
|
|
has_diagnosis BOOLEAN,
|
|
diagnosis_type VARCHAR(255),
|
|
has_transport BOOLEAN,
|
|
|
|
-- Условия
|
|
season VARCHAR(50),
|
|
time_of_day VARCHAR(50),
|
|
elapsed_before_report_h FLOAT,
|
|
|
|
-- Местоположение
|
|
last_seen_direction VARCHAR(100),
|
|
terrain_primary VARCHAR(100),
|
|
water_nearby BOOLEAN,
|
|
road_nearby BOOLEAN,
|
|
|
|
-- Результаты поиска
|
|
found_alive BOOLEAN,
|
|
found_distance_km FLOAT,
|
|
found_direction VARCHAR(100),
|
|
found_location_type VARCHAR(255),
|
|
search_duration_hours FLOAT,
|
|
who_found VARCHAR(255),
|
|
|
|
-- Метаданные
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Индексы для ускорения запросов
|
|
CREATE INDEX IF NOT EXISTS idx_cases_age ON cases(age);
|
|
CREATE INDEX IF NOT EXISTS idx_cases_season ON cases(season);
|
|
CREATE INDEX IF NOT EXISTS idx_cases_terrain ON cases(terrain_primary);
|
|
CREATE INDEX IF NOT EXISTS idx_cases_found_alive ON cases(found_alive);
|
|
CREATE INDEX IF NOT EXISTS idx_cases_found_distance ON cases(found_distance_km);
|
|
|
|
-- Индекс для JSONB поиска
|
|
CREATE INDEX IF NOT EXISTS idx_raw_documents_json ON raw_documents USING gin(extracted_json);
|
|
|
|
COMMENT ON TABLE raw_documents IS 'Сырые документы спецдонесений МЧС';
|
|
COMMENT ON TABLE cases IS 'Структурированные данные о случаях пропажи людей';
|