OSM地图本地发布(三)-----自定义图层提取

男娘i 2022-09-14 09:48 431阅读 0赞

# 一、准备工作 #

1.完成数据准备[OSM本地发布(二)-----数据准备][OSM_-----]

2.推荐使用**Navicat**操作数据库

3.下文是在《[在GeoServer中为OpenStreetMap数据设置OSM样式][GeoServer_OpenStreetMap_OSM]》基础上的改进，可直接看该文实现图层发布

# 二、OSM字段说明 #

planet\_osm\_line,planet\_osm\_point,planet\_osm\_polygon和planet\_osm\_roads四个数据表中字段**官方说明文档：**[https://wiki.openstreetmap.org/wiki/Zh-hans:Map\_Features][https_wiki.openstreetmap.org_wiki_Zh-hans_Map_Features]；以下为对部分字段的整理。

### 1.**共有字段** ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:81.3pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:344.8pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:81.3pt;"> osm_id </td> 
 <td style="vertical-align:top;width:344.8pt;"> 主键 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:81.3pt;"> way </td> 
 <td style="vertical-align:top;width:344.8pt;"> 存储的地理数据 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:81.3pt;"> name </td> 
 <td style="vertical-align:top;width:344.8pt;"> 属性名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:81.3pt;"> tags </td> 
 <td style="vertical-align:top;width:344.8pt;"> 标签，属性描述信息 </td> 
 </tr> 
 </tbody> 
</table>

### **2.道路相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap highway][]

<table style="margin-left:4.65pt;width:487.5pt;"> 
 <tbody> 
 <tr> 
 <td style="width:66pt;"> 字段名称 </td> 
 <td style="width:110.25pt;"> 字段取值 </td> 
 <td style="width:311.25pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="width:66pt;"> highway </td> 
 <td style="width:110.25pt;"> motorway </td> 
 <td style="width:311.25pt;"> 国家级/省级高速公路。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> trunk </td> 
 <td style="width:311.25pt;"> 国道/城市快速路。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> primary </td> 
 <td style="width:311.25pt;"> 省道/主干道。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> motorway_link </td> 
 <td style="width:311.25pt;"> 其他公路通往高速公路的连接道路，高速公路之间的连接匝道。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> trunk_link </td> 
 <td style="width:311.25pt;"> 连接国道/快速路与其他国道/快速路或更低级道路的连接路。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> primary_link </td> 
 <td style="width:311.25pt;"> 连接省道/主干道与其他省道/主干道或更低级道路的连接路。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> proposed </td> 
 <td style="width:311.25pt;"> 规划道路（未建成道路）。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> construction </td> 
 <td style="width:311.25pt;"> 在建道路。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> motorway_junction </td> 
 <td style="width:311.25pt;"> 指定高速公路出口，一般标记在出口匝道与主线的分流处。 </td> 
 </tr> 
 <tr> 
 <td style="width:110.25pt;"> services </td> 
 <td style="width:311.25pt;"> 服务区。通常位于高速公路或快速公路上 </td> 
 </tr> 
 <tr> 
 <td style="width:66pt;"> barrier </td> 
 <td style="width:110.25pt;"> toll_booth </td> 
 <td style="width:311.25pt;"> 一个收取道路使用费或费用的地点。 </td> 
 </tr> 
 </tbody> 
</table>

### **3.水路相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap waterway][]

### **4.铁路相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap railway][]

<table style="margin-left:4.65pt;width:487.5pt;"> 
 <tbody> 
 <tr> 
 <td style="width:89.5pt;"> 字段名称 </td> 
 <td style="width:146.45pt;"> 字段取值 </td> 
 <td style="width:251.55pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="width:89.5pt;"> railway </td> 
 <td style="width:146.45pt;"> construction </td> 
 <td style="width:251.55pt;"> 在建铁路。 </td> 
 </tr> 
 <tr> 
 <td style="width:146.45pt;"> disused </td> 
 <td style="width:251.55pt;"> 废弃铁路，路基、铁轨还没有被拆除。 </td> 
 </tr> 
 <tr> 
 <td style="width:146.45pt;"> rail </td> 
 <td style="width:251.55pt;"> 在该国标准轨距上的全尺寸旅客或货物列车 </td> 
 </tr> 
 <tr> 
 <td style="width:146.45pt;"> station </td> 
 <td style="width:251.55pt;"> 火车站 </td> 
 </tr> 
 <tr> 
 <td style="width:89.5pt;"> bridge </td> 
 <td style="width:146.45pt;"> yes </td> 
 <td style="width:251.55pt;"> 桥梁 </td> 
 </tr> 
 <tr> 
 <td style="width:89.5pt;"> electrified </td> 
 <td style="width:146.45pt;"> contact_line/rail/yes/no </td> 
 <td style="width:251.55pt;"> 架空电力线/第三轨供电/电气化，但是供电方式不详/非电气化铁路 </td> 
 </tr> 
 <tr> 
 <td style="width:89.5pt;"> service </td> 
 <td style="width:146.45pt;"> siding </td> 
 <td style="width:251.55pt;"> 侧线，长度较短的轨道，并行(及连接)于主线。 </td> 
 </tr> 
 <tr> 
 <td style="width:146.45pt;"> spur </td> 
 <td style="width:251.55pt;"> 专用线，长度较短，连接企业、厂矿。 </td> 
 </tr> 
 <tr> 
 <td style="width:146.45pt;"> yard </td> 
 <td style="width:251.55pt;"> 站线，车站内的线路。 </td> 
 </tr> 
 <tr> 
 <td style="width:89.5pt;"> usage </td> 
 <td style="width:146.45pt;"> main/branch/industrial/military/tourism </td> 
 <td style="width:251.55pt;"> 铁路主要用途(主线/支线/工业/军事/旅游) </td> 
 </tr> 
 <tr> 
 <td style="width:89.5pt;"> public_transport </td> 
 <td style="width:146.45pt;"> station </td> 
 <td style="width:251.55pt;"> 铁路客运专站。 </td> 
 </tr> 
 </tbody> 
</table>

### **5.设施相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap amenity][]

### **6.土地类型相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap landuse][]

### **7.线路相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap route][]

### **8.边界相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap boundary][]

### **9.地区相关字段** ###

官方详细说明文档：[Map Features - OpenStreetMap place][]

# **三、执行sql语句生成不同图层的表** #

# **注意：默认生成图层的坐标系：EPSG:3857** #

这里以生成简易地图为例，图层主要包括：建筑物、地名、道路、河流、水域等。

### 表 1 建筑物面图层(building) ###

### 表 2 全国行政地名点图层(placenames\_medium) ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:74.85pt;"> 类型 </td> 
 <td style="vertical-align:top;width:65.45pt;"> 是否不为空 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> name </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> place </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 地名行政等级类型：state首都/city城市/ferry港口码头/small_town村庄、乡/large_town街道、办事处 /town镇 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> capital </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 城市分类：3首都/4省会城市/5二级城市/6城市各区 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> amenity </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 其他属性 </td> 
 </tr> 
 </tbody> 
</table>

DROP TABLE IF EXISTS "placenames_medium";
 CREATE TABLE "placenames_medium" AS ( 
 SELECT
 	 (CASE WHEN "name" = '澳門 Macau' THEN '澳门'
 WHEN "name" = '香港 Hong Kong' THEN '香港'
 ELSE "name" END) as "name",
 	 way,
 		 (CASE WHEN "amenity" = 'ferry_terminal' THEN 'ferry'
 ELSE place END) as place,
 		(CASE WHEN capital = 'yes' and name = '北京市' THEN '3'
 WHEN capital = 'yes' and name <> '北京市' THEN '4'
 				WHEN capital = '3' and name <> '北京市' THEN null
 				WHEN name in ('澳門 Macau', '香港 Hong Kong') and place = 'city' THEN '4'
 ELSE capital END) as capital,
 amenity 
 FROM planet_osm_point
 WHERE place IN ('state','city','metropolis','town','large_town','small_town') AND osm_id <> '8899974086' OR amenity = 'ferry_terminal'
 );
 CREATE INDEX "placenames_medium_way_idx" ON "placenames_medium" USING gist (way);

### 表 3 道路线图层(route\_line) ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:74.85pt;"> 类型 </td> 
 <td style="vertical-align:top;width:65.45pt;"> 是否不为空 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> osm_id </td> 
 <td style="vertical-align:top;width:74.85pt;"> int8(64) </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 主键id </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> name </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 道路名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> highway </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 道路类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> aeroway </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 航空道路类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> oneway </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 是否单向道路：yes是 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> tunnel </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 是否为隧道：yes是/no不是 </td> 
 </tr> 
 </tbody> 
</table>

DROP TABLE IF EXISTS "route_line";
    CREATE TABLE "route_line" AS ( 
      SELECT osm_id,"name",way,
    	  (CASE WHEN route = 'ferry' THEN	'ferry' 
    		 ELSE highway END) AS highway,
    		 aeroway,
    	  CASE WHEN oneway IN ('yes','true','1') THEN 'yes'::text END AS oneway,
        case when tunnel IN ( 'yes', 'true', '1' ) then 'yes'::text
          else 'no'::text end as tunnel,
        case when service IN ( 'parking_aisle',
          'drive_through','driveway' ) then 'INT_minor'::text
          else service end as service
      FROM planet_osm_line
      WHERE highway IS NOT NULL
      OR "aeroway" IN ('apron','runway','taxiway')
      OR route = 'ferry'
      ORDER BY z_order
    );
    CREATE INDEX "route_line_way_idx" ON "route_line" USING gist (way);

### 表 4 河流线图层(river) ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:74.85pt;"> 类型 </td> 
 <td style="vertical-align:top;width:65.45pt;"> 是否不为空 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> name </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> natural </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 自然或人工河流 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> landuse </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 土地利用类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> waterway </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 水路类型：river河流 </td> 
 </tr> 
 </tbody> 
</table>

DROP TABLE IF EXISTS "river";
    CREATE TABLE "river" AS ( 
      SELECT 
    	(CASE WHEN name in ('金沙江','长江','扬子江') THEN '长江'
    		WHEN name in ('黄河','རྨ་ཆུ། 玛曲','黄河 济南市—德州市界') THEN '黄河'
    		WHEN name in ('喀拉额尔齐斯河','喀拉额尔齐斯河;额尔齐斯河','Джалгызагат-Хэ') THEN '额尔齐斯河'
    		ELSE "name" END) as "name" ,
    	"natural", "landuse", "waterway", "way"
      FROM planet_osm_line
      WHERE "waterway" IN ('river','riverbank')
      ORDER BY z_order asc
    );
    CREATE INDEX "river_idx" ON "river" USING gist (way);

### 表 5 水域边界线图层(water\_outline) ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:74.85pt;"> 类型 </td> 
 <td style="vertical-align:top;width:65.45pt;"> 是否不为空 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> name </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> natural </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 水域类型：water水域 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> landuse </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 土地利用类型：reservoir蓄水池/basin盆地/grass草地 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> waterway </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 水路类型：stream自然流动水路/river河流/canal运河/riverbank河岸/fish_pass/drain下水道 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> water </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 河流类型 </td> 
 </tr> 
 </tbody> 
</table>

DROP TABLE IF EXISTS "water_outline";
    CREATE TABLE "water_outline" AS ( 
      SELECT "name","natural", "landuse", "waterway",  "water", "way"
      FROM planet_osm_line
      WHERE "natural" IN ('lake','water')
      OR "waterway" IN ('canal','mill_pond','river','riverbank')
      OR "landuse" IN ('basin','reservoir','water')
      ORDER BY z_order asc
    );
    CREATE INDEX "water_outline_way_idx" ON "water_outline" USING gist (way);

### 表 6 水域面图层(water) ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> 字段名 </td> 
 <td style="vertical-align:top;width:74.85pt;"> 类型 </td> 
 <td style="vertical-align:top;width:65.45pt;"> 是否不为空 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> name </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> natural </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 水域类型：hot_spring温泉/lake湖泊/ shingle河滨海滨/water水域/wetland湿地 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> landuse </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 土地利用类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> waterway </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 水路类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> water </td> 
 <td style="vertical-align:top;width:74.85pt;"> text </td> 
 <td style="vertical-align:top;width:65.45pt;"> 否 </td> 
 <td style="vertical-align:top;width:179.3pt;"> 系列水域名称 </td> 
 </tr> 
 </tbody> 
</table>

DROP TABLE IF EXISTS "water";
    CREATE TABLE "water" AS ( 
      SELECT "name","natural", "landuse", "waterway",  "water", "way"
      FROM planet_osm_polygon
      WHERE "natural" IN ('lake','water')
      OR "waterway" IN ('canal','mill_pond','river','riverbank')
      OR "landuse" IN ('basin','reservoir','water')
      ORDER BY z_order asc
    );
    CREATE INDEX "water_way_idx" ON "water" USING gist (way);

### 完整SQL: ###

DROP TABLE IF EXISTS "building";
 CREATE TABLE "building" AS (
 		SELECT "name",way,building,aeroway
 		FROM planet_osm_polygon
 		WHERE
 		( "building" IS NOT NULL AND "building" != 'no' )
 		OR "aeroway" = 'terminal' 
 		OR "waterway" = 'dam' 
 		OR man_made = 'pier'
 		ORDER BY z_order ASC
 );
 CREATE INDEX "building_way_idx" ON "building" USING gist (way);
 
 
 DROP TABLE IF EXISTS "placenames_medium";
 CREATE TABLE "placenames_medium" AS ( 
 SELECT
 	 (CASE WHEN "name" = '澳門 Macau' THEN '澳门'
 WHEN "name" = '香港 Hong Kong' THEN '香港'
 ELSE "name" END) as "name",
 	 way,
 		 (CASE WHEN "amenity" = 'ferry_terminal' THEN 'ferry'
 ELSE place END) as place,
 		(CASE WHEN capital = 'yes' and name = '北京市' THEN '3'
 WHEN capital = 'yes' and name <> '北京市' THEN '4'
 				WHEN capital = '3' and name <> '北京市' THEN null
 				WHEN name in ('澳門 Macau', '香港 Hong Kong') and place = 'city' THEN '4'
 ELSE capital END) as capital,
 amenity 
 FROM planet_osm_point
 WHERE place IN ('state','city','metropolis','town','large_town','small_town') AND osm_id <> '8899974086' OR amenity = 'ferry_terminal'
 );
 CREATE INDEX "placenames_medium_way_idx" ON "placenames_medium" USING gist (way);
 
 DROP TABLE IF EXISTS "route_line";
 CREATE TABLE "route_line" AS ( 
 SELECT osm_id,"name",way,
 	 (CASE WHEN route = 'ferry' THEN	'ferry' 
 		 ELSE highway END) AS highway,
 		 aeroway,
 	 CASE WHEN oneway IN ('yes','true','1') THEN 'yes'::text END AS oneway,
 case when tunnel IN ( 'yes', 'true', '1' ) then 'yes'::text
 else 'no'::text end as tunnel,
 case when service IN ( 'parking_aisle',
 'drive_through','driveway' ) then 'INT_minor'::text
 else service end as service
 FROM planet_osm_line
 WHERE highway IS NOT NULL
 OR "aeroway" IN ('apron','runway','taxiway')
 OR route = 'ferry'
 ORDER BY z_order
 );
 CREATE INDEX "route_line_way_idx" ON "route_line" USING gist (way);
 
 DROP TABLE IF EXISTS "river";
 CREATE TABLE "river" AS ( 
 SELECT 
 	(CASE WHEN name in ('金沙江','长江','扬子江') THEN '长江'
 		WHEN name in ('黄河','རྨ་ཆུ། 玛曲','黄河 济南市—德州市界') THEN '黄河'
 		WHEN name in ('喀拉额尔齐斯河','喀拉额尔齐斯河;额尔齐斯河','Джалгызагат-Хэ') THEN '额尔齐斯河'
 		ELSE "name" END) as "name" ,
 	"natural", "landuse", "waterway", "way"
 FROM planet_osm_line
 WHERE "waterway" IN ('river','riverbank')
 ORDER BY z_order asc
 );
 CREATE INDEX "river_idx" ON "river" USING gist (way);
 
 DROP TABLE IF EXISTS "water_outline";
 CREATE TABLE "water_outline" AS ( 
 SELECT "name","natural", "landuse", "waterway", "water", "way"
 FROM planet_osm_line
 WHERE "natural" IN ('lake','water')
 OR "waterway" IN ('canal','mill_pond','river','riverbank')
 OR "landuse" IN ('basin','reservoir','water')
 ORDER BY z_order asc
 );
 CREATE INDEX "water_outline_way_idx" ON "water_outline" USING gist (way);
 
 DROP TABLE IF EXISTS "water";
 CREATE TABLE "water" AS ( 
 SELECT "name","natural", "landuse", "waterway", "water", "way"
 FROM planet_osm_polygon
 WHERE "natural" IN ('lake','water')
 OR "waterway" IN ('canal','mill_pond','river','riverbank')
 OR "landuse" IN ('basin','reservoir','water')
 ORDER BY z_order asc
 );
 CREATE INDEX "water_way_idx" ON "water" USING gist (way);

### 执行上述SQL语句生成不同图层。 ###

# 四、QGIS查看图层 #

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARFhuaW1h_size_20_color_FFFFFF_t_70_g_se_x_16][]

到此地图雏形基本显现，但直观看上去很乱，后面将对其进行样式调节并发布到Geoserver中。

[OSM_-----]: https://blog.csdn.net/qq_40953393/article/details/120604270
[GeoServer_OpenStreetMap_OSM]: https://www.cnblogs.com/think8848/p/6013939.html
[https_wiki.openstreetmap.org_wiki_Zh-hans_Map_Features]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features
[Map Features - OpenStreetMap highway]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#.E5.85.AC.E8.B7.AF
[Map Features - OpenStreetMap waterway]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#Waterway
[Map Features - OpenStreetMap railway]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#.E9.93.81.E8.B7.AF
[Map Features - OpenStreetMap amenity]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#.E8.AE.BE.E6.96.BD.28Amenity.29
[Map Features - OpenStreetMap landuse]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#.E5.9C.9F.E5.9C.B0.E5.88.A9.E7.94.A8
[Map Features - OpenStreetMap route]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#.E8.B7.AF.E7.BA.BF
[Map Features - OpenStreetMap boundary]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#Boundary
[Map Features - OpenStreetMap place]: https://wiki.openstreetmap.org/wiki/Zh-hans:Map_Features#Place
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARFhuaW1h_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/17d3f6599107424bb2fe1205c4f82b2a.png