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 b1c289ede9f6a39a6810dff03a8afe4f038a6e70 Author: Bertil Chapuis <[email protected]> AuthorDate: Tue Dec 31 18:46:28 2024 +0100 Improve the refresh scripts --- .../layers/linestring/{index.sql => refresh.sql} | 5 +- .../{linestring/prepare.sql => member/refresh.sql} | 15 +-- basemap/layers/natural/create.sql | 1 - basemap/layers/point/index.sql | 28 ----- basemap/layers/point/refresh.sql | 16 ++- basemap/refresh.js | 137 +++------------------ 6 files changed, 42 insertions(+), 160 deletions(-) diff --git a/basemap/layers/linestring/index.sql b/basemap/layers/linestring/refresh.sql similarity index 85% rename from basemap/layers/linestring/index.sql rename to basemap/layers/linestring/refresh.sql index 6c82ea122..48169ad37 100644 --- a/basemap/layers/linestring/index.sql +++ b/basemap/layers/linestring/refresh.sql @@ -13,5 +13,8 @@ -- See the License for the specific language governing permissions and -- limitations under the License. +DROP INDEX IF EXISTS osm_linestring_geom_index; +DROP INDEX IF EXISTS osm_linestring_tags_index; +REFRESH MATERIALIZED VIEW osm_linestring; +CREATE INDEX IF NOT EXISTS osm_linestring_geom_index ON osm_linestring USING gist (geom); CREATE INDEX IF NOT EXISTS osm_linestring_tags_index ON osm_linestring USING gin (tags); -CREATE INDEX IF NOT EXISTS osm_linestring_geom_index ON osm_linestring USING gist (geom); \ No newline at end of file diff --git a/basemap/layers/linestring/prepare.sql b/basemap/layers/member/refresh.sql similarity index 62% rename from basemap/layers/linestring/prepare.sql rename to basemap/layers/member/refresh.sql index 2666b2f59..947b8da6d 100644 --- a/basemap/layers/linestring/prepare.sql +++ b/basemap/layers/member/refresh.sql @@ -13,15 +13,6 @@ -- 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; - -CREATE INDEX IF NOT EXISTS osm_linestring_tags_index ON osm_linestring USING gin (tags); -CREATE INDEX IF NOT EXISTS osm_linestring_geom_index ON osm_linestring USING gist (geom); +DROP INDEX IF EXISTS osm_member_idx; +REFRESH MATERIALIZED VIEW osm_member; +CREATE INDEX IF NOT EXISTS osm_member_idx ON osm_member (member_ref); \ No newline at end of file diff --git a/basemap/layers/natural/create.sql b/basemap/layers/natural/create.sql index fe74e8438..90f2e0718 100644 --- a/basemap/layers/natural/create.sql +++ b/basemap/layers/natural/create.sql @@ -159,4 +159,3 @@ SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_natural WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 1), 2) WITH NO DATA; - diff --git a/basemap/layers/point/index.sql b/basemap/layers/point/index.sql deleted file mode 100644 index 1c9c24e86..000000000 --- a/basemap/layers/point/index.sql +++ /dev/null @@ -1,28 +0,0 @@ --- 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 INDEX IF NOT EXISTS osm_point_geom_z13_index ON osm_point_z13 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z12_index ON osm_point_z12 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z11_index ON osm_point_z11 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z10_index ON osm_point_z10 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z9_index ON osm_point_z9 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z8_index ON osm_point_z8 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z7_index ON osm_point_z7 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z6_index ON osm_point_z6 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z5_index ON osm_point_z5 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z4_index ON osm_point_z4 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z3_index ON osm_point_z3 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z2_index ON osm_point_z2 USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_point_geom_z1_index ON osm_point_z1 USING gist (geom); \ No newline at end of file diff --git a/basemap/layers/point/refresh.sql b/basemap/layers/point/refresh.sql index 981821ba8..5dee7af78 100644 --- a/basemap/layers/point/refresh.sql +++ b/basemap/layers/point/refresh.sql @@ -25,4 +25,18 @@ REFRESH MATERIALIZED VIEW osm_point_z5; REFRESH MATERIALIZED VIEW osm_point_z4; REFRESH MATERIALIZED VIEW osm_point_z3; REFRESH MATERIALIZED VIEW osm_point_z2; -REFRESH MATERIALIZED VIEW osm_point_z1; \ No newline at end of file +REFRESH MATERIALIZED VIEW osm_point_z1; + +CREATE INDEX IF NOT EXISTS osm_point_geom_z13_index ON osm_point_z13 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z12_index ON osm_point_z12 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z11_index ON osm_point_z11 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z10_index ON osm_point_z10 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z9_index ON osm_point_z9 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z8_index ON osm_point_z8 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z7_index ON osm_point_z7 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z6_index ON osm_point_z6 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z5_index ON osm_point_z5 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z4_index ON osm_point_z4 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z3_index ON osm_point_z3 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z2_index ON osm_point_z2 USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_point_geom_z1_index ON osm_point_z1 USING gist (geom); \ No newline at end of file diff --git a/basemap/refresh.js b/basemap/refresh.js index 7afa917f0..bb1856a13 100644 --- a/basemap/refresh.js +++ b/basemap/refresh.js @@ -19,125 +19,28 @@ import config from "./config.js"; export default { "steps": [ { - "id": "openstreetmap-member", + "id": "refresh", "needs": [], "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/member/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-point", - "needs": ["openstreetmap-member"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/point/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-linestring", - "needs": ["openstreetmap-member"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/linestring/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-polygon", - "needs": ["openstreetmap-member"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/polygon/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-highway", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/highway/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-railway", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/railway/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-route", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/route/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-waterway", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/waterway/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-natural", - "needs": ["openstreetmap-polygon"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/natural/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-landuse", - "needs": ["openstreetmap-polygon"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/landuse/refresh.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-leisure", - "needs": ["openstreetmap-polygon"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/leisure/refresh.sql", - "database": config.database, - }, - ] + "layers/member/refresh.sql", + "layers/linestring/refresh.sql", + "layers/polygon/refresh.sql", + "layers/highway/refresh.sql", + "layers/landuse/refresh.sql", + "layers/leisure/refresh.sql", + "layers/natural/refresh.sql", + //"layers/ocean/refresh.sql", + "layers/point/refresh.sql", + "layers/railway/refresh.sql", + "layers/route/refresh.sql", + "layers/waterway/refresh.sql", + ].map(file => { + return { + "type": "ExecuteSql", + "file": file, + "database": config.database, + } + }) }, ] }
