校园外卖多站点配送要同时定清三件事:地址节点归属哪个站点、订单走直送还是校门中转、跨站改派后由哪个站点结算和承担售后。落地时先统一“校区—片区—楼栋—取餐点”地址层级,再让系统只在站点候选唯一时自动归属;出现围栏重叠就进入异常队列。下面以一个常见故障为例,给出数据模型、可复现校验代码、正常与异常输出和验收方法。
故障:围栏重叠把一笔订单送进两个站点
以一个常见故障为例:某校区东站与西站围栏在宿舍交界处重叠,用户地址落在同一栋楼。订单按文本地址先匹配到东站,骑手端又因围栏命中西站出现可接任务;改派后履约站点变了,结算仍记原站,月底出现跨站补贴和售后责任争议。根因不是骑手,而是地址没有唯一ID、围栏没有候选集、改派没有审计日志。
如果只靠地址文本匹配,同一个“3号楼”在旧楼号、新楼号、东区或西区简称下会产生不同结果;围栏一旦重叠,骑手端会出现两个站点都能接的可接任务。订单表没有 settlement_site_id 时,财务只能用备注判断,退款和跨站补贴就会落在错误站点。
工程上把故障拆成三个约束:订单只能有一个当前履约站点;结算站点和售后站点可以不同,但必须显式记录;任何站点变更必须写日志。三条约束进入表结构和事务后,串单才有机会被系统拦截。
数据模型:站点、围栏、地址、订单、结算各管一件事
| 表 | 关键字段 | 字段约束与用途 |
|---|---|---|
| site | site_id、campus_id、code、name、priority、active | campus_id+code 唯一;active 取 0/1;priority 只作人工审核辅助 |
| site_fence | fence_id、site_id、polygon_json、active | 多边形至少三个点且首尾闭合;允许不同站点围栏重叠,用于识别异常 |
| address_node | address_id、campus_id、parent_id、node_type、canonical_name、x、y | node_type 取 campus/zone/building/pickup;parent_id 自关联;校区+父节点+名称唯一 |
| orders | order_id、address_id、site_id、settlement_site_id、after_sales_site_id、fulfillment_mode、cross_site、status | 三个站点字段均非空;fulfillment_mode 取 DIRECT/CROSS_GATE_RELAY;status 走状态机 |
| delivery_task | task_id、order_id、leg、site_id、assignee_id、status | leg 取 OUTSIDE/INSIDE;同一订单同一段保留一条任务记录 |
| order_site_log | order_id、from_site_id、to_site_id、from_settlement_site_id、to_settlement_site_id、reason、operator_id | reason 和 operator_id 不能为空;每次改派写入一行 |
| settlement_ledger | order_id、site_id、role、amount_cents、direction、status | role 取 merchant/outside_rider/inside_rider/platform;同一订单同角色同站点同方向唯一 |
| site_match_exception | exception_id、order_ref、campus_id、x、y、candidates_json、status | status 取 EXCEPTION_MULTI_SITE/NO_FENCE;保留候选站点和坐标 |
其中 orders.site_id 是履约站点,settlement_site_id 是实际结算站点,after_sales_site_id 是售后责任站点。三者分开后,跨站支援不再靠备注解释;订单、任务、账单和售后工单可以围绕同一个 order_id 对齐。地址节点用 address_id 而不是自由文本,订单只引用 address_id;直送只有 OUTSIDE 一段任务,校门中转拆成 OUTSIDE 与 INSIDE 两段。

微订校园配送站点收单与已收订单送达页面,按宿舍组织处理。
核心实现:唯一候选才自动下单,多候选写异常
下面脚本用 Python 3.10+ 标准库 sqlite3,围栏用 JSON 多边形保存,点面判断用射线法。生产环境可换成空间数据库函数,但“唯一候选才自动归属”的约束不变。脚本故意使用重叠围栏:正常点只命中一个站,异常点命中两个站;若把优先级直接拿来分流,系统会把边界订单静默分到某一站,账和售后仍可能错。这里把优先级保留为人工审核参考,自动归属只接受唯一候选。
运行步骤:把代码保存为 site_router.py;执行 python site_router.py;查看控制台输出。依赖仅 Python 3.10+ 标准库,不需要第三方包。
import json
import sqlite3
SCHEMA = '''
CREATE TABLE site (
site_id INTEGER PRIMARY KEY,
campus_id TEXT NOT NULL,
code TEXT NOT NULL,
name TEXT NOT NULL,
priority INTEGER NOT NULL DEFAULT 100,
active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
UNIQUE(campus_id, code)
);
CREATE TABLE site_fence (
fence_id INTEGER PRIMARY KEY,
site_id INTEGER NOT NULL REFERENCES site(site_id),
polygon_json TEXT NOT NULL,
active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1))
);
CREATE TABLE address_node (
address_id TEXT PRIMARY KEY,
campus_id TEXT NOT NULL,
parent_id TEXT REFERENCES address_node(address_id),
node_type TEXT NOT NULL CHECK(node_type IN ('campus','zone','building','pickup')),
canonical_name TEXT NOT NULL,
x REAL,
y REAL,
UNIQUE(campus_id, parent_id, canonical_name)
);
CREATE TABLE orders (
order_id TEXT PRIMARY KEY,
campus_id TEXT NOT NULL,
address_id TEXT NOT NULL REFERENCES address_node(address_id),
site_id INTEGER NOT NULL REFERENCES site(site_id),
settlement_site_id INTEGER NOT NULL REFERENCES site(site_id),
after_sales_site_id INTEGER NOT NULL REFERENCES site(site_id),
fulfillment_mode TEXT NOT NULL CHECK(fulfillment_mode IN ('DIRECT','CROSS_GATE_RELAY')),
cross_site INTEGER NOT NULL DEFAULT 0 CHECK(cross_site IN (0,1)),
status TEXT NOT NULL CHECK(status IN ('CREATED','ACCEPTED','AT_GATE','SORTED','DELIVERING','DONE','CANCELED'))
);
CREATE TABLE order_site_log (
log_id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id TEXT NOT NULL REFERENCES orders(order_id),
from_site_id INTEGER NOT NULL REFERENCES site(site_id),
to_site_id INTEGER NOT NULL REFERENCES site(site_id),
from_settlement_site_id INTEGER NOT NULL REFERENCES site(site_id),
to_settlement_site_id INTEGER NOT NULL REFERENCES site(site_id),
reason TEXT NOT NULL CHECK(length(trim(reason)) > 0),
operator_id TEXT NOT NULL CHECK(length(trim(operator_id)) > 0),
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE site_match_exception (
exception_id INTEGER PRIMARY KEY AUTOINCREMENT,
order_ref TEXT NOT NULL,
campus_id TEXT NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL,
candidates_json TEXT NOT NULL,
status TEXT NOT NULL CHECK(status IN ('EXCEPTION_MULTI_SITE','NO_FENCE')),
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
'''
def point_in_polygon(x, y, polygon):
inside = False
n = len(polygon)
for i in range(n):
ax, ay = polygon[i]
bx, by = polygon[(i + 1) % n]
if (ay > y) != (by > y):
xinters = (bx - ax) * (y - ay) / (by - ay) + ax
if xinters > x:
inside = not inside
return inside
def resolve_site(conn, campus_id, x, y):
rows = conn.execute(
'''SELECT f.site_id, s.code, s.priority, f.polygon_json
FROM site_fence f
JOIN site s ON s.site_id = f.site_id
WHERE s.campus_id = ? AND s.active = 1 AND f.active = 1''',
(campus_id,),
).fetchall()
candidates = []
for site_id, code, priority, polygon_json in rows:
if point_in_polygon(x, y, json.loads(polygon_json)):
candidates.append({
'site_id': site_id, 'site_code': code, 'priority': priority})
candidates.sort(key=lambda item: (item['priority'], item['site_id']))
if len(candidates) == 1:
return {
'status': 'AUTO', 'site_id': candidates[0]['site_id'], 'candidates': candidates}
if len(candidates) > 1:
return {
'status': 'EXCEPTION_MULTI_SITE', 'site_id': None, 'candidates': candidates}
return {
'status': 'NO_FENCE', 'site_id': None, 'candidates': []}
def create_order(conn, order_id, campus_id, address_id, x, y, mode):
route = resolve_site(conn, campus_id, x, y)
if route['status'] != 'AUTO':
with conn:
conn.execute(
'''INSERT INTO site_match_exception(order_ref,campus_id,x,y,candidates_json,status)
VALUES(?,?,?,?,?,?)''',
(order_id, campus_id, x, y, json.dumps(route['candidates'], ensure_ascii=False), route['status']),
)
return {
'case': 'exception', 'route': route, 'exception_written': True}
site_id = route['site_id']
with conn:
conn.execute(
'''INSERT INTO orders(order_id,campus_id,address_id,site_id,settlement_site_id,after_sales_site_id,fulfillment_mode,cross_site,status)
VALUES(?,?,?,?,?,?,?,?,?)''',
(order_id, campus_id, address_id, site_id, site_id, site_id, mode, 0, 'CREATED'),
)
return {
'case': 'normal', 'route': route, 'order_site_id': site_id, 'settlement_site_id': site_id, 'after_sales_site_id': site_id, 'cross_site': 0}
def reassign_order(conn, order_id, to_site_id, to_settlement_site_id, reason, operator_id):
row = conn.execute('SELECT site_id, settlement_site_id, after_sales_site_id FROM orders WHERE order_id = ?', (order_id,)).fetchone()
if row is None:
raise ValueError('order not found')
from_site_id, from_settlement_site_id, after_sales_site_id = row
with conn:
conn.execute(
'''UPDATE orders
SET site_id = ?, settlement_site_id = ?,
cross_site = CASE WHEN site_id != ? THEN 1 ELSE cross_site END
WHERE order_id = ?''',
(to_site_id, to_settlement_site_id, to_site_id, order_id),
)
conn.execute(
'''INSERT INTO order_site_log(order_id,from_site_id,to_site_id,from_settlement_site_id,to_settlement_site_id,reason,operator_id)
VALUES(?,?,?,?,?,?,?)''',
(order_id, from_site_id, to_site_id, from_settlement_site_id, to_settlement_site_id, reason, operator_id),
)
return {
'case': 'reassign', 'order_id': order_id, 'site_id': to_site_id, 'settlement_site_id': to_settlement_site_id, 'after_sales_site_id': after_sales_site_id, 'cross_site': 1 if to_site_id != from_site_id else 0, 'log_reason': reason}
def main():
conn = sqlite3.connect(':memory:')
conn.executescript(SCHEMA)
with conn:
conn.executemany('INSERT INTO site(site_id,campus_id,code,name,priority) VALUES(?,?,?,?,?)', [
(1, 'CAMPUS_A', 'EAST', '东区站', 20),
(2, 'CAMPUS_A', 'WEST', '西区站', 30),
])
conn.executemany('INSERT INTO site_fence(site_id,polygon_json) VALUES(?,?)', [
(1, json.dumps([[0, 0], [10, 0], [10, 10], [0, 10]], ensure_ascii=False)),
(2, json.dumps([[5, 5], [15, 5], [15, 15], [5, 15]], ensure_ascii=False)),
])
conn.executemany('INSERT INTO address_node(address_id,campus_id,parent_id,node_type,canonical_name,x,y) VALUES(?,?,?,?,?,?,?)', [
('CAMPUS_A', 'CAMPUS_A', None, 'campus', '某校区', None, None),
('CAMPUS_A-EAST', 'CAMPUS_A', 'CAMPUS_A', 'zone', '东区', None, None),
('CAMPUS_A-EAST-3', 'CAMPUS_A', 'CAMPUS_A-EAST', 'building', '3号楼', None, None),
('ADDR-EAST-201', 'CAMPUS_A', 'CAMPUS_A-EAST-3', 'pickup', '3号楼201', 2, 2),
('ADDR-BORDER-501', 'CAMPUS_A', 'CAMPUS_A-EAST-3', 'pickup', '3号楼501', 5, 5),
])
normal = create_order(conn, 'ORD-1001', 'CAMPUS_A', 'ADDR-EAST-201', 2, 2, 'DIRECT')
overlap = create_order(conn, 'ORD-1002', 'CAMPUS_A', 'ADDR-BORDER-501', 5, 5, 'CROSS_GATE_RELAY')
moved = reassign_order(conn, 'ORD-1001', 2, 2, '东站骑手不足,跨站支援', 'dispatcher-01')
print('case=normal')
print('status=' + normal['route']['status'])
print('order_site_id=' + str(normal['order_site_id']))
print('settlement_site_id=' + str(normal['settlement_site_id']))
print('case=overlap')
print('status=' + overlap['route']['status'])
print('candidate_sites=' + ','.join(str(item['site_id']) for item in overlap['route']['candidates']))
print('exception_written=' + str(overlap['exception_written']).lower())
print('case=reassign')
print('site_id=' + str(moved['site_id']))
print('settlement_site_id=' + str(moved['settlement_site_id']))
print('after_sales_site_id=' + str(moved['after_sales_site_id']))
print('cross_site=' + str(moved['cross_site']))
print('log_reason=' + moved['log_reason'])
if __name__ == '__main__':
main()
按脚本逻辑的预期输出如下(Python 3.10+,SQLite 内存库;上线前在测试库复核):
case=normal
status=AUTO
order_site_id=1
settlement_site_id=1
case=overlap
status=EXCEPTION_MULTI_SITE
candidate_sites=1,2
exception_written=true
case=reassign
site_id=2
settlement_site_id=2
after_sales_site_id=1
cross_site=1
log_reason=东站骑手不足,跨站支援
正常点 (2,2) 只命中东站,订单直接落东站;重叠点 (5,5) 同时命中东站和西站,系统不自动下单,而是写入 site_match_exception,等待人工定站。改派示例把履约站点和结算站点改为西站,售后站点保留东站,并写入改派原因和操作人。若业务需要缓冲区,可把缓冲区建成单独的候选层,但它同样只生成候选,不绕过唯一性约束。
业务验收:三类账、四类异常和上线顺序
系统能建表不等于现场能跑。验收时用同一批测试单核对订单、任务、账务和售后。三类账分别是商家货款、校外骑手和校内骑手收入、平台服务费,每一类都用 order_id+site_id 关联;四类异常是重复扫码、模糊码、跨站订单、部分退款,前两类影响站点收餐,后两类影响站点归属和账务。

用户支付经分账规则和区域节点分配至商家、骑手与平台的流程示意。
| 验收场景 | 输入 | 预期结果 | 业务动作 |
|---|---|---|---|
| 正常直送 | 东区3号楼201,坐标(2,2),DIRECT | AUTO,订单站点=结算站点=售后站点=东站 | 生成一条订单和一条任务 |
| 围栏重叠 | 3号楼501,坐标(5,5),CROSS_GATE_RELAY | EXCEPTION_MULTI_SITE,候选东站、西站 | 写入异常队列,人工定站后再下单 |
| 跨站改派 | ORD-1001 改到西站履约、西站结算 | site_id=2,settlement_site_id=2,after_sales_site_id=1,cross_site=1 | 写入改派日志和原因 |
| 部分退款 | 改派后发生部分退款 | 退款冲回 settlement_site_id,售后工单归 after_sales_site_id | 账单和工单同 order_id 可追溯 |
跨站支援单还可用一条审计 SQL 做日常检查:
-- 跨站支援单必须有改派日志,且结算站点变动要可解释
SELECT o.order_id, o.site_id, o.settlement_site_id, o.after_sales_site_id, o.cross_site, l.reason
FROM orders o
LEFT JOIN order_site_log l ON l.order_id = o.order_id
WHERE o.cross_site = 1
AND (o.settlement_site_id = o.site_id OR l.log_id IS NULL);
这条查询把“履约站点、结算站点、售后站点”和改派日志放在一张结果里。返回空集是目标状态;返回记录时逐单核对跨站补贴由谁承担、退款冲回哪个站点、售后由谁处理。
上线顺序建议:先统一地址树和楼栋别名,再配置围栏和唯一候选规则;随后把直送、校门中转拆成 OUTSIDE/INSIDE 任务,接入改派日志;最后跑正常单、部分退款单、结算后退款单。低预算阶段先做地址、收餐分拣、通知和权限,分账与定制按阶段加,价格按模块、终端、部署和交付分项确认。先小批测试单再放量,每次改派保留原站点、目标站点、结算变化和操作人。
常见问题
问:围栏重叠能否用优先级自动解决?答:优先级可作为人工审核辅助,自动归属建议只在候选唯一时放行;重叠订单进入异常队列,避免站点、结算、售后三处不一致。
问:直送和校门中转要不要两张订单表?答:共用 orders,用 fulfillment_mode 区分;配送任务按 OUTSIDE/INSIDE 拆分,用户仍看到一笔订单,站点和结算可分别追溯。
问:跨站支援后售后归谁?答:after_sales_site_id 记录原站点,settlement_site_id 记录实际结算站点,site_id 记录履约站点;三者不同时在日志和账单中写明。
作者:微订(上海逊柯计算机科技有限公司)。