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 fef713579ef90e48a33a39fc9881aee649c9915f Author: Bertil Chapuis <[email protected]> AuthorDate: Mon Dec 30 16:55:30 2024 +0100 Improve osm naming convention in basemap - prefix with osm_ - use singular names --- basemap/layers/aerialway/create.sql | 4 ++-- basemap/layers/aerialway/tileset.js | 2 +- basemap/layers/aeroway/create.sql | 4 ++-- basemap/layers/aeroway/tileset.js | 2 +- basemap/layers/amenity/create.sql | 4 ++-- basemap/layers/amenity/tileset.js | 2 +- basemap/layers/attraction/create.sql | 4 ++-- basemap/layers/attraction/tileset.js | 2 +- basemap/layers/barrier/create.sql | 4 ++-- basemap/layers/barrier/tileset.js | 2 +- basemap/layers/boundary/create.sql | 4 ++-- basemap/layers/boundary/tileset.js | 2 +- basemap/layers/building/create.sql | 2 +- basemap/layers/building/tileset.js | 4 ++-- basemap/layers/highway/tileset.js | 2 +- basemap/layers/landuse/tileset.js | 4 ++-- basemap/layers/leisure/tileset.js | 4 ++-- basemap/layers/linestring/create.sql | 4 ++-- basemap/layers/linestring/prepare.sql | 4 ++-- basemap/layers/man_made/create.sql | 4 +++- basemap/layers/man_made/tileset.js | 2 +- basemap/layers/member/create.sql | 2 +- basemap/layers/member/prepare.sql | 2 +- basemap/layers/natural/create.sql | 2 ++ basemap/layers/natural/tileset.js | 4 ++-- basemap/layers/node/create.sql | 2 +- basemap/layers/point/create.sql | 4 ++-- basemap/layers/point/simplify.sql | 40 +++++++++++++++++------------------ basemap/layers/polygon/create.sql | 12 +++++------ basemap/layers/polygon/prepare.sql | 12 +++++------ basemap/layers/power/create.sql | 4 ++-- basemap/layers/power/tileset.js | 2 +- basemap/layers/railway/create.sql | 2 +- basemap/layers/railway/prepare.sql | 2 +- basemap/layers/railway/tileset.js | 2 +- basemap/layers/relation/create.sql | 2 +- basemap/layers/route/create.sql | 2 +- basemap/layers/route/prepare.sql | 2 +- basemap/layers/route/tileset.js | 2 +- basemap/layers/tourism/create.sql | 2 +- basemap/layers/tourism/tileset.js | 2 +- basemap/layers/waterway/tileset.js | 2 +- basemap/layers/way/create.sql | 2 +- 43 files changed, 89 insertions(+), 85 deletions(-) diff --git a/basemap/layers/aerialway/create.sql b/basemap/layers/aerialway/create.sql index 6e63c5bbb..16e815150 100644 --- a/basemap/layers/aerialway/create.sql +++ b/basemap/layers/aerialway/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW aerialway AS -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'aerialway'; \ No newline at end of file +CREATE VIEW osm_aerialway AS +SELECT id, tags, geom FROM osm_way WHERE tags ? 'aerialway'; \ No newline at end of file diff --git a/basemap/layers/aerialway/tileset.js b/basemap/layers/aerialway/tileset.js index 2ae243c32..9672034b4 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_ways WHERE tags ? 'aerialway'", + "SELECT id, tags, geom FROM osm_way WHERE tags ? 'aerialway'", }, ], } diff --git a/basemap/layers/aeroway/create.sql b/basemap/layers/aeroway/create.sql index 51e156e6b..dcbb84c60 100644 --- a/basemap/layers/aeroway/create.sql +++ b/basemap/layers/aeroway/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW aeroway AS -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'aeroway' \ No newline at end of file +CREATE VIEW osm_aeroway AS +SELECT id, tags, geom FROM osm_way WHERE tags ? 'aeroway' \ No newline at end of file diff --git a/basemap/layers/aeroway/tileset.js b/basemap/layers/aeroway/tileset.js index 76d3ccef2..f17cdb660 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_ways WHERE tags ? 'aeroway'", + "SELECT id, tags, geom FROM osm_way WHERE tags ? 'aeroway'", }, ], } diff --git a/basemap/layers/amenity/create.sql b/basemap/layers/amenity/create.sql index 653042d82..2c4581079 100644 --- a/basemap/layers/amenity/create.sql +++ b/basemap/layers/amenity/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW amenity AS -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'amenity'; \ No newline at end of file +CREATE VIEW osm_amenity AS +SELECT id, tags, geom FROM osm_way WHERE tags ? 'amenity'; \ No newline at end of file diff --git a/basemap/layers/amenity/tileset.js b/basemap/layers/amenity/tileset.js index 2c374af46..b0180a792 100644 --- a/basemap/layers/amenity/tileset.js +++ b/basemap/layers/amenity/tileset.js @@ -20,7 +20,7 @@ export default { { minzoom: 13, maxzoom: 20, - sql: "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'amenity'", + sql: "SELECT id, tags, geom FROM osm_way WHERE tags ? 'amenity'", }, ], } diff --git a/basemap/layers/attraction/create.sql b/basemap/layers/attraction/create.sql index d2eaee111..d79901e40 100644 --- a/basemap/layers/attraction/create.sql +++ b/basemap/layers/attraction/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW attraction AS -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'attraction'; \ No newline at end of file +CREATE VIEW osm_attraction AS +SELECT id, tags, geom FROM osm_way WHERE tags ? 'attraction'; \ No newline at end of file diff --git a/basemap/layers/attraction/tileset.js b/basemap/layers/attraction/tileset.js index 18179ab43..a53cdca94 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_ways WHERE tags ? 'attraction'", + sql: "SELECT id, tags, geom FROM osm_way WHERE tags ? 'attraction'", }, ], } diff --git a/basemap/layers/barrier/create.sql b/basemap/layers/barrier/create.sql index aaef0318a..30fc680cb 100644 --- a/basemap/layers/barrier/create.sql +++ b/basemap/layers/barrier/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW barrier AS -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'barrier'; \ No newline at end of file +CREATE VIEW osm_barrier AS +SELECT id, tags, geom FROM osm_way WHERE tags ? 'barrier'; \ No newline at end of file diff --git a/basemap/layers/barrier/tileset.js b/basemap/layers/barrier/tileset.js index 06c567a19..7081a23c7 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_ways WHERE tags ? 'barrier'", + "SELECT id, tags, geom FROM osm_way WHERE tags ? 'barrier'", }, ], } diff --git a/basemap/layers/boundary/create.sql b/basemap/layers/boundary/create.sql index ee651eca6..ea64f9522 100644 --- a/basemap/layers/boundary/create.sql +++ b/basemap/layers/boundary/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW boundary AS -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'boundary'; \ No newline at end of file +CREATE VIEW osm_boundary AS +SELECT id, tags, geom FROM osm_way WHERE tags ? 'boundary'; \ No newline at end of file diff --git a/basemap/layers/boundary/tileset.js b/basemap/layers/boundary/tileset.js index ad3eb3c5f..7e731a3c5 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_ways WHERE tags ? 'boundary'", + "SELECT id, tags, geom FROM osm_way WHERE tags ? 'boundary'", }, ], } diff --git a/basemap/layers/building/create.sql b/basemap/layers/building/create.sql index 8ea3159e7..6ed153ee0 100644 --- a/basemap/layers/building/create.sql +++ b/basemap/layers/building/create.sql @@ -1,4 +1,4 @@ -CREATE VIEW building AS +CREATE VIEW osm_building AS SELECT id, tags diff --git a/basemap/layers/building/tileset.js b/basemap/layers/building/tileset.js index b57e588bb..f68e137ba 100644 --- a/basemap/layers/building/tileset.js +++ b/basemap/layers/building/tileset.js @@ -46,7 +46,7 @@ export default { ELSE 6 END) as tags, geom - FROM osm_ways + FROM osm_way WHERE (tags ? 'building' OR tags ? 'building:part') AND ((NOT tags ? 'layer') OR convert_to_number(tags ->> 'layer', 0) >= 0)`, }, { @@ -77,7 +77,7 @@ export default { ELSE 6 END) as tags, geom - FROM osm_relations + FROM osm_relation WHERE (tags ? 'building' OR tags ? 'building:part') AND ((NOT tags ? 'layer') OR convert_to_number(tags ->> 'layer', 0) >= 0)`, }, ], diff --git a/basemap/layers/highway/tileset.js b/basemap/layers/highway/tileset.js index c29abd69f..bb1a0a240 100644 --- a/basemap/layers/highway/tileset.js +++ b/basemap/layers/highway/tileset.js @@ -28,7 +28,7 @@ export default { minzoom: 14, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'highway'", + "SELECT id, tags, geom FROM osm_way WHERE tags ? 'highway'", }, ], } diff --git a/basemap/layers/landuse/tileset.js b/basemap/layers/landuse/tileset.js index d39132da4..ef503eeab 100644 --- a/basemap/layers/landuse/tileset.js +++ b/basemap/layers/landuse/tileset.js @@ -27,12 +27,12 @@ export default { { minzoom: 13, maxzoom: 20, - sql: "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'landuse'" + sql: "SELECT id, tags, geom FROM osm_way WHERE tags ? 'landuse'" }, { minzoom: 13, maxzoom: 20, - sql: "SELECT id, tags, geom FROM osm_relations WHERE tags ? 'landuse'" + sql: "SELECT id, tags, geom FROM osm_relation WHERE tags ? 'landuse'" } ], } diff --git a/basemap/layers/leisure/tileset.js b/basemap/layers/leisure/tileset.js index bef2910c0..7dd244377 100644 --- a/basemap/layers/leisure/tileset.js +++ b/basemap/layers/leisure/tileset.js @@ -28,13 +28,13 @@ export default { minzoom: 13, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'leisure'", + "SELECT id, tags, geom FROM osm_way WHERE tags ? 'leisure'", }, { minzoom: 13, maxzoom: 20, sql: - "SELECT id, tags, geom FROM osm_relations WHERE tags ? 'leisure'", + "SELECT id, tags, geom FROM osm_relation WHERE tags ? 'leisure'", }, ], } diff --git a/basemap/layers/linestring/create.sql b/basemap/layers/linestring/create.sql index d8c0b7d22..4cf0976d4 100644 --- a/basemap/layers/linestring/create.sql +++ b/basemap/layers/linestring/create.sql @@ -1,6 +1,6 @@ CREATE MATERIALIZED VIEW osm_linestring AS SELECT id, tags, geom, changeset -FROM osm_ways LEFT JOIN osm_member ON id = member_ref -WHERE ST_GeometryType(osm_ways.geom) = 'ST_LineString' +FROM osm_way LEFT JOIN osm_member ON id = member_ref +WHERE ST_GeometryType( osm_way.geom) = 'ST_LineString' AND tags != '{}' AND member_ref IS NULL; \ No newline at end of file diff --git a/basemap/layers/linestring/prepare.sql b/basemap/layers/linestring/prepare.sql index fb7b1cf3e..7134fd5e2 100644 --- a/basemap/layers/linestring/prepare.sql +++ b/basemap/layers/linestring/prepare.sql @@ -17,9 +17,9 @@ DROP MATERIALIZED VIEW IF EXISTS osm_linestring CASCADE; CREATE MATERIALIZED VIEW osm_linestring AS SELECT id, tags, geom, changeset -FROM osm_ways +FROM osm_way LEFT JOIN osm_member ON id = member_ref -WHERE ST_GeometryType(osm_ways.geom) = 'ST_LineString' +WHERE ST_GeometryType( osm_way.geom) = 'ST_LineString' AND tags != '{}' AND member_ref IS NULL; diff --git a/basemap/layers/man_made/create.sql b/basemap/layers/man_made/create.sql index c7ccfd363..82cd09f40 100644 --- a/basemap/layers/man_made/create.sql +++ b/basemap/layers/man_made/create.sql @@ -1 +1,3 @@ -SELECT id, tags, geom FROM osm_ways WHERE tags ? 'man_made' \ No newline at end of file +CREATE VIEW osm_man_made AS +SELECT id, tags, geom FROM osm_way +WHERE 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 a54e22d4b..d3b32f8c9 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_ways WHERE tags ? 'man_made'" + "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ? 'man_made'" } ] } diff --git a/basemap/layers/member/create.sql b/basemap/layers/member/create.sql index e8ea265f2..3f3f505f1 100644 --- a/basemap/layers/member/create.sql +++ b/basemap/layers/member/create.sql @@ -1,6 +1,6 @@ CREATE MATERIALIZED VIEW osm_member AS SELECT DISTINCT member_ref as member_ref -FROM osm_relations, +FROM osm_relation, unnest(member_types, member_refs) AS way(member_type, member_ref) WHERE geom IS NOT NULL AND member_type = 1 diff --git a/basemap/layers/member/prepare.sql b/basemap/layers/member/prepare.sql index 7d11ddd8e..6367792e1 100644 --- a/basemap/layers/member/prepare.sql +++ b/basemap/layers/member/prepare.sql @@ -16,7 +16,7 @@ DROP MATERIALIZED VIEW IF EXISTS osm_member CASCADE; CREATE MATERIALIZED VIEW osm_member AS SELECT DISTINCT member_ref as member_ref -FROM osm_relations, unnest(member_types, member_refs) AS way(member_type, member_ref) +FROM osm_relation, unnest(member_types, member_refs) AS way(member_type, member_ref) WHERE geom IS NOT NULL AND member_type = 1 AND tags ->> 'type' = 'multipolygon' diff --git a/basemap/layers/natural/create.sql b/basemap/layers/natural/create.sql index 888c17de0..d24abb483 100644 --- a/basemap/layers/natural/create.sql +++ b/basemap/layers/natural/create.sql @@ -33,6 +33,7 @@ SELECT st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY natural_value) AS cluster FROM osm_natural_filtered WHERE geom IS NOT NULL; + CREATE INDEX IF NOT EXISTS osm_natural_clustered_geom_idx ON osm_natural_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_clustered_tags_idx ON osm_natural_clustered (natural_value); @@ -61,5 +62,6 @@ SELECT jsonb_build_object('natural', natural_value) AS tags, geom FROM osm_natural_exploded; + CREATE INDEX IF NOT EXISTS osm_natural_geom_idx ON osm_natural_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_tags_idx ON osm_natural_filtered USING GIN (natural_value); diff --git a/basemap/layers/natural/tileset.js b/basemap/layers/natural/tileset.js index 41c65e7f4..2d2f6fce7 100644 --- a/basemap/layers/natural/tileset.js +++ b/basemap/layers/natural/tileset.js @@ -30,12 +30,12 @@ export default { { "minzoom": 13, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'natural' AND tags ->> 'natural' NOT IN ('coastline')" + "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ? 'natural' AND tags ->> 'natural' NOT IN ('coastline')" }, { "minzoom": 13, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_relations WHERE tags ? 'natural' AND tags ->> 'natural' NOT IN ('coastline')" + "sql": "SELECT id, tags, geom FROM osm_relation WHERE tags ? 'natural' AND tags ->> 'natural' NOT IN ('coastline')" } ] } diff --git a/basemap/layers/node/create.sql b/basemap/layers/node/create.sql index c44b3d12f..c324031dd 100644 --- a/basemap/layers/node/create.sql +++ b/basemap/layers/node/create.sql @@ -1,4 +1,4 @@ -CREATE TABLE IF NOT EXISTS osm_nodes +CREATE TABLE IF NOT EXISTS osm_node ( id int8 PRIMARY KEY, version int, diff --git a/basemap/layers/point/create.sql b/basemap/layers/point/create.sql index 5077eaef4..ea3effdf1 100644 --- a/basemap/layers/point/create.sql +++ b/basemap/layers/point/create.sql @@ -1,6 +1,6 @@ -CREATE VIEW point AS +CREATE VIEW osm_point AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z20 AS SELECT * FROM point; diff --git a/basemap/layers/point/simplify.sql b/basemap/layers/point/simplify.sql index 879bec222..5ca51a019 100644 --- a/basemap/layers/point/simplify.sql +++ b/basemap/layers/point/simplify.sql @@ -15,101 +15,101 @@ CREATE VIEW osm_point_z20 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z19 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z18 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z17 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z16 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z15 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE VIEW osm_point_z14 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}'; CREATE MATERIALIZED VIEW osm_point_z13 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND (tags ->> 'place' IN ('region', 'province', 'district', 'county', 'municipality', 'city', 'town', 'village', 'quarter', 'hamlet')) OR (tags ->> 'natural' IN ('peak', 'volcano')) OR (tags ->> 'highway' IN ('motorway_junction')) OR (tags ->> 'tourism' IN ('wilderness_hut')) OR (tags ->> 'waterway' IN ('waterfall')) OR (tags ->> 'natural' IN ('spring')) OR (tags ->> 'railway' IN ('level_crossing')); CREATE MATERIALIZED VIEW osm_point_z12 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND (tags ->> 'place' IN ('region', 'province', 'district', 'county', 'municipality', 'city', 'town', 'village')) OR (tags ->> 'natural' IN ('peak', 'volcano')) OR (tags ->> 'highway' IN ('motorway_junction')) OR (tags ->> 'tourism' IN ('wilderness_hut')) OR (tags ->> 'waterway' IN ('waterfall')); CREATE MATERIALIZED VIEW osm_point_z11 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND (tags ->> 'place' IN ('country', 'state', 'region', 'province', 'district', 'county', 'municipality', 'city', 'town', 'village')) OR (tags ->> 'natural' IN ('peak', 'volcano')) OR (tags ->> 'highway' IN ('motorway_junction')); CREATE MATERIALIZED VIEW osm_point_z10 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND (tags ->> 'place' IN ('country', 'state', 'region', 'province', 'district', 'county', 'municipality', 'city', 'town')) OR (tags ->> 'natural' IN ('peak', 'volcano')) OR (tags ->> 'highway' IN ('motorway_junction')); CREATE MATERIALIZED VIEW osm_point_z9 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'state', 'region', 'province', 'district', 'county', 'municipality', 'city', 'town'); CREATE MATERIALIZED VIEW osm_point_z8 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'state', 'region', 'province', 'district', 'county', 'municipality', 'city', 'town'); CREATE MATERIALIZED VIEW osm_point_z7 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea', 'state', 'county'); CREATE MATERIALIZED VIEW osm_point_z6 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea', 'state', 'county'); CREATE MATERIALIZED VIEW osm_point_z5 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea', 'state', 'county'); CREATE MATERIALIZED VIEW osm_point_z4 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea'); CREATE MATERIALIZED VIEW osm_point_z3 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea'); CREATE MATERIALIZED VIEW osm_point_z2 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country'); CREATE MATERIALIZED VIEW osm_point_z1 AS SELECT id, tags, geom -FROM osm_nodes +FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country'); \ No newline at end of file diff --git a/basemap/layers/polygon/create.sql b/basemap/layers/polygon/create.sql index 500cb1604..2e824fc38 100644 --- a/basemap/layers/polygon/create.sql +++ b/basemap/layers/polygon/create.sql @@ -1,16 +1,16 @@ CREATE MATERIALIZED VIEW osm_polygon AS SELECT id, tags, geom -FROM osm_ways LEFT JOIN osm_member ON id = member_ref -WHERE ST_GeometryType(osm_ways.geom) = 'ST_Polygon' +FROM osm_way LEFT JOIN osm_member ON id = member_ref +WHERE ST_GeometryType( osm_way.geom) = 'ST_Polygon' AND tags != '{}' AND member_ref IS NULL UNION SELECT id, tags, geom -FROM osm_relations -WHERE ST_GeometryType(osm_relations.geom) = 'ST_Polygon' +FROM osm_relation +WHERE ST_GeometryType( osm_relation.geom) = 'ST_Polygon' AND tags != '{}' UNION SELECT id, tags, (st_dump(geom)).geom as geom -FROM osm_relations -WHERE ST_GeometryType(osm_relations.geom) = 'ST_MultiPolygon' +FROM osm_relation +WHERE ST_GeometryType( osm_relation.geom) = 'ST_MultiPolygon' AND tags != '{}'; \ No newline at end of file diff --git a/basemap/layers/polygon/prepare.sql b/basemap/layers/polygon/prepare.sql index 301e628e4..7de1d122c 100644 --- a/basemap/layers/polygon/prepare.sql +++ b/basemap/layers/polygon/prepare.sql @@ -15,17 +15,17 @@ CREATE MATERIALIZED VIEW osm_polygon AS SELECT id, tags, geom -FROM osm_ways LEFT JOIN osm_member ON id = member_ref -WHERE ST_GeometryType(osm_ways.geom) = 'ST_Polygon' +FROM osm_way LEFT JOIN osm_member ON id = member_ref +WHERE ST_GeometryType( osm_way.geom) = 'ST_Polygon' AND tags != '{}' AND member_ref IS NULL UNION SELECT id, tags, geom -FROM osm_relations -WHERE ST_GeometryType(osm_relations.geom) = 'ST_Polygon' +FROM osm_relation +WHERE ST_GeometryType( osm_relation.geom) = 'ST_Polygon' AND tags != '{}' UNION SELECT id, tags, (st_dump(geom)).geom as geom -FROM osm_relations -WHERE ST_GeometryType(osm_relations.geom) = 'ST_MultiPolygon' +FROM osm_relation +WHERE ST_GeometryType( osm_relation.geom) = 'ST_MultiPolygon' AND tags != '{}'; \ No newline at end of file diff --git a/basemap/layers/power/create.sql b/basemap/layers/power/create.sql index 771556645..4e89ce8ee 100644 --- a/basemap/layers/power/create.sql +++ b/basemap/layers/power/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW power AS -SELECT id, tags, geom FROM osm_ways WHERE tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation'); \ No newline at end of file +CREATE 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 diff --git a/basemap/layers/power/tileset.js b/basemap/layers/power/tileset.js index e651209db..7cd4aeb7b 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_ways WHERE tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation')" + "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation')" } ] } diff --git a/basemap/layers/railway/create.sql b/basemap/layers/railway/create.sql index 9265f962d..321e294a0 100644 --- a/basemap/layers/railway/create.sql +++ b/basemap/layers/railway/create.sql @@ -3,6 +3,6 @@ SELECT id, tags, geom FROM (SELECT min(id) as id, jsonb_build_object('railway', tags -> 'railway', 'service', tags -> 'service') as tags, (st_dump(st_linemerge(st_collect(geom)))).geom as geom - FROM osm_ways + FROM osm_way WHERE tags ->> 'railway' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') GROUP BY tags -> 'railway', tags -> 'service') AS mergedDirective; \ No newline at end of file diff --git a/basemap/layers/railway/prepare.sql b/basemap/layers/railway/prepare.sql index e9f9c029b..038b9bb99 100644 --- a/basemap/layers/railway/prepare.sql +++ b/basemap/layers/railway/prepare.sql @@ -20,7 +20,7 @@ FROM ( min(id) as id, jsonb_build_object('railway', tags -> 'railway', 'service', tags -> 'service') as tags, (st_dump(st_linemerge(st_collect(geom)))).geom as geom - FROM osm_ways + FROM osm_way WHERE tags ->> 'railway' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') GROUP BY tags -> 'railway', tags -> 'service' ) AS mergedDirective; diff --git a/basemap/layers/railway/tileset.js b/basemap/layers/railway/tileset.js index d847a0311..249b34c8b 100644 --- a/basemap/layers/railway/tileset.js +++ b/basemap/layers/railway/tileset.js @@ -25,7 +25,7 @@ export default { { "minzoom": 13, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'railway'" + "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ? 'railway'" } ] } diff --git a/basemap/layers/relation/create.sql b/basemap/layers/relation/create.sql index cd768f414..9a2bc13ba 100644 --- a/basemap/layers/relation/create.sql +++ b/basemap/layers/relation/create.sql @@ -1,4 +1,4 @@ -CREATE TABLE IF NOT EXISTS osm_relations +CREATE TABLE IF NOT EXISTS osm_relation ( id int8 PRIMARY KEY, version int, diff --git a/basemap/layers/route/create.sql b/basemap/layers/route/create.sql index 8340d3b39..a996564c2 100644 --- a/basemap/layers/route/create.sql +++ b/basemap/layers/route/create.sql @@ -20,7 +20,7 @@ FROM ( min(id) as id, jsonb_build_object('route', tags -> 'route') as tags, (st_dump(st_linemerge(st_collect(geom)))).geom as geom - FROM osm_ways + FROM osm_way WHERE tags ->> 'route' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') AND NOT tags ? 'service' GROUP BY tags -> 'route' diff --git a/basemap/layers/route/prepare.sql b/basemap/layers/route/prepare.sql index e7881c161..c70c24ce5 100644 --- a/basemap/layers/route/prepare.sql +++ b/basemap/layers/route/prepare.sql @@ -22,7 +22,7 @@ FROM ( min(id) as id, jsonb_build_object('route', tags -> 'route') as tags, (st_dump(st_linemerge(st_collect(geom)))).geom as geom - FROM osm_ways + FROM osm_way WHERE tags ->> 'route' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') AND NOT tags ? 'service' GROUP BY tags -> 'route' diff --git a/basemap/layers/route/tileset.js b/basemap/layers/route/tileset.js index 9d5481aa9..b5714de16 100644 --- a/basemap/layers/route/tileset.js +++ b/basemap/layers/route/tileset.js @@ -25,7 +25,7 @@ export default { { "minzoom": 13, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'route'" + "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ? 'route'" } ] } diff --git a/basemap/layers/tourism/create.sql b/basemap/layers/tourism/create.sql index a0d8a429e..ad00b54fa 100644 --- a/basemap/layers/tourism/create.sql +++ b/basemap/layers/tourism/create.sql @@ -1,2 +1,2 @@ CREATE VIEW osm_tourism AS -SELECT id, tags, geom FROM osm_relations_z$zoom WHERE tags ? 'tourism'; \ No newline at end of file +SELECT id, tags, geom FROM osm_relation WHERE tags ? 'tourism'; \ No newline at end of file diff --git a/basemap/layers/tourism/tileset.js b/basemap/layers/tourism/tileset.js index f99681c96..689e67456 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_relations_z$zoom WHERE tags ? 'tourism'" + "sql": "SELECT id, tags, geom FROM osm_relation_z$zoom WHERE tags ? 'tourism'" } ] } diff --git a/basemap/layers/waterway/tileset.js b/basemap/layers/waterway/tileset.js index 241874324..a95e49e82 100644 --- a/basemap/layers/waterway/tileset.js +++ b/basemap/layers/waterway/tileset.js @@ -30,7 +30,7 @@ export default { { "minzoom": 13, "maxzoom": 20, - "sql": "SELECT id, tags, geom FROM osm_ways WHERE tags ? 'waterway'" + "sql": "SELECT id, tags, geom FROM osm_way WHERE tags ? 'waterway'" } ] } diff --git a/basemap/layers/way/create.sql b/basemap/layers/way/create.sql index 077a87aa2..ce72df9a1 100644 --- a/basemap/layers/way/create.sql +++ b/basemap/layers/way/create.sql @@ -1,4 +1,4 @@ -CREATE TABLE IF NOT EXISTS osm_ways +CREATE TABLE IF NOT EXISTS osm_way ( id int8 PRIMARY KEY, version int,
