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))