如何用SQLite的substr、instr、trim函数提取字符串中的经纬度坐标
经纬度提取实操方案
原有函数写法失效的核心原因
- 原始数据中的双引号是经过HTML转义的,被写成了
",直接用instr检索"latitude"会完全匹配不到位置,最终返回空值 - 没有提前把整段多JSON拼接的文本按换行符拆成单条JSON单元,直接在全量长文本上计算截取位置,会导致第一行之后的所有行定位错位
无内置JSON解析能力的纯字符串实现(适配你熟悉的substr/instr/trim组合)
这套逻辑完全兼容样例中字段不全的JSON行(比如缺altitude、heading字段的记录),不需要固定匹配JSON字段顺序:
- 先替换转义字符:将整段文本中所有
"替换为正常双引号 - 按换行符将整段文本拆成单条JSON记录,过滤空行
- 对单条JSON:
- 定位
"latitude":的起始位置,加上这个固定子串的长度,拿到纬度值的开始索引 - 从起始索引往后查找第一个逗号的位置,截取两个位置中间的内容,trim后转数值即为纬度值
- 用完全相同的逻辑定位
"longitude":,拿到经度值
- 定位
可直接套用的Oracle环境示例代码:
WITH raw_content AS ( -- 将你拿到的原始整段文本粘贴到这里 SELECT REPLACE('原始整段文本', '"', '"') AS full_text FROM dual ), single_json AS ( -- 按换行拆成单条JSON SELECT REGEXP_SUBSTR(full_text, '[^\r\n]+', 1, LEVEL) AS json_row FROM raw_content CONNECT BY REGEXP_SUBSTR(full_text, '[^\r\n]+', 1, LEVEL) IS NOT NULL ) SELECT TO_NUMBER(TRIM( SUBSTR(json_row, INSTR(json_row, '"latitude":') + LENGTH('"latitude":'), INSTR(json_row, ',', INSTR(json_row, '"latitude":')) - (INSTR(json_row, '"latitude":') + LENGTH('"latitude":')) ) )) AS latitude, TO_NUMBER(TRIM( SUBSTR(json_row, INSTR(json_row, '"longitude":') + LENGTH('"longitude":'), INSTR(json_row, ',', INSTR(json_row, '"longitude":')) - (INSTR(json_row, '"longitude":') + LENGTH('"longitude":')) ) )) AS longitude FROM single_json WHERE json_row LIKE '{%}';
带JSON解析能力环境的简化方案
纯字符串截取的维护成本高,有内置解析能力的环境可以直接用解析函数取值,出错概率更低:
- MySQL 5.7+写法:
SELECT JSON_EXTRACT(TRIM(json_row), '$.latitude') AS latitude, JSON_EXTRACT(TRIM(json_row), '$.longitude') AS longitude FROM ( SELECT REPLACE(REGEXP_SUBSTR(full_text, '[^\r\n]+', 1, n), '"', '"') AS json_row FROM raw_content -- 借助连续数字表完成换行拆分 JOIN nums_table ON n <= LENGTH(full_text) - LENGTH(REPLACE(full_text, '\n', '')) + 1 ) t WHERE json_row LIKE '{%}' - Python脚本写法:
import json with open("raw_gps.txt", "r", encoding="utf-8") as f: for line in f: line = line.strip().replace(""", '"') if not line.startswith("{"): continue gps = json.loads(line) print(f"纬度: {gps['latitude']}, 经度: {gps['longitude']}")
内容的提问来源于stack exchange,提问作者MAG
相关产品推荐
相关产品推荐

