一、准备工作

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

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

3.下文是在《在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；以下为对部分字段的整理。

1.共有字段

2.道路相关字段

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

3.水路相关字段

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

4.铁路相关字段

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

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)

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);

表 2 全国行政地名点图层(placenames_medium)

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)

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)

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)

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)

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查看图层

md_be0b8b03.png

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