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 61ba616c420075b5f81076896114d61c3e131a20 Author: Bertil Chapuis <[email protected]> AuthorDate: Sun Jan 5 17:32:03 2025 +0100 Improve the leisure layer --- basemap/layers/leisure/create.sql | 460 ++++++++++++++++++++++++++++++------- basemap/layers/leisure/refresh.sql | 113 ++++++--- basemap/layers/leisure/tileset.js | 14 +- 3 files changed, 459 insertions(+), 128 deletions(-) diff --git a/basemap/layers/leisure/create.sql b/basemap/layers/leisure/create.sql index 0f45f3bd5..bcbe2531b 100644 --- a/basemap/layers/leisure/create.sql +++ b/basemap/layers/leisure/create.sql @@ -13,149 +13,453 @@ -- 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, - 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') -WITH NO DATA; +-- Zoom levels 20 to 13 -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 -FROM osm_leisure_filtered -WHERE geom IS NOT NULL -WITH NO DATA; - -DROP MATERIALIZED VIEW IF EXISTS osm_leisure CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure AS -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 OR REPLACE VIEW osm_leisure AS +SELECT id, tags, geom +FROM osm_way +WHERE tags ? 'leisure' +UNION +SELECT id, tags, geom +FROM osm_relation +WHERE tags ? 'leisure'; CREATE OR REPLACE VIEW osm_leisure_z20 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z19 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z18 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z17 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z16 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z15 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z14 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; CREATE OR REPLACE VIEW osm_leisure_z13 AS -SELECT id, tags, geom FROM osm_leisure; +SELECT id, tags, geom +FROM osm_leisure; + +-- Zoom level 12 + +DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z12_filtered CASCADE; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z12_filtered AS +SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 12) + ), + 78270 / power(2, 12) * 1.1, + 'join=mitre' + ) AS geom +FROM osm_leisure +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 12), 2) * 32 + AND tags ->> 'leisure' IN + ('garden', 'golf_course', 'marina', 'nature_reserve', 'park', 'pitch', 'sport_center', 'stadium', 'swimming_pool', + 'track') +WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z12 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z12 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 12), 2) +WITH clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM osm_leisure_z12_filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 12), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 12) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 12), 2) * 32 +GROUP BY tag, cluster +WITH NO DATA; + +-- Zoom level 11 + +DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z11_filtered CASCADE; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z11_filtered AS +SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 11) + ), + 78270 / power(2, 11) * 1.1, + 'join=mitre' + ) AS geom +FROM osm_leisure_z12 +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 11), 2) * 32 WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z11 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z11 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 11), 2) +WITH clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM osm_leisure_z11_filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 11), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 11) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 11), 2) * 32 +GROUP BY tag, cluster +WITH NO DATA; + +-- Zoom level 10 + +DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z10_filtered CASCADE; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z10_filtered AS +SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 10) + ), + 78270 / power(2, 10) * 1.1, + 'join=mitre' + ) AS geom +FROM osm_leisure_z11 +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 10), 2) * 32 WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z10 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z10 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 10), 2) +WITH clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM osm_leisure_z10_filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 10), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 10) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 10), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 9 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z9 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z9 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 9), 2) +WITH filtered AS (SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 9) + ), + 78270 / power(2, 9) * 1.1, + 'join=mitre' + ) AS geom + FROM osm_leisure_z10 + WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 9), 2) * 32), + clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 9), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 9) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 9), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 8 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z8 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z8 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 8), 2) +WITH filtered AS (SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 8) + ), + 78270 / power(2, 8) * 1.1, + 'join=mitre' + ) AS geom + FROM osm_leisure_z9 + WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 8), 2) * 32), + clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 8), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 8) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 8), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 7 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z7 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z7 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 7), 2) +WITH filtered AS (SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 7) + ), + 78270 / power(2, 7) * 1.1, + 'join=mitre' + ) AS geom + FROM osm_leisure_z8 + WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 7), 2) * 32), + clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 7), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 7) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 7), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 6 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z6 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z6 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 6), 2) +WITH filtered AS (SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 6) + ), + 78270 / power(2, 6) * 1.1, + 'join=mitre' + ) AS geom + FROM osm_leisure_z7 + WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 6), 2) * 32), + clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 6), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 6) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 6), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 5 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z5 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z5 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 5), 2) +WITH filtered AS (SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 5) + ), + 78270 / power(2, 5) * 1.1, + 'join=mitre' + ) AS geom + FROM osm_leisure_z6 + WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 5), 2) * 32), + clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 5), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 5) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 5), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 4 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z4 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z4 AS -SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 4), 2) +WITH filtered AS (SELECT tags -> 'leisure' AS tag, + st_buffer( + st_simplifypreservetopology( + geom, + 78270 / power(2, 4) + ), + 78270 / power(2, 4) * 1.1, + 'join=mitre' + ) AS geom + FROM osm_leisure_z6 + WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 4), 2) * 32), + clustered AS (SELECT tag, + geom, + st_clusterdbscan(geom, 0, 0) OVER (PARTITION BY tag) AS cluster + FROM filtered) +SELECT row_number() OVER () AS id, + jsonb_build_object('leisure', tag) AS tags, + st_simplifypreservetopology( + (st_dump( + st_buffer( + st_collect(geom), + -78270 / power(2, 4), + 'join=mitre' + ) + )).geom, + 78270 / power(2, 4) + ) AS geom +FROM clustered +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 4), 2) * 32 +GROUP BY tag, cluster WITH NO DATA; +-- Zoom level 3 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z3 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z3 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 3), 2) +FROM osm_leisure_z4 +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 3), 2) * 16 WITH NO DATA; +-- Zoom level 2 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z2 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z2 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 2), 2) +FROM osm_leisure_z4 +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 2), 2) * 16 WITH NO DATA; +-- Zoom level 1 + DROP MATERIALIZED VIEW IF EXISTS osm_leisure_z1 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z1 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom -FROM osm_leisure -WHERE st_area(st_envelope(geom)) > 250 * power(78270 / power(2, 1), 2) +FROM osm_leisure_z4 +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND st_area(geom) > power(78270 / power(2, 1), 2) * 16 WITH NO DATA; diff --git a/basemap/layers/leisure/refresh.sql b/basemap/layers/leisure/refresh.sql index 21042adac..94d7e9fb9 100644 --- a/basemap/layers/leisure/refresh.sql +++ b/basemap/layers/leisure/refresh.sql @@ -13,77 +13,116 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -DROP INDEX IF EXISTS osm_leisure_filtered_geom_idx; -REFRESH MATERIALIZED VIEW osm_leisure_filtered; -CREATE INDEX IF NOT EXISTS osm_leisure_filtered_geom_idx - ON osm_leisure_filtered USING GIST (geom); +-- Zoom level 12 -DROP INDEX IF EXISTS osm_leisure_clustered_geom_idx; -REFRESH MATERIALIZED VIEW osm_leisure_clustered; -CREATE INDEX IF NOT EXISTS osm_leisure_clustered_geom_idx - ON osm_leisure_clustered USING GIST (geom); - -DROP INDEX IF EXISTS osm_leisure_geom_idx; -REFRESH MATERIALIZED VIEW osm_leisure; -CREATE INDEX IF NOT EXISTS osm_leisure_geom_idx - ON osm_leisure USING GIST (geom); +DROP INDEX IF EXISTS osm_leisure_z12_filtered_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z12_filtered_tags_idx; +REFRESH MATERIALIZED VIEW osm_leisure_z12_filtered; +CREATE INDEX IF NOT EXISTS osm_leisure_z12_filtered_geom_idx ON osm_leisure_z12_filtered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z12_filtered_tags_idx ON osm_leisure_z12_filtered (tag); DROP INDEX IF EXISTS osm_leisure_z12_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z12_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z12; -CREATE INDEX IF NOT EXISTS osm_leisure_z12_geom_idx - ON osm_leisure_z12 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z12_geom_idx ON osm_leisure_z12 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z12_tags_idx ON osm_leisure_z12 USING GIN (tags); + +-- Zoom level 11 + +DROP INDEX IF EXISTS osm_leisure_z11_filtered_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z11_filtered_tags_idx; +REFRESH MATERIALIZED VIEW osm_leisure_z11_filtered; +CREATE INDEX IF NOT EXISTS osm_leisure_z11_filtered_geom_idx ON osm_leisure_z11_filtered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z11_filtered_tags_idx ON osm_leisure_z11_filtered (tag); DROP INDEX IF EXISTS osm_leisure_z11_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z11_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z11; -CREATE INDEX IF NOT EXISTS osm_leisure_z11_geom_idx - ON osm_leisure_z11 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z11_geom_idx ON osm_leisure_z11 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z11_tags_idx ON osm_leisure_z11 USING GIN (tags); + +-- Zoom level 10 + +DROP INDEX IF EXISTS osm_leisure_z10_filtered_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z10_filtered_tags_idx; +REFRESH MATERIALIZED VIEW osm_leisure_z10_filtered; +CREATE INDEX IF NOT EXISTS osm_leisure_z10_filtered_geom_idx ON osm_leisure_z10_filtered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z10_filtered_tags_idx ON osm_leisure_z10_filtered (tag); DROP INDEX IF EXISTS osm_leisure_z10_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z10_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z10; -CREATE INDEX IF NOT EXISTS osm_leisure_z10_geom_idx - ON osm_leisure_z10 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z10_geom_idx ON osm_leisure_z10 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z10_tags_idx ON osm_leisure_z10 USING GIN (tags); + +-- Zoom level 9 DROP INDEX IF EXISTS osm_leisure_z9_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z9_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z9; -CREATE INDEX IF NOT EXISTS osm_leisure_z9_geom_idx - ON osm_leisure_z9 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z9_geom_idx ON osm_leisure_z9 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z9_tags_idx ON osm_leisure_z9 USING GIN (tags); + +-- Zoom level 8 DROP INDEX IF EXISTS osm_leisure_z8_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z8_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z8; -CREATE INDEX IF NOT EXISTS osm_leisure_z8_geom_idx - ON osm_leisure_z8 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z8_geom_idx ON osm_leisure_z8 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z8_tags_idx ON osm_leisure_z8 USING GIN (tags); + +-- Zoom level 7 DROP INDEX IF EXISTS osm_leisure_z7_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z7_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z7; -CREATE INDEX IF NOT EXISTS osm_leisure_z7_geom_idx - ON osm_leisure_z7 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z7_geom_idx ON osm_leisure_z7 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z7_tags_idx ON osm_leisure_z7 USING GIN (tags); + +-- Zoom level 6 DROP INDEX IF EXISTS osm_leisure_z6_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z6_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z6; -CREATE INDEX IF NOT EXISTS osm_leisure_z6_geom_idx - ON osm_leisure_z6 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z6_geom_idx ON osm_leisure_z6 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z6_tags_idx ON osm_leisure_z6 USING GIN (tags); + +-- Zoom level 5 DROP INDEX IF EXISTS osm_leisure_z5_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z5_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z5; -CREATE INDEX IF NOT EXISTS osm_leisure_z5_geom_idx - ON osm_leisure_z5 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z5_geom_idx ON osm_leisure_z5 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z5_tags_idx ON osm_leisure_z5 USING GIN (tags); + +-- Zoom level 4 DROP INDEX IF EXISTS osm_leisure_z4_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z4_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z4; -CREATE INDEX IF NOT EXISTS osm_leisure_z4_geom_idx - ON osm_leisure_z4 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z4_geom_idx ON osm_leisure_z4 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z4_tags_idx ON osm_leisure_z4 USING GIN (tags); + +-- Zoom level 3 DROP INDEX IF EXISTS osm_leisure_z3_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z3_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z3; -CREATE INDEX IF NOT EXISTS osm_leisure_z3_geom_idx - ON osm_leisure_z3 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z3_geom_idx ON osm_leisure_z3 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z3_tags_idx ON osm_leisure_z3 USING GIN (tags); + +-- Zoom level 2 DROP INDEX IF EXISTS osm_leisure_z2_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z2_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z2; -CREATE INDEX IF NOT EXISTS osm_leisure_z2_geom_idx - ON osm_leisure_z2 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z2_geom_idx ON osm_leisure_z2 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z2_tags_idx ON osm_leisure_z2 USING GIN (tags); + +-- Zoom level 1 DROP INDEX IF EXISTS osm_leisure_z1_geom_idx; +DROP INDEX IF EXISTS osm_leisure_z1_tags_idx; REFRESH MATERIALIZED VIEW osm_leisure_z1; -CREATE INDEX IF NOT EXISTS osm_leisure_z1_geom_idx - ON osm_leisure_z1 USING GIST (geom); \ No newline at end of file +CREATE INDEX IF NOT EXISTS osm_leisure_z1_geom_idx ON osm_leisure_z1 USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_z1_tags_idx ON osm_leisure_z1 USING GIN (tags); diff --git a/basemap/layers/leisure/tileset.js b/basemap/layers/leisure/tileset.js index 7dd244377..4eb6ffde0 100644 --- a/basemap/layers/leisure/tileset.js +++ b/basemap/layers/leisure/tileset.js @@ -20,21 +20,9 @@ export default { queries: [ { minzoom: 1, - maxzoom: 13, - sql: - "SELECT id, tags, geom FROM osm_leisure_z$zoom WHERE tags ? 'leisure'", - }, - { - minzoom: 13, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_way WHERE tags ? 'leisure'", - }, - { - minzoom: 13, - maxzoom: 20, - sql: - "SELECT id, tags, geom FROM osm_relation WHERE tags ? 'leisure'", + "SELECT id, tags, geom FROM osm_leisure_z$zoom WHERE tags ? 'leisure'", }, ], }
