一踪央、準備工作

1.完成數(shù)據(jù)準備OSM本地發(fā)布(二)-----數(shù)據(jù)準備

2.推薦使用Navicat操作數(shù)據(jù)庫

3.下文是在《在GeoServer中為OpenStreetMap數(shù)據(jù)設置OSM樣式》基礎上的改進，可直接看該文實現(xiàn)圖層發(fā)布

二、OSM字段說明

planet_osm_line,planet_osm_point,planet_osm_polygon和planet_osm_roads四個數(shù)據(jù)表中字段官方說明文檔：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.地區(qū)相關字段

官方詳細說明文檔：Map Features - OpenStreetMap place

三承璃、執(zhí)行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);

執(zhí)行上述SQL語句生成不同圖層进萄。

四捻脖、QGIS查看圖層

md_be0b8b03.png

到此地圖雛形基本顯現(xiàn)，但直觀看上去很亂中鼠，后面將對其進行樣式調(diào)節(jié)并發(fā)布到Geoserver中可婶。