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 dd30468bb75ef87518eebf56ea1e78dd7595fef9 Author: Bertil Chapuis <[email protected]> AuthorDate: Sun Jan 5 17:35:44 2025 +0100 Improve the highway layer --- basemap/layers/highway/create.sql | 49 +++++++++++++++++++++++++-------------- 1 file changed, 32 insertions(+), 17 deletions(-) diff --git a/basemap/layers/highway/create.sql b/basemap/layers/highway/create.sql index 5d9e864bf..2f60242f0 100644 --- a/basemap/layers/highway/create.sql +++ b/basemap/layers/highway/create.sql @@ -51,7 +51,10 @@ FROM osm_highway; CREATE OR REPLACE VIEW osm_highway_z13 AS SELECT id, tags, geom -FROM osm_highway; +FROM osm_highway +WHERE tags ->> 'highway' IN + ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', + 'tertiary', 'tertiary_link', 'unclassified', 'residential'); DROP MATERIALIZED VIEW IF EXISTS osm_highway_filtered CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_filtered AS @@ -59,8 +62,8 @@ SELECT tags -> 'highway' AS highway, geom AS geom FROM osm_highway WHERE tags ->> 'highway' IN - ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', - 'secondary_link', 'tertiary', 'tertiary_link') + ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', + 'tertiary', 'tertiary_link', 'unclassified', 'residential') WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_clustered CASCADE; @@ -80,16 +83,17 @@ WITH merged AS (SELECT highway AS highway, exploded AS (SELECT highway AS highway, (ST_Dump(geom)).geom AS geom FROM merged) -SELECT row_number() OVER () AS id, +SELECT row_number() OVER () AS id, jsonb_build_object('highway', highway) AS tags, - geom AS geom + geom AS geom FROM exploded WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z12 CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z12 AS SELECT id, tags, geom -FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_highway_simplified) AS 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)) @@ -98,7 +102,8 @@ 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 +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)) @@ -110,7 +115,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z10 CASCADE; 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 +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)) @@ -122,7 +128,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z9 CASCADE; 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 +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)) @@ -133,7 +140,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z8 CASCADE; 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 +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)) @@ -143,7 +151,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z7 CASCADE; 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 +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)) @@ -153,7 +162,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z6 CASCADE; 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 +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)) @@ -163,7 +173,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z5 CASCADE; 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 +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)) @@ -173,7 +184,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z4 CASCADE; 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 +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)) @@ -183,7 +195,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z3 CASCADE; 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 +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)) @@ -193,7 +206,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z2 CASCADE; 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 +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)) @@ -203,7 +217,8 @@ WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_highway_z1 CASCADE; 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 +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))
