This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch basemaps-schema in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit 8232be48c20f7b85dc99e9099177718e4ec9b0bf Author: Bertil Chapuis <[email protected]> AuthorDate: Mon Dec 30 21:47:20 2024 +0100 Improve the queries in the create scripts - Add missing license headers - Merge intermediary steps --- basemap/layers/header/create.sql | 1 + basemap/layers/landuse/create.sql | 61 +++++++++++++++------------------------ basemap/layers/leisure/create.sql | 60 ++++++++++++++------------------------ basemap/layers/natural/create.sql | 60 ++++++++++++++------------------------ basemap/layers/tourism/create.sql | 15 ++++++++++ 5 files changed, 83 insertions(+), 114 deletions(-) diff --git a/basemap/layers/header/create.sql b/basemap/layers/header/create.sql index c7e112b92..aa7397af6 100644 --- a/basemap/layers/header/create.sql +++ b/basemap/layers/header/create.sql @@ -13,6 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. +DROP TABLE IF EXISTS osm_headers; CREATE TABLE IF NOT EXISTS osm_headers ( replication_sequence_number bigint PRIMARY KEY, diff --git a/basemap/layers/landuse/create.sql b/basemap/layers/landuse/create.sql index ffa33c984..1c4d9d26f 100644 --- a/basemap/layers/landuse/create.sql +++ b/basemap/layers/landuse/create.sql @@ -15,13 +15,15 @@ DROP MATERIALIZED VIEW IF EXISTS osm_landuse_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_filtered AS -SELECT - tags -> 'landuse' AS landuse, - st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom +SELECT tags -> 'landuse' AS landuse, + st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_polygon WHERE geom IS NOT NULL AND st_area(geom) > 78270 / power(2, 12) * 100 - AND tags ->> 'landuse' IN ('commercial', 'construction', 'industrial', 'residential', 'retail', 'farmland', 'forest', 'meadow', 'greenhouse_horticulture', 'meadow', 'orchard', 'plant_nursery','vineyard', 'basin', 'salt_pond', 'brownfield', 'cemetery', 'grass', 'greenfield', 'landfill', 'military', 'quarry', 'railway') + AND tags ->> 'landuse' IN + ('commercial', 'construction', 'industrial', 'residential', 'retail', 'farmland', 'forest', 'meadow', + 'greenhouse_horticulture', 'meadow', 'orchard', 'plant_nursery', 'vineyard', 'basin', 'salt_pond', 'brownfield', + 'cemetery', 'grass', 'greenfield', 'landfill', 'military', 'quarry', 'railway') WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_landuse_filtered_geom_idx ON osm_landuse_filtered USING GIST (geom); @@ -29,10 +31,9 @@ CREATE INDEX IF NOT EXISTS osm_landuse_filtered_tags_idx ON osm_landuse_filtered DROP MATERIALIZED VIEW IF EXISTS osm_landuse_clustered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_clustered AS -SELECT - landuse, - geom, - st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY landuse) AS cluster +SELECT landuse, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY landuse) AS cluster FROM osm_landuse_filtered WHERE geom IS NOT NULL WITH NO DATA; @@ -40,38 +41,22 @@ WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_landuse_clustered_geom_idx ON osm_landuse_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_landuse_clustered_tags_idx ON osm_landuse_clustered (landuse); -DROP MATERIALIZED VIEW IF EXISTS osm_landuse_grouped CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_grouped AS -SELECT - landuse, - st_collect(geom) AS geom -FROM osm_landuse_clustered -GROUP BY landuse, cluster -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_landuse_buffered CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_buffered AS -SELECT - landuse, - st_buffer(geom, 0, 'join=mitre') AS geom -FROM osm_landuse_grouped -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_landuse_exploded CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_exploded AS -SELECT - landuse, - (st_dump(geom)).geom AS geom -FROM osm_landuse_buffered -WITH NO DATA; - DROP MATERIALIZED VIEW IF EXISTS osm_landuse CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse AS -SELECT - row_number() OVER () AS id, - jsonb_build_object('landuse', landuse) AS tags, - geom -FROM osm_landuse_exploded +WITH grouped AS (SELECT landuse, + ST_Collect(geom) AS geom + FROM osm_landuse_clustered + GROUP BY landuse, cluster), + buffered AS (SELECT landuse, + ST_Buffer(geom, 0, 'join=mitre') AS geom + FROM grouped), + exploded AS (SELECT landuse, + (ST_Dump(geom)).geom AS geom + FROM buffered) +SELECT ROW_NUMBER() OVER () AS id, + JSONB_BUILD_OBJECT('landuse', landuse) AS tags, + geom +FROM exploded WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_landuse_geom_idx ON osm_landuse USING GIST (geom); diff --git a/basemap/layers/leisure/create.sql b/basemap/layers/leisure/create.sql index aa05f74c3..7a8cab8e2 100644 --- a/basemap/layers/leisure/create.sql +++ b/basemap/layers/leisure/create.sql @@ -15,13 +15,14 @@ DROP MATERIALIZED VIEW IF EXISTS osm_leisure_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_filtered AS -SELECT - tags -> 'leisure' AS leisure, - st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom +SELECT tags -> 'leisure' AS leisure, + st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_polygon WHERE geom IS NOT NULL AND st_area(geom) > 78270 / power(2, 12) * 100 - AND tags ->> 'leisure' IN ('garden', 'golf_course', 'marina', 'nature_reserve', 'park', 'pitch', 'sport_center', 'stadium', 'swimming_pool', 'track') + AND tags ->> 'leisure' IN + ('garden', 'golf_course', 'marina', 'nature_reserve', 'park', 'pitch', 'sport_center', 'stadium', 'swimming_pool', + 'track') WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_leisure_filtered_geom_idx ON osm_leisure_filtered USING GIST (geom); @@ -29,10 +30,9 @@ CREATE INDEX IF NOT EXISTS osm_leisure_filtered_tags_idx ON osm_leisure_filtered DROP MATERIALIZED VIEW IF EXISTS osm_leisure_clustered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_clustered AS -SELECT - leisure, - geom, - st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster +SELECT leisure, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY leisure) AS cluster FROM osm_leisure_filtered WHERE geom IS NOT NULL WITH NO DATA; @@ -40,38 +40,22 @@ WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_leisure_clustered_geom_idx ON osm_leisure_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_leisure_clustered_tags_idx ON osm_leisure_clustered (leisure); -DROP MATERIALIZED VIEW IF EXISTS osm_leisure_grouped CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_grouped AS -SELECT - leisure, - st_collect(geom) AS geom -FROM osm_leisure_clustered -GROUP BY leisure, cluster -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_leisure_buffered CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_buffered AS -SELECT - leisure, - st_buffer(geom, 0, 'join=mitre') AS geom -FROM osm_leisure_grouped -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_leisure_exploded CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_exploded AS -SELECT - leisure, - (st_dump(geom)).geom AS geom -FROM osm_leisure_buffered -WITH NO DATA; - DROP MATERIALIZED VIEW IF EXISTS osm_leisure CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure AS -SELECT - row_number() OVER () AS id, - jsonb_build_object('leisure', leisure) AS tags, - geom -FROM osm_leisure_exploded +WITH grouped AS (SELECT leisure, + ST_Collect(geom) AS geom + FROM osm_leisure_clustered + GROUP BY leisure, cluster), + buffered AS (SELECT leisure, + ST_Buffer(geom, 0, 'join=mitre') AS geom + FROM grouped), + exploded AS (SELECT leisure, + (ST_Dump(geom)).geom AS geom + FROM buffered) +SELECT ROW_NUMBER() OVER () AS id, + JSONB_BUILD_OBJECT('leisure', leisure) AS tags, + geom +FROM exploded WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_leisure_geom_idx ON osm_leisure USING GIST (geom); diff --git a/basemap/layers/natural/create.sql b/basemap/layers/natural/create.sql index bfecbaef2..cb631fa40 100644 --- a/basemap/layers/natural/create.sql +++ b/basemap/layers/natural/create.sql @@ -15,13 +15,14 @@ DROP MATERIALIZED VIEW IF EXISTS osm_natural_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_filtered AS -SELECT - tags -> 'natural' AS natural_value, - st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom +SELECT tags -> 'natural' AS natural_value, + st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_polygon WHERE geom IS NOT NULL AND st_area(geom) > 78270 / power(2, 12) * 100 - AND tags ->> 'natural' IN ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree') + AND tags ->> 'natural' IN + ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', + 'wetland', 'bare_rock', 'sand', 'scree') WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_natural_filtered_geom_idx ON osm_natural_filtered USING GIST (geom); @@ -29,10 +30,9 @@ CREATE INDEX IF NOT EXISTS osm_natural_filtered_tags_idx ON osm_natural_filtered DROP MATERIALIZED VIEW IF EXISTS osm_natural_clustered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_clustered AS -SELECT - natural_value, - geom, - st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY natural_value) AS cluster +SELECT natural_value, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY natural_value) AS cluster FROM osm_natural_filtered WHERE geom IS NOT NULL WITH NO DATA; @@ -40,38 +40,22 @@ WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_natural_clustered_geom_idx ON osm_natural_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_clustered_tags_idx ON osm_natural_clustered (natural_value); -DROP MATERIALIZED VIEW IF EXISTS osm_natural_grouped CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_grouped AS -SELECT - natural_value, - st_collect(geom) AS geom -FROM osm_natural_clustered -GROUP BY natural_value, cluster -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_natural_buffered CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_buffered AS -SELECT - natural_value, - st_buffer(geom, 0, 'join=mitre') AS geom -FROM osm_natural_grouped -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_natural_exploded CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_exploded AS -SELECT - natural_value, - (st_dump(geom)).geom AS geom -FROM osm_natural_buffered -WITH NO DATA; - DROP MATERIALIZED VIEW IF EXISTS osm_natural CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural AS -SELECT - row_number() OVER () AS id, - jsonb_build_object('natural', natural_value) AS tags, - geom -FROM osm_natural_exploded +WITH grouped AS (SELECT natural_value, + ST_Collect(geom) AS geom + FROM osm_natural_clustered + GROUP BY natural_value, cluster), + buffered AS (SELECT natural_value, + ST_Buffer(geom, 0, 'join=mitre') AS geom + FROM grouped), + exploded AS (SELECT natural_value, + (ST_Dump(geom)).geom AS geom + FROM buffered) +SELECT ROW_NUMBER() OVER () AS id, + JSONB_BUILD_OBJECT('natural', natural_value) AS tags, + geom +FROM exploded WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_natural_geom_idx ON osm_natural USING GIST (geom); diff --git a/basemap/layers/tourism/create.sql b/basemap/layers/tourism/create.sql index 962c7dca5..ae94a8651 100644 --- a/basemap/layers/tourism/create.sql +++ b/basemap/layers/tourism/create.sql @@ -1,2 +1,17 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to you under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + CREATE OR REPLACE VIEW osm_tourism AS SELECT id, tags, geom FROM osm_relation WHERE tags ? 'tourism'; \ No newline at end of file
