You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@baremaps.apache.org by bc...@apache.org on 2025/01/06 10:23:13 UTC
(incubator-baremaps) 11/20: Improve the highway layer
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 <bc...@gmail.com>
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",
},
],
}