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 6099fb82cab28b62fdf421271beb999287e0e77f Author: Bertil Chapuis <[email protected]> AuthorDate: Thu Jan 2 11:41:03 2025 +0100 Improve the highway layer --- basemap/layers/highway/create.sql | 91 +++++++++++++++++++++----------------- basemap/layers/highway/refresh.sql | 18 ++++---- basemap/layers/highway/tileset.js | 8 +--- 3 files changed, 62 insertions(+), 55 deletions(-) diff --git a/basemap/layers/highway/create.sql b/basemap/layers/highway/create.sql index bb9e3b743..6836e815f 100644 --- a/basemap/layers/highway/create.sql +++ b/basemap/layers/highway/create.sql @@ -13,35 +13,53 @@ -- 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 filtered AS (SELECT tags -> 'highway' AS highway, - tags -> 'construction' AS construction, - geom AS geom - FROM osm_linestring - WHERE tags ->> 'highway' IN - ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', - 'secondary_link', 'tertiary', 'tertiary_link', 'unclassified', 'residential', 'construction')), - clustered AS (SELECT highway AS highway, - construction AS construction, - geom as geom, - ST_ClusterDBSCAN(geom, 0, 1) OVER (PARTITION BY highway) AS cluster - FROM filtered), - merged AS (SELECT highway AS highway, - construction AS construction, +CREATE OR REPLACE VIEW osm_highway AS +SELECT id, tags, geom +FROM osm_way +WHERE osm_way.geom IS NOT NULL + AND tags ? '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, + 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') +WITH NO DATA; + +DROP MATERIALIZED VIEW IF EXISTS osm_highway_clustered CASCADE; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_clustered AS +SELECT highway AS highway, + geom as geom, + ST_ClusterDBSCAN(geom, 0, 1) OVER (PARTITION BY highway) AS cluster +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, ST_LineMerge(ST_Collect(geom)) AS geom - FROM clustered - GROUP BY highway, construction, cluster), + FROM osm_highway_clustered + GROUP BY highway, cluster), exploded AS (SELECT highway AS highway, - construction AS construction, (ST_Dump(geom)).geom AS geom FROM merged) SELECT row_number() OVER () AS id, - jsonb_build_object('highway', highway, 'construction', construction) AS tags, + jsonb_build_object('highway', highway) AS tags, geom AS geom 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; @@ -74,21 +92,14 @@ CREATE OR REPLACE VIEW osm_highway_z13 AS SELECT id, tags, geom FROM osm_highway; -DROP MATERIALIZED VIEW IF EXISTS osm_highway_z12 CASCADE; -CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway_z12 AS +CREATE OR REPLACE VIEW osm_highway_z12 AS SELECT id, tags, geom -FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_highway) AS osm_highway -WHERE geom IS NOT NULL - AND (st_area(st_envelope(geom)) > power((78270 / power(2, 12)), 2)) - AND tags ->> 'highway' IN - ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', - 'tertiary', 'tertiary_link', 'unclassified', 'residential', 'construction') -WITH NO DATA; +FROM osm_highway; 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 11)), 2)) AND tags ->> 'highway' IN @@ -99,7 +110,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 10)), 2)) AND tags ->> 'highway' IN @@ -110,7 +121,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 9)), 2)) AND tags ->> 'highway' IN @@ -120,7 +131,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 8)), 2)) AND tags ->> 'highway' IN ('motorway', 'trunk', 'primary') @@ -129,7 +140,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 7)), 2)) AND tags ->> 'highway' IN ('motorway', 'trunk', 'primary') @@ -138,7 +149,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 6)), 2)) AND tags ->> 'highway' IN ('motorway', 'trunk', 'primary') @@ -147,7 +158,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 5)), 2)) AND tags ->> 'highway' IN ('motorway') @@ -156,7 +167,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 4)), 2)) AND tags ->> 'highway' IN ('motorway') @@ -165,7 +176,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 3)), 2)) AND tags ->> 'highway' IN ('motorway') @@ -174,7 +185,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 2)), 2)) AND tags ->> 'highway' IN ('motorway') @@ -183,7 +194,7 @@ 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) 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 (st_area(st_envelope(geom)) > power((78270 / power(2, 1)), 2)) AND tags ->> 'highway' IN ('motorway') diff --git a/basemap/layers/highway/refresh.sql b/basemap/layers/highway/refresh.sql index bff501c53..589e1c28b 100644 --- a/basemap/layers/highway/refresh.sql +++ b/basemap/layers/highway/refresh.sql @@ -13,15 +13,17 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -DROP INDEX IF EXISTS osm_highway_geom_idx; -REFRESH MATERIALIZED VIEW osm_highway; -CREATE INDEX IF NOT EXISTS osm_highway_geom_idx - ON osm_highway USING GIST (geom); +DROP INDEX IF EXISTS osm_highway_filtered_geom; +REFRESH MATERIALIZED VIEW osm_highway_filtered; +CREATE INDEX IF NOT EXISTS osm_highway_filtered_geom ON osm_highway_filtered 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_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 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_z11_geom_idx; REFRESH MATERIALIZED VIEW osm_highway_z11; diff --git a/basemap/layers/highway/tileset.js b/basemap/layers/highway/tileset.js index bb1a0a240..48d8090a1 100644 --- a/basemap/layers/highway/tileset.js +++ b/basemap/layers/highway/tileset.js @@ -20,15 +20,9 @@ export default { queries: [ { minzoom: 4, - maxzoom: 14, - sql: - "SELECT id, tags, geom FROM osm_highway_z$zoom", - }, - { - minzoom: 14, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_way WHERE tags ? 'highway'", + "SELECT id, tags, geom FROM osm_highway_z$zoom", }, ], }
