Как спроектировать бд с парковочными зонами в Москве с нуля?

Как спроектировать БД с парковочными зонами в Москве с нуля?

Дизайн базы данных для системы паркировки требует учёта географических данных, real-time обновлений, аналитики и масштабируемости. Это типичный пример для geo-spatial приложения.

1. Анализ требований

Функциональные требования:

• Хранение границ парковочных зон (полигоны)
• Отслеживание доступных мест (real-time)
• История паркирования (аналитика)
• Тарифы и правила для каждой зоны
• Поиск ближайшей свободной зоны по GPS

Нефункциональные требования:

• Высокая доступность (99.9%+)
• Latency < 500ms для geo-queries
• Масштабируемость на миллионы парковочных мест
• Аналитика в real-time

2. Схема базы данных (PostgreSQL + PostGIS)

-- Создаём расширение для работы с географией
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- для полнотекстового поиска

-- Таблица парковочных зон
CREATE TABLE parking_zones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    zone_name VARCHAR(255) NOT NULL,
    zone_code VARCHAR(50) UNIQUE NOT NULL,
    polygon GEOMETRY(POLYGON, 4326) NOT NULL,  -- WGS84
    district VARCHAR(100),  -- СВАО, ЮЗАО, etc.
    zone_type VARCHAR(50),  -- 'standard', 'residents', 'commercial'
    
    -- Мощность
    total_spaces INT NOT NULL,
    reserved_spaces INT DEFAULT 0,  -- для инвалидов, жителей
    
    -- Тарифы (рубли за час)
    rate_per_hour DECIMAL(10, 2),
    max_daily_rate DECIMAL(10, 2),
    
    -- Режим работы
    work_hours_start TIME,
    work_hours_end TIME,
    week_days VARCHAR(100),  -- 'Mon,Tue,Wed,Thu,Fri,Sat,Sun'
    
    -- Ограничения
    max_parking_duration_hours INT,
    free_parking_for_residents BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_parking_zones_polygon ON parking_zones USING GIST(polygon);
CREATE INDEX idx_parking_zones_district ON parking_zones(district);
CREATE INDEX idx_parking_zones_type ON parking_zones(zone_type);

-- Таблица парковочных мест
CREATE TABLE parking_spaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    zone_id UUID NOT NULL REFERENCES parking_zones(id) ON DELETE CASCADE,
    space_number VARCHAR(50) NOT NULL,
    position GEOMETRY(POINT, 4326) NOT NULL,  -- GPS координаты
    
    -- Статус
    is_occupied BOOLEAN DEFAULT FALSE,
    is_available BOOLEAN DEFAULT TRUE,
    is_disabled BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(zone_id, space_number)
);

CREATE INDEX idx_parking_spaces_zone ON parking_spaces(zone_id);
CREATE INDEX idx_parking_spaces_position ON parking_spaces USING GIST(position);
CREATE INDEX idx_parking_spaces_occupied ON parking_spaces(is_occupied, zone_id);

-- Таблица сессий паркирования
CREATE TABLE parking_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    space_id UUID NOT NULL REFERENCES parking_spaces(id),
    zone_id UUID NOT NULL REFERENCES parking_zones(id),
    
    -- Время
    start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP WITH TIME ZONE,
    
    -- Платёж
    expected_cost DECIMAL(10, 2),
    actual_cost DECIMAL(10, 2),
    paid BOOLEAN DEFAULT FALSE,
    
    -- Место входа/выхода
    entry_point GEOMETRY(POINT, 4326),
    exit_point GEOMETRY(POINT, 4326),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_parking_sessions_user ON parking_sessions(user_id);
CREATE INDEX idx_parking_sessions_zone ON parking_sessions(zone_id);
CREATE INDEX idx_parking_sessions_start_time ON parking_sessions(start_time DESC);
CREATE INDEX idx_parking_sessions_status ON parking_sessions(end_time) WHERE end_time IS NULL;  -- активные

-- Таблица для аналитики (денормализованная)
CREATE TABLE parking_stats_hourly (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    zone_id UUID NOT NULL REFERENCES parking_zones(id),
    hour_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Метрики
    occupied_count INT,
    available_count INT,
    occupancy_rate DECIMAL(5, 2),  -- процент
    avg_stay_duration_minutes INT,
    revenue DECIMAL(15, 2),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(zone_id, hour_timestamp)
);

CREATE INDEX idx_parking_stats_zone_time ON parking_stats_hourly(zone_id, hour_timestamp DESC);

3. Оптимизация для geo-queries

-- Поиск ближайшей свободной парковки по GPS
PREPARE find_nearest_parking AS
SELECT 
    z.id,
    z.zone_name,
    z.rate_per_hour,
    COUNT(CASE WHEN ps.is_occupied = FALSE THEN 1 END) as available_spaces,
    ST_Distance(
        z.polygon::geography,
        ST_PointFromText('POINT($1 $2)', 4326)::geography
    ) / 1000 as distance_km
FROM parking_zones z
LEFT JOIN parking_spaces ps ON z.id = ps.zone_id
WHERE ST_DWithin(
    z.polygon::geography,
    ST_PointFromText('POINT($1 $2)', 4326)::geography,
    5000  -- 5км радиус
)
AND COUNT(CASE WHEN ps.is_occupied = FALSE THEN 1 END) > 0
GROUP BY z.id
ORDER BY distance_km
LIMIT 10;

-- Поиск зон по полигону (пользователь паркуется где-то, найти его зону)
PREPARE find_zone_by_location AS
SELECT z.*, 
    ST_Area(z.polygon::geography) / 10000 as area_hectares
FROM parking_zones z
WHERE ST_Contains(z.polygon, ST_PointFromText('POINT($1 $2)', 4326));

4. Python-скрипт для работы с БД

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session
import geopandas as gpd
from shapely.geometry import Point, Polygon
import json

class ParkingDatabase:
    def __init__(self, connection_string):
        self.engine = create_engine(connection_string)
    
    def find_nearest_zones(self, latitude, longitude, radius_km=5):
        """Найти ближайшие свободные зоны"""
        query = text("""
        SELECT 
            z.id,
            z.zone_name,
            z.rate_per_hour,
            COUNT(CASE WHEN ps.is_occupied = FALSE THEN 1 END) as available_spaces,
            ST_Distance(
                z.polygon::geography,
                ST_PointFromText('POINT(:lon :lat)', 4326)::geography
            ) / 1000 as distance_km
        FROM parking_zones z
        LEFT JOIN parking_spaces ps ON z.id = ps.zone_id
        WHERE ST_DWithin(
            z.polygon::geography,
            ST_PointFromText('POINT(:lon :lat)', 4326)::geography,
            :radius
        )
        GROUP BY z.id
        HAVING COUNT(CASE WHEN ps.is_occupied = FALSE THEN 1 END) > 0
        ORDER BY distance_km
        LIMIT 10
        """)
        
        with self.engine.connect() as conn:
            results = conn.execute(query, {
                'lat': latitude,
                'lon': longitude,
                'radius': radius_km * 1000
            })
            return [dict(row) for row in results]
    
    def reserve_space(self, user_id, space_id):
        """Зарезервировать парковочное место"""
        query = text("""
        INSERT INTO parking_sessions (user_id, space_id, zone_id, start_time)
        SELECT :user_id, :space_id, ps.zone_id, CURRENT_TIMESTAMP
        FROM parking_spaces ps
        WHERE ps.id = :space_id AND ps.is_occupied = FALSE
        RETURNING id
        """)
        
        with self.engine.begin() as conn:
            result = conn.execute(query, {
                'user_id': user_id,
                'space_id': space_id
            })
            return result.scalar()
    
    def get_occupancy_rate(self, zone_id, hours=24):
        """Получить % занятости за последние N часов"""
        query = text("""
        SELECT 
            hour_timestamp,
            occupancy_rate,
            available_count
        FROM parking_stats_hourly
        WHERE zone_id = :zone_id
            AND hour_timestamp >= CURRENT_TIMESTAMP - INTERVAL ':hours hours'
        ORDER BY hour_timestamp DESC
        """)
        
        with self.engine.connect() as conn:
            results = conn.execute(query, {
                'zone_id': zone_id,
                'hours': hours
            })
            return [dict(row) for row in results]

# Использование
db = ParkingDatabase("postgresql://user:pass@localhost/parking_db")

# Найти свободные парковки
zones = db.find_nearest_zones(latitude=55.7558, longitude=37.6173)  # Красная площадь
for zone in zones:
    print(f"{zone['zone_name']}: {zone['available_spaces']} мест на расстоянии {zone['distance_km']:.2f}км")

# Процент занятости
stats = db.get_occupancy_rate(zone_id="uuid-here", hours=24)

5. Структура для аналитики и отчётов

-- Дневной отчёт по выручке по зонам
CREATE VIEW daily_revenue_by_zone AS
SELECT 
    z.zone_name,
    z.district,
    DATE(ps.start_time) as date,
    COUNT(DISTINCT ps.id) as sessions,
    SUM(ps.actual_cost) as total_revenue,
    AVG(ps.actual_cost) as avg_revenue,
    AVG(EXTRACT(EPOCH FROM (ps.end_time - ps.start_time)) / 3600) as avg_duration_hours
FROM parking_sessions ps
JOIN parking_zones z ON ps.zone_id = z.id
WHERE ps.paid = TRUE
GROUP BY z.zone_name, z.district, DATE(ps.start_time)
ORDER BY date DESC;

-- Пиковые часы по зонам
CREATE VIEW peak_hours_by_zone AS
SELECT 
    zone_id,
    DATE_TRUNC('hour', start_time)::TIME as hour,
    COUNT(*) as session_count,
    AVG(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) as avg_duration
FROM parking_sessions
GROUP BY zone_id, DATE_TRUNC('hour', start_time)
ORDER BY session_count DESC;

6. Масштабирование

• Реплика для чтения: часто запрашиваемые geo-данные
• Шардинг по районам: разные БД для разных округов Москвы
• Redis кеш: часто используемые зоны
• TimescaleDB: для временных рядов аналитики
• Elasticsearch: для поиска по названиям зон

Эта архитектура обеспечивает масштабируемость, точность и эффективность работы с паркировочной системой.