把地震目录塞进SQLite再查，比在线接口顺手的一次实测

上周想在家里那台旧笔记本上做点小分析：看某个区域近十年的地震时间分布。在线接口查一次几百条还行，想按任意时间窗反复筛就难受了，网络一断思路也断。于是我把 USGS 的 fdsnws 目录一次性拉下来，落到本地 SQLite。

环境很普通：Ubuntu 22.04，Python 3.10，sqlite3 自带。范围选了环太平洋一个 8°×8° 的框，2014-01-01 到 2024-01-01，最小震级 2.5，大概七万多条，JSON 文件 40MB 出头。

拉数据这一步：

curl -G 'https://earthquake.usgs.gov/fdsnws/event/1/query' \
  --data-urlencode 'format=geojson' \
  --data-urlencode 'starttime=2014-01-01' \
  --data-urlencode 'endtime=2024-01-01' \
  --data-urlencode 'minmagnitude=2.5' \
  --data-urlencode 'minlatitude=...' \
  --data-urlencode 'maxlatitude=...' \
  --data-urlencode 'minlongitude=...' \
  --data-urlencode 'maxlongitude=...' \
  -o quakes.json

入库只留用得上的字段，经纬度单独存，时间统一转成 Unix 毫秒：

import json, sqlite3

con = sqlite3.connect('quakes.db')
con.execute('''CREATE TABLE IF NOT EXISTS q(
  id TEXT PRIMARY KEY, t INTEGER, mag REAL,
  lon REAL, lat REAL, depth REAL, place TEXT)''')
rows = json.load(open('quakes.json'))['features']
con.executemany(
  'INSERT OR IGNORE INTO q VALUES (?,?,?,?,?,?,?)',
  [(f['id'], f['properties']['time'], f['properties']['mag'],
    *f['geometry']['coordinates'][:2], f['geometry']['coordinates'][2],
    f['properties']['place']) for f in rows])
con.execute('CREATE INDEX IF NOT EXISTS idx_t ON q(t)')
con.commit()

之后按小时、按天聚合就是一句 SQL，几万条毫秒级返回：

SELECT t/86400000 AS day, COUNT(*) FROM q
WHERE mag >= 3.0 GROUP BY day ORDER BY day;

为什么值得这么做：在线接口每次查询都有往返和限流，反复试参数时成本很高；落到本地后，索引建在时间上，任意窗口都是顺序扫，试错几乎零成本。代价是数据会旧，需要定期重拉，而且 ID 去重只能挡住重复事件，挡不住上游修订震级。

会失效的地方我踩过两个。一是分页：单次请求有返回上限，框选太大时结果会被截断，我是按年切成十段分别拉的，拉完对总数才发现第一次少了一半。二是时间字段，别拿字符串直接比大小，统一转整数再存。另外 depth 偶尔是负值或 null，聚合前先过滤，不然均值会被带偏。

现在这个库还在我本地，偶尔改个 SQL 就能看不同震级阈值的日频次变化。你们拉目录时是分页拉还是按年切？有没有更省事的去重办法？