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:18 UTC

(incubator-baremaps) 16/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 dd30468bb75ef87518eebf56ea1e78dd7595fef9
Author: Bertil Chapuis <bc...@gmail.com>
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))