120 lines
4.4 KiB
Markdown
120 lines
4.4 KiB
Markdown
# 数据库设计文档 (DATABASE.md)
|
||
|
||
本项目采用 **PostgreSQL (PostGIS)** 作为主数据库，**Redis** 作为多级缓存层。
|
||
|
||
---
|
||
|
||
## 1. PostgreSQL 数据库设计
|
||
|
||
### 1.1 扩展 (Extensions)
|
||
必须启用 PostGIS 扩展以支持地理空间数据存储与查询。
|
||
|
||
```sql
|
||
CREATE EXTENSION IF NOT EXISTS postgis;
|
||
```
|
||
|
||
### 1.2 表结构 (Tables)
|
||
|
||
#### 1.2.1 `peaks` (山峰表)
|
||
存储 14 座 8000 米级山峰的核心元数据。
|
||
|
||
```sql
|
||
CREATE TABLE peaks (
|
||
id SERIAL PRIMARY KEY,
|
||
name_en VARCHAR(100) NOT NULL UNIQUE, -- 英文名 (e.g., "Everest")
|
||
name_cn VARCHAR(100), -- 中文名
|
||
elevation_m INTEGER NOT NULL, -- 海拔 (米)
|
||
prominence_m INTEGER, -- 地形突起度
|
||
location GEOMETRY(POINT, 4326), -- 经纬度坐标 (WGS84)
|
||
first_ascent_year INTEGER, -- 首登年份
|
||
description TEXT, -- 简介
|
||
thumbnail_url VARCHAR(255), -- 缩略图 URL
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_peaks_location ON peaks USING GIST (location);
|
||
```
|
||
|
||
#### 1.2.2 `routes` (攀登路线表)
|
||
存储山峰的攀登路线，支持一条山峰多条路线。路线的几何数据量较大，通过 GeoJSON 或 PostGIS LineString 存储。
|
||
|
||
```sql
|
||
CREATE TABLE routes (
|
||
id SERIAL PRIMARY KEY,
|
||
peak_id INTEGER REFERENCES peaks(id) ON DELETE CASCADE,
|
||
name VARCHAR(100) NOT NULL, -- 路线名称 (e.g., "South Col Route")
|
||
difficulty VARCHAR(50), -- 难度等级
|
||
path_geometry GEOMETRY(LINESTRINGZ, 4326), -- 3D 路径 (包含海拔 Z)
|
||
description TEXT,
|
||
is_standard_route BOOLEAN DEFAULT FALSE, -- 是否为常规路线
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_routes_peak_id ON routes(peak_id);
|
||
CREATE INDEX idx_routes_geometry ON routes USING GIST (path_geometry);
|
||
```
|
||
|
||
#### 1.2.3 `camps` (营地表)
|
||
存储攀登路线上的关键营地位置 (Base Camp, C1, C2, etc.)。
|
||
|
||
```sql
|
||
CREATE TABLE camps (
|
||
id SERIAL PRIMARY KEY,
|
||
route_id INTEGER REFERENCES routes(id) ON DELETE CASCADE,
|
||
name VARCHAR(50) NOT NULL, -- 营地名 (e.g., "Camp 4")
|
||
elevation_m INTEGER, -- 营地海拔
|
||
location GEOMETRY(POINT, 4326), -- 营地坐标
|
||
camp_type VARCHAR(20) CHECK (camp_type IN ('BaseCamp', 'AdvancedBaseCamp', 'Camp', 'Summit')),
|
||
description TEXT
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_camps_route_id ON camps(route_id);
|
||
```
|
||
|
||
---
|
||
|
||
## 2. Redis 缓存策略 (Cache Strategy)
|
||
|
||
采用多层缓存结构以提升 API 响应速度和减轻数据库 GIS 计算压力。
|
||
|
||
### 2.1 键命名规范 (Key Naming Convention)
|
||
`{prefix}:{resource}:{identifier}:{field}`
|
||
|
||
### 2.2 缓存层级与 TTL
|
||
|
||
| 数据类型 | Redis Key 模式 | 数据结构 | TTL (过期时间) | 说明 |
|
||
| :--- | :--- | :--- | :--- | :--- |
|
||
| **API 响应** | `api:peaks:list` | String (JSON) | 24 Hours | 所有山峰列表的轻量级 JSON |
|
||
| **山峰详情** | `data:peak:{id}` | Hash | 7 Days | 单个山峰的详细信息 |
|
||
| **路线 GeoJSON** | `data:route:{id}:geojson` | String (GeoJSON) | 30 Days | 路线的完整几何数据 (体积大，极少变动) |
|
||
| **高程查询** | `cache:elev:{lat}_{lon}` | String | 30 Days | 特定坐标的高程缓存 (避免重复栅格计算) |
|
||
| **用户会话** | `session:{token}` | String | 2 Hours | 用户登录状态 (如有) |
|
||
|
||
### 2.3 缓存更新逻辑
|
||
1. **Read-Through:** 读取数据时，先查 Redis。命中则返回；未命中则查 PostgreSQL，写入 Redis 并返回。
|
||
2. **Write-Invalidate:** 更新数据库 (如修正路线数据) 时，立即删除对应的 Redis Key (e.g., `DEL data:route:123:geojson`)，下次读取时自动重建。
|
||
|
||
---
|
||
|
||
## 3. 示例查询 (Example Queries)
|
||
|
||
### 3.1 查找珠峰附近 50km 内的所有其他 8000 米山峰
|
||
```sql
|
||
SELECT name_en, elevation_m,
|
||
ST_Distance(location::geography, (SELECT location::geography FROM peaks WHERE name_en = 'Everest')) as dist_meters
|
||
FROM peaks
|
||
WHERE id != (SELECT id FROM peaks WHERE name_en = 'Everest')
|
||
AND ST_DWithin(location::geography, (SELECT location::geography FROM peaks WHERE name_en = 'Everest'), 50000);
|
||
```
|
||
|
||
### 3.2 获取某条路线的 3D 长度
|
||
```sql
|
||
SELECT name, ST_3DLength(path_geometry::geography) as length_meters
|
||
FROM routes
|
||
WHERE id = 1;
|
||
```
|