485f489bd2
- SQL migration 101_user_custom_pois.sql: table with GEOGRAPHY(POINT) + GIST index - Pydantic schemas: CustomPoiCreate / CustomPoiUpdate / CustomPoiOut - Service custom_pois.py: CRUD + get_overlaps_for_scoring (1km radius, psycopg v3 CAST) - API /api/v1/custom-pois: POST(201)/GET/PATCH/DELETE, X-Session-Id header auth - Scoring in analyze_parcel: custom POI block after OSM POI loop, decay weight * max(0, 1 - dist/1000), custom_poi_score_items in response - Tests: CRUD + validation (weight/lon/lat 422) + scoring integration (mock-based) - Vault: Schema_User_Custom_Pois.md + Endpoint_Parcel_Analyze v3.9 section
34 lines
1.5 KiB
PL/PgSQL
34 lines
1.5 KiB
PL/PgSQL
-- #254: User custom POIs — точки с произвольным весом для ad-hoc scoring.
|
||
-- Юзер добавляет точку на карту (name, weight, lon, lat), scoring учитывает.
|
||
-- parcel_cad NULL = глобальная per-user точка (видна для всех анализов пользователя).
|
||
|
||
BEGIN;
|
||
|
||
CREATE TABLE IF NOT EXISTS user_custom_pois (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
parcel_cad TEXT, -- NULL = global per-user
|
||
name TEXT NOT NULL,
|
||
category TEXT,
|
||
weight REAL NOT NULL CHECK (weight BETWEEN -5 AND 5),
|
||
lon DOUBLE PRECISION NOT NULL CHECK (lon BETWEEN -180 AND 180),
|
||
lat DOUBLE PRECISION NOT NULL CHECK (lat BETWEEN -90 AND 90),
|
||
geom GEOGRAPHY(POINT, 4326) GENERATED ALWAYS AS (
|
||
ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography
|
||
) STORED,
|
||
notes TEXT,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE user_custom_pois IS
|
||
'User-defined ad-hoc POI points with custom scoring weight (#254). '
|
||
'parcel_cad=NULL means global (applies to all parcel analyses for user_id).';
|
||
|
||
CREATE INDEX IF NOT EXISTS user_custom_pois_geom_gist
|
||
ON user_custom_pois USING GIST (geom);
|
||
|
||
CREATE INDEX IF NOT EXISTS user_custom_pois_user_parcel
|
||
ON user_custom_pois (user_id, parcel_cad);
|
||
|
||
COMMIT;
|