基于PostgreSQL的百度或高德等POI多层级分类的数据库设计
目录
前言
一、百度 VS 高德 POI分类
1、高德POI分类
2、百度POI分类
3、分类对比与区别
二、POI分类表设计
1、物理表结构
2、数据存储
3、数据查询
三、总结
前言
在当今数字化快速发展的时代，地理信息数据的重要性日益凸显，而POI（Point of Interest，兴趣点）数据作为地理空间数据的关键组成部分，广泛应用于地图导航、位置服务、商业选址、城市规划等诸多领域。例如，在地图导航软件中，精准丰富的POI数据能够为用户呈现周边各类设施的位置与信息，帮助其更好地规划出行路线；对于商业机构而言，分析POI数据则可助力其洞察区域商业布局，为新店选址提供科学依据。目前，百度、高德等地图服务提供商均积累了海量且细致的POI数据，这些数据涵盖了餐饮、住宿、购物、旅游景点、交通设施等众多生活服务类别，并且呈现出多层级的分类结构。以餐饮类为例，先分为中式、西式、日式等菜系大类，中式菜系下又有川菜、粤菜、鲁菜等细分，每一细分菜系下还可能包含不同风格、档次的餐厅子项。如此复杂且层次分明的分类体系，对数据的存储与管理提出了严峻挑战。
传统的数据库存储方式在面对这种多层级、海量的POI数据时，逐渐显现出诸多弊端。一方面，数据之间的层级关系难以直观、高效地表达，导致在数据查询时，尤其是跨层级查询或钻取时，操作繁琐且性能不佳；另一方面，随着数据量的不断增长，数据库的扩展性也受到限制，难以灵活应对业务的快速扩张需求。为了实现多源数据的POI分类的准确管理，同时可以保持各平台的独立性，要求我们可以实现对不同的数据源进行管理，因此要求设计一个多层次的POI分类表，以适应多源多层次的分类管理需求。
PostgreSQL作为一种功能强大、开源的数据库管理系统，凭借其在存储复杂数据结构、支持扩展性以及数据一致性和完整性保障等方面的卓越性能，成为了应对这类挑战的理想选择之一。其支持丰富的数据类型，例如可以利用数组类型、JSON类型等来存储多层级数据结构的特征信息；而且具备强大的函数与索引功能，能够针对特定的查询需求进行优化，提升查询效率。因此，对基于PostgreSQL存储百度或高德等POI多层级分类的数据库设计进行深入研究具有重要的现实意义。本研究旨在探索如何根据POI数据的多层级特点，合理规划数据库的表结构、字段设计，通过有效的数据关联方式和索引策略，实现对海量POI数据的高效存储、快速查询以及便捷的维护与更新，为各类依赖POI数据的应用场景提供坚实可靠的数据支撑，推动地理信息数据在更广泛领域的深度应用与创新发展，以更好地满足人们在智能生活与智慧城市建设进程中对精准地理信息服务的不断攀升的需求。
一、百度 VS 高德 POI分类
本节将重点介绍百度和高德两个平台的POI分类信息，不仅详细说明两者的官方数据入口。还对比了两者的层级和各层级的分类信息，通过两个平台的分类分层的体系说明，也说明了两者在细分领域的深入程度。不管是高德还是百度的检索接口，POI的分类绝对是很重要的一个信息。同时也是区域信息查询的一个重要条件。
1、高德POI分类
首先来看高德POI分类，可以在检索接口中看到POI分类的第一个应用，如下图所示：
| pes | 查询 POI 类型 | 可选值：分类代码 或 汉字（若用汉字，请严格按照附件之中的汉字填写） 规则： 多个关键字用“|”分割 分类代码由六位数字组成，一共分为三个部分，前两个数字代表大类；中间两个数字代表中类；最后两个数字代表小类。 若指定了某个大类，则所属的中类、小类都会被显示。 例如：010000为汽车服务（大类） 010100为加油站（中类） 010101为中国石化（小类） 010900为汽车租赁（中类） 010901为汽车租赁还车（小类） 当指定010000，则010100等中类、010101等小类会被包含，当指定010900，则010901等小类会被包含。 注意：返回结果可能会包含中小类POI，但不保证包含所有，如需更精确的信息，推荐输入小类或缩小范围查询 下载 POI 分类编码和城市编码表 若不指定 city，返回的内容为城市列表以及此城市内有多少结果符合要求。 | 必填（keyword 或者 types 二选一必填） |
其实在这里就可以下载高德平台的POI分类和城市信息表，点击超链接将跳转到具体的下载页面中，如下图所示：
点击下载按钮就可以将高德的POI分类表格下载到本地。
2、百度POI分类
下面再来详细介绍一下百度的POI分类，同样是来看一下百度地图的POI分类的入口，关于POI的介绍有两个入口，第一个是比较粗略的二级分类，同样在检索接口中包含以下分类Tag的定义与使用。
| tag | 检索分类偏好，与query组合进行检索，多个分类以","分隔 （POI分类），如果需要严格按分类检索，请通过query参数设置 | 美食 | string(50) | 否 |
点击“POI分类”可以查看百度的POI分类信息，如下表所示：
| 一级行业分类 | 二级行业分类 |
|---|---|
| 美食 | 中餐厅、外国餐厅、小吃快餐店、蛋糕甜品店、咖啡厅、茶座、酒吧、其他 |
| 酒店 | 星级酒店、快捷酒店、公寓式酒店、民宿、其他 |
| 购物 | 购物中心、百货商场、超市、便利店、家居建材、家电数码、商铺、市场、其他 |
| 生活服务 | 通讯营业厅、邮局、物流公司、售票处、洗衣店、图文快印店、照相馆、房产中介机构、公用事业、维修点、家政服务、殡葬服务、彩票销售点、宠物服务、报刊亭、公共厕所、步骑行专用道驿站、其他 |
| 丽人 | 美容、美发、美甲、美体、其他 |
| 旅游景点 | 公园、动物园、植物园、游乐园、博物馆、水族馆、海滨浴场、文物古迹、教堂、风景区、景点、寺庙、其他 |
| 休闲娱乐 | 度假村、农家院、电影院、ktv、剧院、歌舞厅、网吧、游戏场所、洗浴按摩、休闲广场、其他 |
| 运动健身 | 体育场馆、极限运动场所、健身中心、其他 |
| 教育培训 | 高等院校、中学、小学、幼儿园、成人教育、亲子教育、特殊教育学校、留学中介机构、科研机构、培训机构、图书馆、科技馆、其他 |
| 文化传媒 | 新闻出版、广播电视、艺术团体、美术馆、展览馆、文化宫、其他 |
| 医疗 | 综合医院、专科医院、诊所、药店、体检机构、疗养院、急救中心、疾控中心、医疗器械、医疗保健、核酸检测点、新冠疫苗接种点、风险点、方舱医院、发热门诊、其他 |
| 汽车服务 | 汽车销售、汽车维修、汽车美容、汽车配件、汽车租赁、汽车检测场、其他 |
| 交通设施 | 飞机场、火车站、地铁站、地铁线路、长途汽车站、公交车站、港口、停车场、停车区、停车位、加油加气站、服务区、收费站、桥、充电站、路侧停车位、普通停车位、接送点、电动自行车充电站、高速公路停车区、其他 |
| 金融 | 银行、ATM、信用社、投资理财、典当行、其他 |
| 房地产 | 写字楼、住宅区、宿舍、内部楼栋、其他 |
| 公司企业 | 公司、园区、农林园艺、厂矿、其他 |
| 政府机构 | 中央机构、各级政府、行政单位、公检法机构、涉外机构、党派团体、福利机构、政治教育机构、社会团体、民主党派、居民委员会、其他 |
| 出入口 | 高速公路出口、高速公路入口、机场出口、机场入口、车站出口、车站入口、门（备注：建筑物和建筑物群的门）、停车场出入口、自行车高速出口、自行车高速入口、自行车高速出入口、停车场出口、停车场入口、其他 |
| 自然地物 | 岛屿、山峰、水系、其他 |
| 行政地标 | 省、省级城市、地级市、区县、商圈、乡镇、村庄、其他 |
| 门址 | 门址点、其他 |
| 道路 | 高速公路、国道、省道、县道、乡道、城市快速路、城市主干道、城市次干道、城市支路、车渡线、路口、其他 |
| 铁路 | 铁路、地铁/轻轨、磁悬浮列车、有轨电车、城际快轨、其他 |
| 行政界线 | 其他国家国界、已定国界、未定国界、港澳界线、南海范围线、已定省界、未定省界、海岸线、其他 |
| 其他线要素 | 桥梁、隧道、行政假想线、水域假想线、绿地假想线、岛屿假想线、疫情管控区、其他 |
| 行政区划 | 世界级、国家级、省级、市级、区县级、热点区域、建成区、智能区域、其他 |
| 水系 | 双线河、湖沼、海洋、其他 |
| 绿地 | 绿地公园、高尔夫球场、岛、绿化带、机场、机场道路、其他 |
| 标注 | 大洲标注、大洋标注、海域标注、水系标注、岛屿标注、非水系标注、其他 |
| 公交线路 | 普通日行公交车、地铁\轻轨、有轨电车、机场巴士（前往机场）、机场巴士（从机场返回）、机场巴士（机场之间）、旅游线路车、夜班车、轮渡、快车、慢车、机场快轨（前往机场）、机场快轨（从机场返回）、机场轨道交通环路、其他 |
| 电子眼 | 限速电子眼、应急车道电子眼、公交车道电子眼、外地车辆电子眼、违章电子眼、其他 |
当然，以上表格是一个最多到二级的分类信息。如果需要百度的更详细的POI分类表格，需要在接口文档的响应参数中进行查看，如下图：
点击红框中的超链接即可下载百度的POI分类。
3、分类对比与区别
我们将百度和高德两个平台的POI类别都下载都本地后，可以打开Excel文件具体的对比一下两者的联系和区别。首先来看下高德的POI分类表格，如下图所示：
可以看到，高德的POI分类是三级分类，最多也就是三级。 接下来再看一下百度的分类：
可以很明显的看到，百度的POI分类层级居然到了五级，比高德的三级更多也更细。这也侧面应征了前面的话，百度的POI分类比高德更加细致。除了两个在层级有区别以外，我们也可以对比两者的一级分类的对应关系，百度地图POI分类中，旅游景点是一级分类；而高德地图的一级分类是风景名胜；当然这种分法本身没有什么问题，分类而已。同时，两者在层次设计上，高德一般三级分类都是全部设置好的，而百度地图的POI分类，有的只有二级，不会到更细致的层级，这也是两者的一个大差别。如果还有其它未说明的区别或者联系，欢迎大家在评论区留言指出。
二、POI分类表设计
不管是百度地图还是高德地图，两者其实都包含了一个层级的概念，而一般层级在树形结构中用的非常多。相信大家对二叉树等比较熟悉，这里其实也是一个差不多的意思。因此我们在进行表结构的设计时就考虑到了这种父子关系的树形结构设计。那么本文将给出一种具体的面向高德或者百度的POI分类存储表，能够实现两个不同平台的数据存储与检索，最后给一个查询的实例用于说明如何进行数据查询。
1、物理表结构
既然是面向多层级的动态树结构，这里我们就一定会采用树形表结构设计。这里我们直接给出存储POI类型表的物理表结构，当然这里我们给出了数据表结构的SQL语句，如有不足或者有问题的地方，欢迎大家评论区交流，物理表模型如下：
对应的物理表SQL如下：
CREATE TABLE "public"."biz_poi_category" ("pk_id" int8 NOT NULL,"category_name" varchar(100) NOT NULL DEFAULT ''::character varying,"origin_code" varchar(100) NOT NULL DEFAULT ''::character varying,"parent_id" int8 NOT NULL DEFAULT 0,"ancestors" varchar(2000) NOT NULL DEFAULT ''::character varying,"category_english_name" varchar(100) NOT NULL DEFAULT ''::character varying,"platform" varchar(10) NOT NULL DEFAULT ''::character varying,"order_num" int4 NOT NULL DEFAULT 0,"status" int2 NOT NULL DEFAULT 0,"del_flag" int2 NOT NULL DEFAULT 0,"create_by" varchar(64) NOT NULL DEFAULT ''::character varying,"create_time" timestamp(6),"update_by" varchar(64) NOT NULL DEFAULT ''::character varying,"update_time" timestamp(6),CONSTRAINT "pk_biz_poi_category" PRIMARY KEY ("pk_id")
);
CREATE INDEX "idx_biz_poi_category_name" ON "public"."biz_poi_category" USING btree ("category_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
COMMENT ON COLUMN "public"."biz_poi_category"."pk_id" IS 'pk_id';
COMMENT ON COLUMN "public"."biz_poi_category"."category_name" IS '分类名称';
COMMENT ON COLUMN "public"."biz_poi_category"."origin_code" IS '原始分类code';
COMMENT ON COLUMN "public"."biz_poi_category"."parent_id" IS '父分类id';
COMMENT ON COLUMN "public"."biz_poi_category"."ancestors" IS '祖级列表';
COMMENT ON COLUMN "public"."biz_poi_category"."category_english_name" IS '分类名称-英文';
COMMENT ON COLUMN "public"."biz_poi_category"."platform" IS '所属平台';
COMMENT ON COLUMN "public"."biz_poi_category"."order_num" IS '排序号';
COMMENT ON COLUMN "public"."biz_poi_category"."status" IS '状态';
COMMENT ON COLUMN "public"."biz_poi_category"."del_flag" IS '栅格标记';
COMMENT ON COLUMN "public"."biz_poi_category"."create_by" IS '创建者';
COMMENT ON COLUMN "public"."biz_poi_category"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."biz_poi_category"."update_by" IS '更新人';
COMMENT ON COLUMN "public"."biz_poi_category"."update_time" IS '更新时间';
COMMENT ON TABLE "public"."biz_poi_category" IS 'POI分类信息表，完全兼容百度、高德等平台，还可以扩展至其它平台';2、数据存储
数据的存储结构比较简单，主要就是基于parent_id进行父子关联使用。这里我们使用web界面对POI分类表进行管理，手动添加示例数据。我们在添加一些演示后演示的数据如下：
3、数据查询
这里我们简单的讲解一下如何在PostgreSQL中进行POI分类表的的数据检索查询。这里我们讲一种是查询高德节点和下级子节点。sql如下：
with RECURSIVE r as
( select t1.* from biz_poi_category t1 where t1.pk_id = 101
union all
select t2.* from biz_poi_category t2 inner join r on r.pk_id = t2.parent_id)
select * from r order by pk_id;查询结果如下：
三、总结
以上就是本文的主要内容，本研究旨在探索如何根据POI数据的多层级特点，合理规划数据库的表结构、字段设计，通过有效的数据关联方式和索引策略，实现对海量POI数据的高效存储、快速查询以及便捷的维护与更新，为各类依赖POI数据的应用场景提供坚实可靠的数据支撑，推动地理信息数据在更广泛领域的深度应用与创新发展，以更好地满足人们在智能生活与智慧城市建设进程中对精准地理信息服务的不断攀升的需求。文章详细得介绍了百度和高德两个地图平台的POI分类信息，最后基于这个POI分类设计了存储相关数据的物理表用来存储相关数据。行文仓促，定有不足之处，欢迎各位朋友在评论区批评指正，不胜感激。