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 a573d2847daed5afe6182fac09e5020c9fbd4858 Author: Bertil Chapuis <[email protected]> AuthorDate: Wed Jan 1 10:12:00 2025 +0100 Improve the coherence of the tileset --- basemap/layers/aerialway/tileset.js | 2 +- basemap/layers/aeroway/create.sql | 2 +- basemap/layers/aeroway/tileset.js | 2 +- basemap/layers/attraction/create.sql | 3 +- basemap/layers/attraction/tileset.js | 2 +- basemap/layers/barrier/create.sql | 3 +- basemap/layers/barrier/tileset.js | 2 +- basemap/layers/boundary/create.sql | 3 +- basemap/layers/boundary/tileset.js | 2 +- basemap/layers/building/create.sql | 47 ++++++++++++++-------------- basemap/layers/linestring/create.sql | 6 ++-- basemap/layers/man_made/create.sql | 3 +- basemap/layers/man_made/tileset.js | 2 +- basemap/layers/point/create.sql | 60 ++++++++++++++++++++---------------- basemap/layers/polygon/create.sql | 12 +++++--- basemap/layers/power/create.sql | 5 ++- basemap/layers/power/tileset.js | 2 +- basemap/layers/tourism/create.sql | 3 +- basemap/layers/tourism/tileset.js | 2 +- 19 files changed, 91 insertions(+), 72 deletions(-) diff --git a/basemap/layers/aerialway/tileset.js b/basemap/layers/aerialway/tileset.js index 9672034b4..cc9dcbe59 100644 --- a/basemap/layers/aerialway/tileset.js +++ b/basemap/layers/aerialway/tileset.js @@ -21,7 +21,7 @@ export default { minzoom: 13, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_way WHERE tags ? 'aerialway'", + "SELECT id, tags, geom FROM osm_aerialway", }, ], } diff --git a/basemap/layers/aeroway/create.sql b/basemap/layers/aeroway/create.sql index 1672775db..77863fb33 100644 --- a/basemap/layers/aeroway/create.sql +++ b/basemap/layers/aeroway/create.sql @@ -17,4 +17,4 @@ CREATE OR REPLACE VIEW osm_aeroway AS SELECT id, tags, geom FROM osm_way WHERE geom IS NOT NULL -AND tags ? 'aeroway'; \ No newline at end of file + AND tags ? 'aeroway'; \ No newline at end of file diff --git a/basemap/layers/aeroway/tileset.js b/basemap/layers/aeroway/tileset.js index f17cdb660..188c1dd24 100644 --- a/basemap/layers/aeroway/tileset.js +++ b/basemap/layers/aeroway/tileset.js @@ -21,7 +21,7 @@ export default { minzoom: 13, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_way WHERE tags ? 'aeroway'", + "SELECT id, tags, geom FROM osm_aerialway", }, ], } diff --git a/basemap/layers/attraction/create.sql b/basemap/layers/attraction/create.sql index 828ebffab..c9ef71be8 100644 --- a/basemap/layers/attraction/create.sql +++ b/basemap/layers/attraction/create.sql @@ -16,4 +16,5 @@ CREATE OR REPLACE VIEW osm_attraction AS SELECT id, tags, geom FROM osm_way -WHERE tags ? 'attraction'; \ No newline at end of file +WHERE geom IS NOT NULL + AND tags ? 'attraction'; \ No newline at end of file diff --git a/basemap/layers/attraction/tileset.js b/basemap/layers/attraction/tileset.js index a53cdca94..25ece39c9 100644 --- a/basemap/layers/attraction/tileset.js +++ b/basemap/layers/attraction/tileset.js @@ -20,7 +20,7 @@ export default { { minzoom: 13, maxzoom: 20, - sql: "SELECT id, tags, geom FROM osm_way WHERE tags ? 'attraction'", + sql: "SELECT id, tags, geom FROM osm_attraction", }, ], } diff --git a/basemap/layers/barrier/create.sql b/basemap/layers/barrier/create.sql index 6ed2c1b90..776b1573d 100644 --- a/basemap/layers/barrier/create.sql +++ b/basemap/layers/barrier/create.sql @@ -16,4 +16,5 @@ CREATE OR REPLACE VIEW osm_barrier AS SELECT id, tags, geom FROM osm_way -WHERE tags ? 'barrier'; \ No newline at end of file +WHERE geom IS NOT NULL + AND tags ? 'barrier'; \ No newline at end of file diff --git a/basemap/layers/barrier/tileset.js b/basemap/layers/barrier/tileset.js index 7081a23c7..d2fe107f3 100644 --- a/basemap/layers/barrier/tileset.js +++ b/basemap/layers/barrier/tileset.js @@ -21,7 +21,7 @@ export default { minzoom: 14, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_way WHERE tags ? 'barrier'", + "SELECT id, tags, geom FROM osm_barrier", }, ], } diff --git a/basemap/layers/boundary/create.sql b/basemap/layers/boundary/create.sql index 17eba85a8..096903b68 100644 --- a/basemap/layers/boundary/create.sql +++ b/basemap/layers/boundary/create.sql @@ -16,4 +16,5 @@ CREATE OR REPLACE VIEW osm_boundary AS SELECT id, tags, geom FROM osm_way -WHERE tags ? 'boundary'; \ No newline at end of file +WHERE geom IS NOT NULL + AND tags ? 'boundary'; \ No newline at end of file diff --git a/basemap/layers/boundary/tileset.js b/basemap/layers/boundary/tileset.js index 7e731a3c5..66a990ca2 100644 --- a/basemap/layers/boundary/tileset.js +++ b/basemap/layers/boundary/tileset.js @@ -21,7 +21,7 @@ export default { minzoom: 13, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_way WHERE tags ? 'boundary'", + "SELECT id, tags, geom FROM osm_boundary", }, ], } diff --git a/basemap/layers/building/create.sql b/basemap/layers/building/create.sql index dd0145218..0ffa20118 100644 --- a/basemap/layers/building/create.sql +++ b/basemap/layers/building/create.sql @@ -14,30 +14,29 @@ -- limitations under the License. CREATE OR REPLACE VIEW osm_building AS -SELECT - id, - tags - || jsonb_build_object('extrusion:base', - CASE - WHEN tags ? 'min_height' - THEN convert_to_number(tags ->> 'min_height', 0) - WHEN tags ? 'building:min_height' - THEN convert_to_number(tags ->> 'building:min_height', 0) - WHEN tags ? 'building:min_level' - THEN convert_to_number(tags ->> 'building:min_level', 0) * 3 - ELSE 0 - END) - || jsonb_build_object('extrusion:height', - CASE - WHEN tags ? 'height' - THEN convert_to_number(tags ->> 'height', 6) - WHEN tags ? 'building:height' - THEN convert_to_number(tags ->> 'building:height', 6) - WHEN tags ? 'building:levels' - THEN convert_to_number(tags ->> 'building:levels', 2) * 3 - ELSE 6 - END) as tags, - geom +SELECT id, + tags + || jsonb_build_object('extrusion:base', + CASE + WHEN tags ? 'min_height' + THEN convert_to_number(tags ->> 'min_height', 0) + WHEN tags ? 'building:min_height' + THEN convert_to_number(tags ->> 'building:min_height', 0) + WHEN tags ? 'building:min_level' + THEN convert_to_number(tags ->> 'building:min_level', 0) * 3 + ELSE 0 + END) + || jsonb_build_object('extrusion:height', + CASE + WHEN tags ? 'height' + THEN convert_to_number(tags ->> 'height', 6) + WHEN tags ? 'building:height' + THEN convert_to_number(tags ->> 'building:height', 6) + WHEN tags ? 'building:levels' + THEN convert_to_number(tags ->> 'building:levels', 2) * 3 + ELSE 6 + END) as tags, + geom FROM osm_polygon WHERE (tags ? 'building' OR tags ? 'building:part') AND ((NOT tags ? 'layer') OR convert_to_number(tags ->> 'layer', 0) >= 0) \ No newline at end of file diff --git a/basemap/layers/linestring/create.sql b/basemap/layers/linestring/create.sql index 28aee067b..01e8a1208 100644 --- a/basemap/layers/linestring/create.sql +++ b/basemap/layers/linestring/create.sql @@ -16,8 +16,10 @@ DROP MATERIALIZED VIEW IF EXISTS osm_linestring CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_linestring AS SELECT id, tags, geom, changeset -FROM osm_way LEFT JOIN osm_member ON id = member_ref -WHERE ST_GeometryType( osm_way.geom) = 'ST_LineString' +FROM osm_way + LEFT JOIN osm_member ON id = member_ref +WHERE geom IS NOT NULL + AND ST_GeometryType(osm_way.geom) = 'ST_LineString' AND tags != '{}' AND member_ref IS NULL WITH NO DATA; \ No newline at end of file diff --git a/basemap/layers/man_made/create.sql b/basemap/layers/man_made/create.sql index 96e7af151..9e8641f78 100644 --- a/basemap/layers/man_made/create.sql +++ b/basemap/layers/man_made/create.sql @@ -16,4 +16,5 @@ CREATE OR REPLACE VIEW osm_man_made AS SELECT id, tags, geom FROM osm_way -WHERE tags ? 'man_made' \ No newline at end of file +WHERE geom IS NOT NULL + AND tags ? 'man_made' \ No newline at end of file diff --git a/basemap/layers/man_made/tileset.js b/basemap/layers/man_made/tileset.js index d3b32f8c9..3f81bcbaa 100644 --- a/basemap/layers/man_made/tileset.js +++ b/basemap/layers/man_made/tileset.js @@ -21,7 +21,7 @@ export default { { "minzoom": 14, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ? 'man_made'" + "sql": "SELECT id, tags, geom FROM osm_man_made" } ] } diff --git a/basemap/layers/point/create.sql b/basemap/layers/point/create.sql index f226cfdd3..91ad9c374 100644 --- a/basemap/layers/point/create.sql +++ b/basemap/layers/point/create.sql @@ -1,7 +1,8 @@ CREATE OR REPLACE VIEW osm_point AS SELECT id, tags, geom FROM osm_node -WHERE tags != '{}'; +WHERE geom IS NOT NULL + AND tags != '{}'; CREATE OR REPLACE VIEW osm_point_z20 AS SELECT * @@ -35,102 +36,107 @@ DROP MATERIALIZED VIEW IF EXISTS osm_point_z13; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z13 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','municipality','city','town','village','quarter','hamlet']) - OR (tags->>'natural') = ANY (ARRAY['peak','volcano','spring']) - OR (tags->>'highway') = 'motorway_junction' - OR (tags->>'tourism') = 'wilderness_hut' - OR (tags->>'waterway') = 'waterfall' - OR (tags->>'railway') = 'level_crossing' +WHERE (tags ->> 'place') = ANY + (ARRAY ['region','province','district','county','municipality','city','town','village','quarter','hamlet']) + OR (tags ->> 'natural') = ANY (ARRAY ['peak','volcano','spring']) + OR (tags ->> 'highway') = 'motorway_junction' + OR (tags ->> 'tourism') = 'wilderness_hut' + OR (tags ->> 'waterway') = 'waterfall' + OR (tags ->> 'railway') = 'level_crossing' WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z12; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z12 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','municipality','city','town','village']) - OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) - OR (tags->>'highway') = 'motorway_junction' - OR (tags->>'tourism') = 'wilderness_hut' - OR (tags->>'waterway') = 'waterfall' +WHERE (tags ->> 'place') = ANY (ARRAY ['region','province','district','county','municipality','city','town','village']) + OR (tags ->> 'natural') = ANY (ARRAY ['peak','volcano']) + OR (tags ->> 'highway') = 'motorway_junction' + OR (tags ->> 'tourism') = 'wilderness_hut' + OR (tags ->> 'waterway') = 'waterfall' WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z11; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z11 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town','village']) - OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) - OR (tags->>'highway') = 'motorway_junction' +WHERE (tags ->> 'place') = ANY + (ARRAY ['country','state','region','province','district','county','municipality','city','town','village']) + OR (tags ->> 'natural') = ANY (ARRAY ['peak','volcano']) + OR (tags ->> 'highway') = 'motorway_junction' WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z10; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z10 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) - OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) - OR (tags->>'highway') = 'motorway_junction' +WHERE (tags ->> 'place') = ANY + (ARRAY ['country','state','region','province','district','county','municipality','city','town']) + OR (tags ->> 'natural') = ANY (ARRAY ['peak','volcano']) + OR (tags ->> 'highway') = 'motorway_junction' WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z9; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z9 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) +WHERE (tags ->> 'place') = ANY + (ARRAY ['country','state','region','province','district','county','municipality','city','town']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z8; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z8 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) +WHERE (tags ->> 'place') = ANY + (ARRAY ['country','state','region','province','district','county','municipality','city','town']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z7; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z7 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']) +WHERE (tags ->> 'place') = ANY (ARRAY ['country','city','sea','state','county']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z6; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z6 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']) +WHERE (tags ->> 'place') = ANY (ARRAY ['country','city','sea','state','county']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z5; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z5 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']) +WHERE (tags ->> 'place') = ANY (ARRAY ['country','city','sea','state','county']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z4; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z4 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']) +WHERE (tags ->> 'place') = ANY (ARRAY ['country','city','sea']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z3; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z3 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']) +WHERE (tags ->> 'place') = ANY (ARRAY ['country','city','sea']) WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z2; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z2 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = 'country' +WHERE (tags ->> 'place') = 'country' WITH NO DATA; DROP MATERIALIZED VIEW IF EXISTS osm_point_z1; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z1 AS SELECT id, tags, geom FROM osm_point -WHERE (tags->>'place') = 'country' +WHERE (tags ->> 'place') = 'country' WITH NO DATA; diff --git a/basemap/layers/polygon/create.sql b/basemap/layers/polygon/create.sql index 6cba6e162..50bc5a12a 100644 --- a/basemap/layers/polygon/create.sql +++ b/basemap/layers/polygon/create.sql @@ -16,18 +16,22 @@ DROP MATERIALIZED VIEW IF EXISTS osm_polygon CASCADE; CREATE MATERIALIZED VIEW IF NOT EXISTS osm_polygon AS SELECT id, tags, geom -FROM osm_way LEFT JOIN osm_member ON id = member_ref -WHERE ST_GeometryType( osm_way.geom) = 'ST_Polygon' +FROM osm_way + LEFT JOIN osm_member ON id = member_ref +WHERE geom IS NOT NULL + AND ST_GeometryType(osm_way.geom) = 'ST_Polygon' AND tags != '{}' AND member_ref IS NULL UNION SELECT id, tags, geom FROM osm_relation -WHERE ST_GeometryType( osm_relation.geom) = 'ST_Polygon' +WHERE geom IS NOT NULL + AND ST_GeometryType(osm_relation.geom) = 'ST_Polygon' AND tags != '{}' UNION SELECT id, tags, (st_dump(geom)).geom as geom FROM osm_relation -WHERE ST_GeometryType( osm_relation.geom) = 'ST_MultiPolygon' +WHERE geom IS NOT NULL + AND ST_GeometryType(osm_relation.geom) = 'ST_MultiPolygon' AND tags != '{}' WITH NO DATA; \ No newline at end of file diff --git a/basemap/layers/power/create.sql b/basemap/layers/power/create.sql index 398e97a5d..8781ff798 100644 --- a/basemap/layers/power/create.sql +++ b/basemap/layers/power/create.sql @@ -1,2 +1,5 @@ CREATE OR REPLACE VIEW osm_power AS -SELECT id, tags, geom FROM osm_way WHERE tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation'); \ No newline at end of file +SELECT id, tags, geom +FROM osm_way +WHERE geom IS NOT NULL + AND tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation'); \ No newline at end of file diff --git a/basemap/layers/power/tileset.js b/basemap/layers/power/tileset.js index 7cd4aeb7b..e2cf1746f 100644 --- a/basemap/layers/power/tileset.js +++ b/basemap/layers/power/tileset.js @@ -20,7 +20,7 @@ export default { { "minzoom": 13, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation')" + "sql": "SELECT id, tags, geom FROM osm_power" } ] } diff --git a/basemap/layers/tourism/create.sql b/basemap/layers/tourism/create.sql index 2c40ebd72..0c5dd6e4b 100644 --- a/basemap/layers/tourism/create.sql +++ b/basemap/layers/tourism/create.sql @@ -16,4 +16,5 @@ CREATE OR REPLACE VIEW osm_tourism AS SELECT id, tags, geom FROM osm_relation -WHERE tags ? 'tourism'; \ No newline at end of file +WHERE geom IS NOT NULL + AND tags ? 'tourism'; \ No newline at end of file diff --git a/basemap/layers/tourism/tileset.js b/basemap/layers/tourism/tileset.js index 689e67456..a6fca2451 100644 --- a/basemap/layers/tourism/tileset.js +++ b/basemap/layers/tourism/tileset.js @@ -20,7 +20,7 @@ export default { { "minzoom": 14, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_relation_z$zoom WHERE tags ? 'tourism'" + "sql": "SELECT id, tags, geom FROM osm_tourism" } ] }
