56 lines
2.8 KiB
PL/PgSQL
56 lines
2.8 KiB
PL/PgSQL
-- 82_osm_poi_ekb.sql
|
|
-- Context: Site Finder feature — анализ участка по близости к социальной
|
|
-- инфраструктуре. Хранение OSM POI для быстрых ST_DWithin запросов
|
|
-- в радиусе 1-3 км от анализируемого участка.
|
|
-- Dependencies: PostGIS extension (уже включён в prod)
|
|
-- Deploy order: standalone — нет зависимостей от других таблиц
|
|
-- Idempotent: да (IF NOT EXISTS / CREATE INDEX IF NOT EXISTS)
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS osm_poi_ekb (
|
|
poi_id BIGSERIAL PRIMARY KEY,
|
|
osm_id BIGINT NOT NULL,
|
|
osm_type TEXT NOT NULL,
|
|
-- 'school' | 'kindergarten' | 'pharmacy' | 'hospital' | 'shop_mall' |
|
|
-- 'shop_supermarket' | 'shop_small' | 'park' | 'tram_stop' |
|
|
-- 'bus_stop' | 'metro_stop'
|
|
category TEXT NOT NULL,
|
|
name TEXT,
|
|
lat NUMERIC(10, 7) NOT NULL,
|
|
lon NUMERIC(10, 7) NOT NULL,
|
|
geom GEOMETRY(Point, 4326) NOT NULL,
|
|
-- Дата последнего редактирования POI в OSM.
|
|
-- Для фильтра "данные не старше 2 лет" (требование Максима).
|
|
last_osm_edit_date DATE,
|
|
-- Остальные OSM теги для drill-down
|
|
tags JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (osm_type, osm_id, category)
|
|
);
|
|
|
|
-- GIST индекс для ST_DWithin / ST_Intersects запросов
|
|
CREATE INDEX IF NOT EXISTS osm_poi_ekb_geom_gist
|
|
ON osm_poi_ekb USING GIST (geom);
|
|
|
|
-- Индекс по категории для фильтрации по типу инфраструктуры
|
|
CREATE INDEX IF NOT EXISTS osm_poi_ekb_category_idx
|
|
ON osm_poi_ekb (category);
|
|
|
|
-- Индекс по дате редактирования для фильтра свежести данных
|
|
CREATE INDEX IF NOT EXISTS osm_poi_ekb_last_edit_idx
|
|
ON osm_poi_ekb (last_osm_edit_date);
|
|
|
|
COMMENT ON TABLE osm_poi_ekb IS
|
|
'Социальная инфраструктура Екатеринбурга из OSM. Источник: Overpass API. '
|
|
'Используется для site-finder scoring (близость школ, садов, магазинов, ст. транспорта).';
|
|
|
|
COMMENT ON COLUMN osm_poi_ekb.category IS
|
|
'Нормализованная категория. Маппинг OSM-тегов → category: '
|
|
'см. backend/app/services/site_finder/poi_loader.py';
|
|
|
|
COMMENT ON COLUMN osm_poi_ekb.last_osm_edit_date IS
|
|
'Дата последнего редактирования POI в OSM. '
|
|
'Для фильтра "данные не старше 2 лет" (требование Максима).';
|
|
|
|
COMMIT;
|