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 553e15e2849f6088e7de9f8407a9e79bb409283f Author: Bertil Chapuis <[email protected]> AuthorDate: Mon Dec 30 21:37:39 2024 +0100 Improve the queries in the create scripts - Add license headers - Make everything more consistent --- basemap/layers/aerialway/create.sql | 19 +++++++- basemap/layers/aeroway/create.sql | 19 +++++++- basemap/layers/amenity/create.sql | 19 +++++++- basemap/layers/attraction/create.sql | 19 +++++++- basemap/layers/barrier/create.sql | 19 +++++++- basemap/layers/boundary/create.sql | 19 +++++++- basemap/layers/building/create.sql | 15 +++++++ basemap/layers/header/create.sql | 15 +++++++ basemap/layers/highway/create.sql | 5 ++- basemap/layers/landuse/create.sql | 49 ++++++++++++++++---- basemap/layers/leisure/create.sql | 9 ++++ basemap/layers/linestring/create.sql | 19 +++++++- basemap/layers/man_made/create.sql | 18 +++++++- basemap/layers/member/create.sql | 19 +++++++- basemap/layers/natural/create.sql | 12 +++-- basemap/layers/node/create.sql | 16 +++++++ basemap/layers/ocean/create.sql | 3 ++ basemap/layers/point/create.sql | 86 +++++++++++++++++++++++++++--------- basemap/layers/polygon/create.sql | 19 +++++++- basemap/layers/railway/create.sql | 19 +++++++- basemap/layers/relation/create.sql | 16 +++++++ basemap/layers/route/create.sql | 4 +- basemap/layers/waterway/create.sql | 4 +- basemap/layers/way/create.sql | 16 +++++++ 24 files changed, 412 insertions(+), 46 deletions(-) diff --git a/basemap/layers/aerialway/create.sql b/basemap/layers/aerialway/create.sql index 7775e6e04..4d1914da8 100644 --- a/basemap/layers/aerialway/create.sql +++ b/basemap/layers/aerialway/create.sql @@ -1,2 +1,19 @@ +-- 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_aerialway AS -SELECT id, tags, geom FROM osm_way WHERE tags ? 'aerialway'; \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'aerialway'; \ No newline at end of file diff --git a/basemap/layers/aeroway/create.sql b/basemap/layers/aeroway/create.sql index f56d43937..4c546d045 100644 --- a/basemap/layers/aeroway/create.sql +++ b/basemap/layers/aeroway/create.sql @@ -1,2 +1,19 @@ +-- 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_aeroway AS -SELECT id, tags, geom FROM osm_way WHERE tags ? 'aeroway' \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'aeroway' \ No newline at end of file diff --git a/basemap/layers/amenity/create.sql b/basemap/layers/amenity/create.sql index 327be23f0..e4bceed29 100644 --- a/basemap/layers/amenity/create.sql +++ b/basemap/layers/amenity/create.sql @@ -1,2 +1,19 @@ +-- 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_amenity AS -SELECT id, tags, geom FROM osm_way WHERE tags ? 'amenity'; \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'amenity'; \ No newline at end of file diff --git a/basemap/layers/attraction/create.sql b/basemap/layers/attraction/create.sql index 5cd5ad6a8..828ebffab 100644 --- a/basemap/layers/attraction/create.sql +++ b/basemap/layers/attraction/create.sql @@ -1,2 +1,19 @@ +-- 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_attraction AS -SELECT id, tags, geom FROM osm_way WHERE tags ? 'attraction'; \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'attraction'; \ No newline at end of file diff --git a/basemap/layers/barrier/create.sql b/basemap/layers/barrier/create.sql index 3ddf00e44..6ed2c1b90 100644 --- a/basemap/layers/barrier/create.sql +++ b/basemap/layers/barrier/create.sql @@ -1,2 +1,19 @@ +-- 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_barrier AS -SELECT id, tags, geom FROM osm_way WHERE tags ? 'barrier'; \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'barrier'; \ No newline at end of file diff --git a/basemap/layers/boundary/create.sql b/basemap/layers/boundary/create.sql index 5f06f34b6..17eba85a8 100644 --- a/basemap/layers/boundary/create.sql +++ b/basemap/layers/boundary/create.sql @@ -1,2 +1,19 @@ +-- 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_boundary AS -SELECT id, tags, geom FROM osm_way WHERE tags ? 'boundary'; \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'boundary'; \ No newline at end of file diff --git a/basemap/layers/building/create.sql b/basemap/layers/building/create.sql index 8d5ec5fd2..dd0145218 100644 --- a/basemap/layers/building/create.sql +++ b/basemap/layers/building/create.sql @@ -1,3 +1,18 @@ +-- 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_building AS SELECT id, diff --git a/basemap/layers/header/create.sql b/basemap/layers/header/create.sql index 73cab3580..c7e112b92 100644 --- a/basemap/layers/header/create.sql +++ b/basemap/layers/header/create.sql @@ -1,3 +1,18 @@ +-- 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 TABLE IF NOT EXISTS osm_headers ( replication_sequence_number bigint PRIMARY KEY, diff --git a/basemap/layers/highway/create.sql b/basemap/layers/highway/create.sql index b5a49eec5..354752b47 100644 --- a/basemap/layers/highway/create.sql +++ b/basemap/layers/highway/create.sql @@ -12,6 +12,8 @@ -- 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. + +DROP MATERIALIZED VIEW IF EXISTS osm_highway CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway AS WITH -- Filter the linestrings @@ -57,4 +59,5 @@ SELECT row_number() OVER () AS id, jsonb_build_object('highway', highway, 'construction', construction) AS tags, geom AS geom -FROM exploded; +FROM exploded +WITH NO DATA; diff --git a/basemap/layers/landuse/create.sql b/basemap/layers/landuse/create.sql index 386ee5c05..ffa33c984 100644 --- a/basemap/layers/landuse/create.sql +++ b/basemap/layers/landuse/create.sql @@ -1,6 +1,19 @@ +-- 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. - - +DROP MATERIALIZED VIEW IF EXISTS osm_landuse_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_filtered AS SELECT tags -> 'landuse' AS landuse, @@ -8,38 +21,58 @@ SELECT 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); +CREATE INDEX IF NOT EXISTS osm_landuse_filtered_tags_idx ON osm_landuse_filtered (landuse); +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 FROM osm_landuse_filtered -WHERE geom IS NOT NULL; +WHERE geom IS NOT NULL +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; +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; +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; +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; \ No newline at end of file +FROM osm_landuse_exploded +WITH NO DATA; + +CREATE INDEX IF NOT EXISTS osm_landuse_geom_idx ON osm_landuse USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_landuse_tags_idx ON osm_landuse USING GIN (tags); \ No newline at end of file diff --git a/basemap/layers/leisure/create.sql b/basemap/layers/leisure/create.sql index 29e784c93..aa05f74c3 100644 --- a/basemap/layers/leisure/create.sql +++ b/basemap/layers/leisure/create.sql @@ -13,6 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. +DROP MATERIALIZED VIEW IF EXISTS osm_leisure_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_filtered AS SELECT tags -> 'leisure' AS leisure, @@ -26,6 +27,7 @@ WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_leisure_filtered_geom_idx ON osm_leisure_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_leisure_filtered_tags_idx ON osm_leisure_filtered (leisure); +DROP MATERIALIZED VIEW IF EXISTS osm_leisure_clustered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_clustered AS SELECT leisure, @@ -38,6 +40,7 @@ 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, @@ -46,6 +49,7 @@ 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, @@ -53,6 +57,7 @@ SELECT 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, @@ -60,6 +65,7 @@ SELECT 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, @@ -67,3 +73,6 @@ SELECT geom FROM osm_leisure_exploded WITH NO DATA; + +CREATE INDEX IF NOT EXISTS osm_leisure_geom_idx ON osm_leisure USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_tags_idx ON osm_leisure USING GIN (tags); diff --git a/basemap/layers/linestring/create.sql b/basemap/layers/linestring/create.sql index d3de1821e..28aee067b 100644 --- a/basemap/layers/linestring/create.sql +++ b/basemap/layers/linestring/create.sql @@ -1,6 +1,23 @@ +-- 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. + +DROP MATERIALIZED VIEW IF EXISTS osm_linestring CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_linestring AS SELECT id, tags, geom, changeset FROM osm_way LEFT JOIN osm_member ON id = member_ref WHERE ST_GeometryType( osm_way.geom) = 'ST_LineString' AND tags != '{}' - AND member_ref IS NULL; \ No newline at end of file + AND member_ref IS NULL +WITH NO DATA; \ No newline at end of file diff --git a/basemap/layers/man_made/create.sql b/basemap/layers/man_made/create.sql index 26c06bef5..96e7af151 100644 --- a/basemap/layers/man_made/create.sql +++ b/basemap/layers/man_made/create.sql @@ -1,3 +1,19 @@ +-- 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_man_made AS -SELECT id, tags, geom FROM osm_way +SELECT id, tags, geom +FROM osm_way WHERE tags ? 'man_made' \ No newline at end of file diff --git a/basemap/layers/member/create.sql b/basemap/layers/member/create.sql index 58b791dd9..af62b5026 100644 --- a/basemap/layers/member/create.sql +++ b/basemap/layers/member/create.sql @@ -1,3 +1,19 @@ +-- 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. + +DROP MATERIALIZED VIEW IF EXISTS osm_member CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_member AS SELECT DISTINCT member_ref as member_ref FROM osm_relation, @@ -5,4 +21,5 @@ FROM osm_relation, WHERE geom IS NOT NULL AND member_type = 1 AND tags ->> 'type' = 'multipolygon' - AND NOT tags ->> 'natural' = 'coastline'; \ No newline at end of file + AND NOT tags ->> 'natural' = 'coastline' +WITH NO DATA; \ No newline at end of file diff --git a/basemap/layers/natural/create.sql b/basemap/layers/natural/create.sql index 0af84236a..bfecbaef2 100644 --- a/basemap/layers/natural/create.sql +++ b/basemap/layers/natural/create.sql @@ -13,8 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. --- ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree'); - +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, @@ -28,6 +27,7 @@ WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_natural_filtered_geom_idx ON osm_natural_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_filtered_tags_idx ON osm_natural_filtered (natural_value); +DROP MATERIALIZED VIEW IF EXISTS osm_natural_clustered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_clustered AS SELECT natural_value, @@ -40,6 +40,7 @@ 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, @@ -48,6 +49,7 @@ 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, @@ -55,6 +57,7 @@ SELECT 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, @@ -62,6 +65,7 @@ SELECT 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, @@ -70,5 +74,5 @@ SELECT FROM osm_natural_exploded WITH NO DATA; -CREATE INDEX IF NOT EXISTS osm_natural_geom_idx ON osm_natural_filtered USING GIST (geom); -CREATE INDEX IF NOT EXISTS osm_natural_tags_idx ON osm_natural_filtered USING GIN (natural_value); +CREATE INDEX IF NOT EXISTS osm_natural_geom_idx ON osm_natural USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_natural_tags_idx ON osm_natural USING GIN (tags); diff --git a/basemap/layers/node/create.sql b/basemap/layers/node/create.sql index c324031dd..62ac9d79e 100644 --- a/basemap/layers/node/create.sql +++ b/basemap/layers/node/create.sql @@ -1,3 +1,19 @@ +-- 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. + +DROP TABLE IF EXISTS osm_node CASCADE; CREATE TABLE IF NOT EXISTS osm_node ( id int8 PRIMARY KEY, diff --git a/basemap/layers/ocean/create.sql b/basemap/layers/ocean/create.sql index 0e83f6d61..8ec47c33c 100644 --- a/basemap/layers/ocean/create.sql +++ b/basemap/layers/ocean/create.sql @@ -12,10 +12,13 @@ -- 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. + +DROP MATERIALIZED VIEW IF EXISTS osm_ocean CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_ocean AS SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM water_polygons_shp WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_ocean_simplified CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_ocean_simplified AS SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM simplified_water_polygons_shp WITH NO DATA; diff --git a/basemap/layers/point/create.sql b/basemap/layers/point/create.sql index bfec5d4b2..617d17b60 100644 --- a/basemap/layers/point/create.sql +++ b/basemap/layers/point/create.sql @@ -3,14 +3,35 @@ SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE OR REPLACE VIEW osm_point_z20 AS SELECT * FROM point; -CREATE OR REPLACE VIEW osm_point_z19 AS SELECT * FROM point; -CREATE OR REPLACE VIEW osm_point_z18 AS SELECT * FROM point; -CREATE OR REPLACE VIEW osm_point_z17 AS SELECT * FROM point; -CREATE OR REPLACE VIEW osm_point_z16 AS SELECT * FROM point; -CREATE OR REPLACE VIEW osm_point_z15 AS SELECT * FROM point; -CREATE OR REPLACE VIEW osm_point_z14 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z20 AS +SELECT * +FROM point; +CREATE OR REPLACE VIEW osm_point_z19 AS +SELECT * +FROM point; + +CREATE OR REPLACE VIEW osm_point_z18 AS +SELECT * +FROM point; + +CREATE OR REPLACE VIEW osm_point_z17 AS +SELECT * +FROM point; + +CREATE OR REPLACE VIEW osm_point_z16 AS +SELECT * +FROM point; + +CREATE OR REPLACE VIEW osm_point_z15 AS +SELECT * +FROM point; + +CREATE OR REPLACE VIEW osm_point_z14 AS +SELECT * +FROM point; + +DROP MATERIALIZED VIEW IF EXISTS osm_point_z13; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z13 AS SELECT id, tags, geom FROM point @@ -19,8 +40,10 @@ WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','mun OR (tags->>'highway') = 'motorway_junction' OR (tags->>'tourism') = 'wilderness_hut' OR (tags->>'waterway') = 'waterfall' - OR (tags->>'railway') = 'level_crossing'; + OR (tags->>'railway') = 'level_crossing' +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z12; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z12 AS SELECT id, tags, geom FROM point @@ -28,63 +51,86 @@ WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','mun OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) OR (tags->>'highway') = 'motorway_junction' OR (tags->>'tourism') = 'wilderness_hut' - OR (tags->>'waterway') = 'waterfall'; + OR (tags->>'waterway') = 'waterfall' +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z11; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z11 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town','village']) OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) - OR (tags->>'highway') = 'motorway_junction'; + OR (tags->>'highway') = 'motorway_junction' +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z10; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z10 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) - OR (tags->>'highway') = 'motorway_junction'; + OR (tags->>'highway') = 'motorway_junction' +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z9; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z9 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']); +WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z8; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z8 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']); +WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z7; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z7 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z6; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z6 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z5; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z5 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z4; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z4 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']); +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z3; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z3 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']); +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']) +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z2; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z2 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = 'country'; +WHERE (tags->>'place') = 'country' +WITH NO DATA; +DROP MATERIALIZED VIEW IF EXISTS osm_point_z1; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z1 AS SELECT id, tags, geom FROM point -WHERE (tags->>'place') = 'country'; +WHERE (tags->>'place') = 'country' +WITH NO DATA; diff --git a/basemap/layers/polygon/create.sql b/basemap/layers/polygon/create.sql index ea70ab309..6cba6e162 100644 --- a/basemap/layers/polygon/create.sql +++ b/basemap/layers/polygon/create.sql @@ -1,3 +1,19 @@ +-- 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. + +DROP MATERIALIZED VIEW IF EXISTS osm_polygon CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_polygon AS SELECT id, tags, geom FROM osm_way LEFT JOIN osm_member ON id = member_ref @@ -13,4 +29,5 @@ UNION SELECT id, tags, (st_dump(geom)).geom as geom FROM osm_relation WHERE ST_GeometryType( osm_relation.geom) = 'ST_MultiPolygon' - AND tags != '{}'; \ No newline at end of file + AND tags != '{}' +WITH NO DATA; \ No newline at end of file diff --git a/basemap/layers/railway/create.sql b/basemap/layers/railway/create.sql index 128ce70dc..0c38510c9 100644 --- a/basemap/layers/railway/create.sql +++ b/basemap/layers/railway/create.sql @@ -1,3 +1,19 @@ +-- 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. + +DROP MATERIALIZED VIEW IF EXISTS osm_railway CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway AS SELECT id, tags, geom FROM (SELECT min(id) as id, @@ -5,4 +21,5 @@ FROM (SELECT min(id) (st_dump(st_linemerge(st_collect(geom)))).geom as geom FROM osm_way WHERE tags ->> 'railway' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') - GROUP BY tags -> 'railway', tags -> 'service') AS mergedDirective; \ No newline at end of file + GROUP BY tags -> 'railway', tags -> 'service') AS mergedDirective +WITH NO DATA; \ No newline at end of file diff --git a/basemap/layers/relation/create.sql b/basemap/layers/relation/create.sql index 9a2bc13ba..4dc135216 100644 --- a/basemap/layers/relation/create.sql +++ b/basemap/layers/relation/create.sql @@ -1,3 +1,19 @@ +-- 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. + +DROP TABLE IF EXISTS osm_relation CASCADE; CREATE TABLE IF NOT EXISTS osm_relation ( id int8 PRIMARY KEY, diff --git a/basemap/layers/route/create.sql b/basemap/layers/route/create.sql index cb1f26266..1ffa8eead 100644 --- a/basemap/layers/route/create.sql +++ b/basemap/layers/route/create.sql @@ -13,6 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. +DROP MATERIALIZED VIEW IF EXISTS osm_route CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route AS SELECT id, tags, geom FROM ( @@ -24,6 +25,7 @@ FROM ( WHERE tags ->> 'route' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') AND NOT tags ? 'service' GROUP BY tags -> 'route' -) AS mergedDirective; +) AS mergedDirective +WITH NO DATA; CREATE INDEX IF NOT EXISTS osm_route_geom_index ON osm_route USING SPGIST (geom); diff --git a/basemap/layers/waterway/create.sql b/basemap/layers/waterway/create.sql index 45605a2dd..55b668106 100644 --- a/basemap/layers/waterway/create.sql +++ b/basemap/layers/waterway/create.sql @@ -13,6 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. +DROP MATERIALIZED VIEW IF EXISTS osm_waterway CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway AS WITH -- Filter the linestrings @@ -55,4 +56,5 @@ SELECT row_number() OVER () AS id, jsonb_build_object('waterway', waterway) AS tags, geom AS geom -FROM exploded; +FROM exploded +WITH NO DATA; diff --git a/basemap/layers/way/create.sql b/basemap/layers/way/create.sql index ce72df9a1..3d0195980 100644 --- a/basemap/layers/way/create.sql +++ b/basemap/layers/way/create.sql @@ -1,3 +1,19 @@ +-- 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. + +DROP TABLE IF EXISTS osm_way CASCADE; CREATE TABLE IF NOT EXISTS osm_way ( id int8 PRIMARY KEY,
