使用hstore实现OpenStreetMap瓦片汉化_sql

概述关注并把玩OpenStreetMap也有一段时间了，准确说，快10年了吧。仅仅是把玩而已，从来没有认真学习过里面的数据结构与完整的技术工具链。就着2018年元旦的契机，我的ArchLinux Openstreetmap服务器要导入近期的数据，再一次遇到了汉化的问题。这一次，不准备用过时的地名大字典来匹配了。本来尝试了几个著名的web翻译引擎，不过发现现有的翻译引擎，对万国地名文字的适配性真的一般—

关注并把玩OpenStreetMap也有一段时间了，准确说，快10年了吧。仅仅是把玩而已，从来没有认真学习过里面的数据结构与完整的技术工具链。就着2018年元旦的契机，我的Archlinux Openstreetmap服务器要导入近期的数据，再一次遇到了汉化的问题。这一次，不准备用过时的地名大字典来匹配了。本来尝试了几个著名的web翻译引擎，不过发现现有的翻译引擎，对万国地名文字的适配性真的一般——最后的选择，就是考察一下OpenStreetMap官方的国际化选项：name：字段。

0.name字段

在OpenStreetMap官网，使用“这是哪里”的功能，可以看到一些地标的多国语言表述，如下图：

OpenStreetMap国际化支持见：国际化支持的具体内容。
大约在2013年以前，使用官方的 name:local 选项进行汉化的地标数量非常少，主要就是一些关键的地标。2018年，再次使用hstore选项导入多国地名，惊喜的发现经过这么多年的发展，地名的国际化程度已经有了显著提高，特此记录。

1. 导入数据

为了在数据库中保留各种语言的地名，–hstore选项是必须的。

osm2pgsql -c -s --unlogged -S"/home/archosm/osmstyle/openstreetmap-carto/openstreetmap-carto.style" -C24000 -dgis --drop --hstore --flat-nodes "./flat_node" /path/to/planet.pbf

主要关键选项：
–hstore 导入Tags列，这列里面用哈希键值存储了额外的信息，包括name:fr等字段。
-c 删除原有数据表，创建新表
-s slim模式，在内存少于64GB时必须选择这个模式。
–unlogged 不采用日志系统，直接导入。能够显著提高导入速度，并且对一次性导入来说，不存在数据不完整的风险。断电了？重来一次即可。
-C24000 缓存大小，设置为物理内存50%。我的物理内存48GB，给了24GB。给的太大，会发生swap，得不偿失。
–drop 导入完毕后，删除中间表。这将继续节省大量时间与空间。由于我们不做实时热同步，那些用于增量导入的表（nodes等）都不需要。
–flat-nodes “./flat_node” 用一个二进制文件而非数据库来缓存节点。这进一步提高了速度。把这个缓存与数据库表空间设置在两块SSD上，可以最大程度的分散IO压力。

采用这些策略后，花了20小时，便导入了2017年12月18日的完整镜像（39GB PBF）。

2. 汉化数据（1）为4个表格建立备份字段

由于汉化会修改name字段，我们要建立两个字段用于存放备份信息。即存放纯中文信息的字段trans_name_chs,存放原始信息的字段raw_name.

ALTER table public.planet_osm_line ADD ColUMN trans_name_chs character varying(255);    ALTER table public.planet_osm_point ADD ColUMN trans_name_chs character varying(255);    ALTER table public.planet_osm_polygon ADD ColUMN trans_name_chs character varying(255);    ALTER table public.planet_osm_roads ADD ColUMN trans_name_chs character varying(255);    ALTER table public.planet_osm_line ADD ColUMN raw_name character varying(255);    ALTER table public.planet_osm_point ADD ColUMN raw_name character varying(255);    ALTER table public.planet_osm_polygon ADD ColUMN raw_name character varying(255);    ALTER table public.planet_osm_roads ADD ColUMN raw_name character varying(255);

（2）使用hstore中的中文汉化

汉化的原理就是把含有中文标记的记录“name”字段更新为中文+原文的形式。

update planet_osm_line set name = name ||' (' || (Tags->'name:zh' ) || ')',raw_name = name,trans_name_chs = (Tags->'name:zh' ) where Tags->'name:zh' is not null and Tags->'name:zh'!= name and raw_name is null and trans_name_chs is null;    update planet_osm_point set name = name ||' (' || (Tags->'name:zh' ) || ')',trans_name_chs = (Tags->'name:zh' ) where Tags->'name:zh' is not null and Tags->'name:zh'!= name and raw_name is null and trans_name_chs is null;    update planet_osm_polygon set name = name ||' (' || (Tags->'name:zh' ) || ')',trans_name_chs = (Tags->'name:zh' ) where Tags->'name:zh' is not null and Tags->'name:zh'!= name and raw_name is null and trans_name_chs is null;    update planet_osm_roads set name = name ||' (' || (Tags->'name:zh' ) || ')',trans_name_chs = (Tags->'name:zh' ) where Tags->'name:zh' is not null and Tags->'name:zh'!= name and raw_name is null and trans_name_chs is null;

（3）建立索引

为了后续使用字典进行进一步汉化，创建索引。

CREATE INDEX ON public.planet_osm_line (name ASC ) where name is not null;    CREATE INDEX ON public.planet_osm_point (name ASC ) where name is not null;    CREATE INDEX ON public.planet_osm_roads (name ASC ) where name is not null;    CREATE INDEX ON public.planet_osm_polygon (name ASC) where name is not null;    CREATE INDEX ON public.planet_osm_line (trans_name_chs ASC ) where trans_name_chs is not null;    CREATE INDEX ON public.planet_osm_point (trans_name_chs ASC ) where trans_name_chs is not null;    CREATE INDEX ON public.planet_osm_roads (trans_name_chs ASC ) where trans_name_chs is not null;    CREATE INDEX ON public.planet_osm_polygon (trans_name_chs ASC) where trans_name_chs is not null;