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 9037768daeae12c751dc9076b7f1198c8723ebe8 Author: Bertil Chapuis <[email protected]> AuthorDate: Sun Jan 5 17:31:45 2025 +0100 Improve the highway layer --- basemap/layers/highway/create.sql | 94 +++++++++++++++++++++----------------- basemap/layers/highway/refresh.sql | 5 ++ 2 files changed, 57 insertions(+), 42 deletions(-) diff --git a/basemap/layers/highway/create.sql b/basemap/layers/highway/create.sql index 6836e815f..5d9e864bf 100644 --- a/basemap/layers/highway/create.sql +++ b/basemap/layers/highway/create.sql @@ -13,12 +13,46 @@ -- See the License for the specific language governing permissions and -- limitations under the License. +-- Zoom levels 20-13 + CREATE OR REPLACE VIEW osm_highway AS SELECT id, tags, geom FROM osm_way WHERE osm_way.geom IS NOT NULL AND tags ? 'highway'; +CREATE OR REPLACE VIEW osm_highway_z20 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z19 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z18 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z17 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z16 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z15 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z14 AS +SELECT id, tags, geom +FROM osm_highway; + +CREATE OR REPLACE VIEW osm_highway_z13 AS +SELECT id, tags, geom +FROM osm_highway; + DROP MATERIALIZED VIEW IF EXISTS osm_highway_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_filtered AS SELECT tags -> 'highway' AS highway, @@ -37,10 +71,6 @@ SELECT highway AS highway, FROM osm_highway_filtered WITH NO DATA; -DROP INDEX IF EXISTS osm_highway_clustered_geom; -REFRESH MATERIALIZED VIEW osm_highway_clustered; -CREATE INDEX IF NOT EXISTS osm_highway_clustered_geom ON osm_highway_clustered USING GIST (geom); - DROP MATERIALIZED VIEW IF EXISTS osm_highway_simplified CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_simplified AS WITH merged AS (SELECT highway AS highway, @@ -56,51 +86,21 @@ SELECT row_number() OVER () AS i FROM exploded WITH NO DATA; -DROP INDEX IF EXISTS osm_highway_simplified_geom; -REFRESH MATERIALIZED VIEW osm_highway_simplified; -CREATE INDEX IF NOT EXISTS osm_highway_simplified_geom ON osm_highway_simplified USING GIST (geom); - -CREATE OR REPLACE VIEW osm_highway_z20 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z19 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z18 AS +DROP MATERIALIZED VIEW IF EXISTS osm_highway_z12 CASCADE; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z12 AS SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z17 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z16 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z15 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z14 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z13 AS -SELECT id, tags, geom -FROM osm_highway; - -CREATE OR REPLACE VIEW osm_highway_z12 AS -SELECT id, tags, geom -FROM osm_highway; +FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_highway_simplified) AS osm_highway +WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) + AND (st_area(st_envelope(geom)) > power((78270 / power(2, 12)), 2)) +WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z11 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z11 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 11)), 2)) AND tags ->> 'highway' IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', @@ -112,6 +112,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z10 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 10)), 2)) AND tags ->> 'highway' IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', @@ -123,6 +124,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z9 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 9)), 2)) AND tags ->> 'highway' IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link') @@ -133,6 +135,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z8 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 8)), 2)) AND tags ->> 'highway' IN ('motorway', 'trunk', 'primary') WITH NO DATA; @@ -142,6 +145,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z7 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 7)), 2)) AND tags ->> 'highway' IN ('motorway', 'trunk', 'primary') WITH NO DATA; @@ -151,6 +155,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z6 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 6)), 2)) AND tags ->> 'highway' IN ('motorway', 'trunk', 'primary') WITH NO DATA; @@ -160,6 +165,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z5 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 5)), 2)) AND tags ->> 'highway' IN ('motorway') WITH NO DATA; @@ -169,6 +175,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z4 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 4)), 2)) AND tags ->> 'highway' IN ('motorway') WITH NO DATA; @@ -178,6 +185,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z3 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 3)), 2)) AND tags ->> 'highway' IN ('motorway') WITH NO DATA; @@ -187,6 +195,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z2 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 2)), 2)) AND tags ->> 'highway' IN ('motorway') WITH NO DATA; @@ -196,6 +205,7 @@ CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z1 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_highway_simplified) AS osm_highway WHERE geom IS NOT NULL + AND NOT ST_IsEmpty(geom) AND (st_area(st_envelope(geom)) > power((78270 / power(2, 1)), 2)) AND tags ->> 'highway' IN ('motorway') WITH NO DATA; diff --git a/basemap/layers/highway/refresh.sql b/basemap/layers/highway/refresh.sql index 589e1c28b..a4d7ab642 100644 --- a/basemap/layers/highway/refresh.sql +++ b/basemap/layers/highway/refresh.sql @@ -25,6 +25,11 @@ DROP INDEX IF EXISTS osm_highway_simplified_geom; REFRESH MATERIALIZED VIEW osm_highway_simplified; CREATE INDEX IF NOT EXISTS osm_highway_simplified_geom ON osm_highway_simplified USING GIST (geom); +DROP INDEX IF EXISTS osm_highway_z12_geom_idx; +REFRESH MATERIALIZED VIEW osm_highway_z12; +CREATE INDEX IF NOT EXISTS osm_highway_z12_geom_idx + ON osm_highway_z12 USING GIST (geom); + DROP INDEX IF EXISTS osm_highway_z11_geom_idx; REFRESH MATERIALIZED VIEW osm_highway_z11; CREATE INDEX IF NOT EXISTS osm_highway_z11_geom_idx
