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 e66b0adf32857afdca11ddc2e6254fcb7fec6c5b Author: Bertil Chapuis <[email protected]> AuthorDate: Mon Dec 30 17:18:08 2024 +0100 Improve the queries in the create scripts - Use CREATE OR REPLACE for views - USE IF NOT EXISTS for materialized views --- basemap/layers/aerialway/create.sql | 2 +- basemap/layers/aeroway/create.sql | 2 +- basemap/layers/amenity/create.sql | 2 +- basemap/layers/attraction/create.sql | 2 +- basemap/layers/barrier/create.sql | 2 +- basemap/layers/boundary/create.sql | 2 +- basemap/layers/building/create.sql | 2 +- basemap/layers/highway/create.sql | 2 +- basemap/layers/highway/prepare.sql | 2 +- basemap/layers/highway/simplify.sql | 40 +++++++++++------------ basemap/layers/landuse/create.sql | 12 +++---- basemap/layers/landuse/prepare.sql | 60 +++++++++++++++++------------------ basemap/layers/landuse/simplify.sql | 40 +++++++++++------------ basemap/layers/leisure/create.sql | 32 ++++++++++++------- basemap/layers/leisure/prepare.sql | 60 +++++++++++++++++------------------ basemap/layers/leisure/simplify.sql | 40 +++++++++++------------ basemap/layers/linestring/create.sql | 2 +- basemap/layers/linestring/prepare.sql | 2 +- basemap/layers/man_made/create.sql | 2 +- basemap/layers/member/create.sql | 2 +- basemap/layers/member/prepare.sql | 2 +- basemap/layers/natural/create.sql | 31 +++++++++++------- basemap/layers/natural/prepare.sql | 48 ++++++++++++++-------------- basemap/layers/natural/simplify.sql | 40 +++++++++++------------ basemap/layers/ocean/create.sql | 10 +++--- basemap/layers/ocean/prepare.sql | 4 +-- basemap/layers/point/create.sql | 42 ++++++++++++------------ basemap/layers/point/simplify.sql | 40 +++++++++++------------ basemap/layers/polygon/create.sql | 2 +- basemap/layers/polygon/prepare.sql | 2 +- basemap/layers/power/create.sql | 2 +- basemap/layers/railway/create.sql | 2 +- basemap/layers/railway/prepare.sql | 2 +- basemap/layers/railway/simplify.sql | 40 +++++++++++------------ basemap/layers/route/create.sql | 2 +- basemap/layers/route/prepare.sql | 2 +- basemap/layers/route/simplify.sql | 40 +++++++++++------------ basemap/layers/tourism/create.sql | 2 +- basemap/layers/waterway/create.sql | 2 +- basemap/layers/waterway/prepare.sql | 2 +- basemap/layers/waterway/simplify.sql | 40 +++++++++++------------ 41 files changed, 342 insertions(+), 325 deletions(-) diff --git a/basemap/layers/aerialway/create.sql b/basemap/layers/aerialway/create.sql index 16e815150..7775e6e04 100644 --- a/basemap/layers/aerialway/create.sql +++ b/basemap/layers/aerialway/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_aerialway AS +CREATE OR REPLACE 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/aeroway/create.sql b/basemap/layers/aeroway/create.sql index dcbb84c60..f56d43937 100644 --- a/basemap/layers/aeroway/create.sql +++ b/basemap/layers/aeroway/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_aeroway AS +CREATE OR REPLACE 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/amenity/create.sql b/basemap/layers/amenity/create.sql index 2c4581079..327be23f0 100644 --- a/basemap/layers/amenity/create.sql +++ b/basemap/layers/amenity/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_amenity AS +CREATE OR REPLACE 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/attraction/create.sql b/basemap/layers/attraction/create.sql index d79901e40..5cd5ad6a8 100644 --- a/basemap/layers/attraction/create.sql +++ b/basemap/layers/attraction/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_attraction AS +CREATE OR REPLACE 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/barrier/create.sql b/basemap/layers/barrier/create.sql index 30fc680cb..3ddf00e44 100644 --- a/basemap/layers/barrier/create.sql +++ b/basemap/layers/barrier/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_barrier AS +CREATE OR REPLACE 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/boundary/create.sql b/basemap/layers/boundary/create.sql index ea64f9522..5f06f34b6 100644 --- a/basemap/layers/boundary/create.sql +++ b/basemap/layers/boundary/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_boundary AS +CREATE OR REPLACE 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/building/create.sql b/basemap/layers/building/create.sql index 6ed153ee0..8d5ec5fd2 100644 --- a/basemap/layers/building/create.sql +++ b/basemap/layers/building/create.sql @@ -1,4 +1,4 @@ -CREATE VIEW osm_building AS +CREATE OR REPLACE VIEW osm_building AS SELECT id, tags diff --git a/basemap/layers/highway/create.sql b/basemap/layers/highway/create.sql index f4bda38a2..b5a49eec5 100644 --- a/basemap/layers/highway/create.sql +++ b/basemap/layers/highway/create.sql @@ -12,7 +12,7 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_highway AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway AS WITH -- Filter the linestrings filtered AS ( diff --git a/basemap/layers/highway/prepare.sql b/basemap/layers/highway/prepare.sql index b29d130d3..cf28428fe 100644 --- a/basemap/layers/highway/prepare.sql +++ b/basemap/layers/highway/prepare.sql @@ -12,7 +12,7 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_highway AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_highway AS WITH -- Filter the linestrings filtered AS ( diff --git a/basemap/layers/highway/simplify.sql b/basemap/layers/highway/simplify.sql index faeb24f42..4e6194a9c 100644 --- a/basemap/layers/highway/simplify.sql +++ b/basemap/layers/highway/simplify.sql @@ -13,97 +13,97 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_highway_z20 AS +CREATE OR REPLACE VIEW osm_highway_z20 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z19 AS +CREATE OR REPLACE VIEW osm_highway_z19 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z18 AS +CREATE OR REPLACE VIEW osm_highway_z18 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z17 AS +CREATE OR REPLACE VIEW osm_highway_z17 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z16 AS +CREATE OR REPLACE VIEW osm_highway_z16 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z15 AS +CREATE OR REPLACE VIEW osm_highway_z15 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z14 AS +CREATE OR REPLACE VIEW osm_highway_z14 AS SELECT id, tags, geom FROM osm_highway; -CREATE VIEW osm_highway_z13 AS +CREATE OR REPLACE VIEW osm_highway_z13 AS SELECT id, tags, geom FROM osm_highway; -CREATE MATERIALIZED VIEW osm_highway_z12 AS +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) 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'); -CREATE MATERIALIZED VIEW osm_highway_z11 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 11)), 2)) AND tags ->> 'highway' IN ( 'motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link', 'unclassified', 'residential'); -CREATE MATERIALIZED VIEW osm_highway_z10 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 10)), 2)) AND tags ->> 'highway' IN ( 'motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link'); -CREATE MATERIALIZED VIEW osm_highway_z9 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 9)), 2)) AND tags ->> 'highway' IN ( 'motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link'); -CREATE MATERIALIZED VIEW osm_highway_z8 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 8)), 2)) AND tags ->> 'highway' IN ( 'motorway', 'trunk', 'primary'); -CREATE MATERIALIZED VIEW osm_highway_z7 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 7)), 2)) AND tags ->> 'highway' IN ( 'motorway', 'trunk', 'primary'); -CREATE MATERIALIZED VIEW osm_highway_z6 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 6)), 2)) AND tags ->> 'highway' IN ( 'motorway', 'trunk', 'primary'); -CREATE MATERIALIZED VIEW osm_highway_z5 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 5)), 2)) AND tags ->> 'highway' IN ( 'motorway'); -CREATE MATERIALIZED VIEW osm_highway_z4 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 4)), 2)) AND tags ->> 'highway' IN ( 'motorway'); -CREATE MATERIALIZED VIEW osm_highway_z3 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 3)), 2)) AND tags ->> 'highway' IN ( 'motorway'); -CREATE MATERIALIZED VIEW osm_highway_z2 AS +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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 2)), 2)) AND tags ->> 'highway' IN ( 'motorway'); -CREATE MATERIALIZED VIEW osm_highway_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTSosm_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 WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 1)), 2)) diff --git a/basemap/layers/landuse/create.sql b/basemap/layers/landuse/create.sql index 90b018745..386ee5c05 100644 --- a/basemap/layers/landuse/create.sql +++ b/basemap/layers/landuse/create.sql @@ -1,7 +1,7 @@ -CREATE MATERIALIZED VIEW osm_landuse_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_filtered AS SELECT tags -> 'landuse' AS landuse, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom @@ -10,7 +10,7 @@ WHERE geom IS NOT NULL AND st_area(geom) > 78270 / power(2, 12) * 100 AND tags ->> 'landuse' IN ('commercial', 'construction', 'industrial', 'residential', 'retail', 'farmland', 'forest', 'meadow', 'greenhouse_horticulture', 'meadow', 'orchard', 'plant_nursery','vineyard', 'basin', 'salt_pond', 'brownfield', 'cemetery', 'grass', 'greenfield', 'landfill', 'military', 'quarry', 'railway'); -CREATE MATERIALIZED VIEW osm_landuse_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_clustered AS SELECT landuse, geom, @@ -18,26 +18,26 @@ SELECT FROM osm_landuse_filtered WHERE geom IS NOT NULL; -CREATE MATERIALIZED VIEW osm_landuse_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_grouped AS SELECT landuse, st_collect(geom) AS geom FROM osm_landuse_clustered GROUP BY landuse, cluster; -CREATE MATERIALIZED VIEW osm_landuse_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_buffered AS SELECT landuse, st_buffer(geom, 0, 'join=mitre') AS geom FROM osm_landuse_grouped; -CREATE MATERIALIZED VIEW osm_landuse_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_exploded AS SELECT landuse, (st_dump(geom)).geom AS geom FROM osm_landuse_buffered; -CREATE MATERIALIZED VIEW osm_landuse AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse AS SELECT row_number() OVER () AS id, jsonb_build_object('landuse', landuse) AS tags, diff --git a/basemap/layers/landuse/prepare.sql b/basemap/layers/landuse/prepare.sql index 520c2fc88..fc0eceb74 100644 --- a/basemap/layers/landuse/prepare.sql +++ b/basemap/layers/landuse/prepare.sql @@ -12,7 +12,7 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_landuse_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_filtered AS SELECT tags -> 'landuse' AS landuse, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom @@ -23,7 +23,7 @@ WHERE geom IS NOT NULL CREATE INDEX IF NOT EXISTS osm_landuse_filtered_geom_idx ON osm_landuse_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_landuse_filtered_tags_idx ON osm_landuse_filtered (landuse); -CREATE MATERIALIZED VIEW osm_landuse_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_clustered AS SELECT landuse, geom, @@ -33,26 +33,26 @@ WHERE geom IS NOT NULL; CREATE INDEX IF NOT EXISTS osm_landuse_clustered_geom_idx ON osm_landuse_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_landuse_clustered_tags_idx ON osm_landuse_clustered (landuse); -CREATE MATERIALIZED VIEW osm_landuse_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_grouped AS SELECT landuse, st_collect(geom) AS geom FROM osm_landuse_clustered GROUP BY landuse, cluster; -CREATE MATERIALIZED VIEW osm_landuse_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_buffered AS SELECT landuse, st_buffer(geom, 0, 'join=mitre') AS geom FROM osm_landuse_grouped; -CREATE MATERIALIZED VIEW osm_landuse_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_exploded AS SELECT landuse, (st_dump(geom)).geom AS geom FROM osm_landuse_buffered; -CREATE MATERIALIZED VIEW osm_landuse AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse AS SELECT row_number() OVER () AS id, jsonb_build_object('landuse', landuse) AS tags, @@ -60,7 +60,7 @@ SELECT FROM osm_landuse_exploded; -- XTRA LARGE -CREATE MATERIALIZED VIEW osm_landuse_xl_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_xl_filtered AS SELECT id, tags -> 'landuse' as landuse, @@ -68,14 +68,14 @@ SELECT FROM osm_landuse WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 8), 2); -CREATE MATERIALIZED VIEW osm_landuse_xl_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_xl_clustered AS SELECT landuse, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY landuse) AS cluster FROM osm_landuse_xl_filtered; -CREATE MATERIALIZED VIEW osm_landuse_xl_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_xl_grouped AS SELECT landuse, cluster, @@ -83,19 +83,19 @@ SELECT FROM osm_landuse_xl_clustered GROUP BY landuse, cluster; -CREATE MATERIALIZED VIEW osm_landuse_xl_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_xl_buffered AS SELECT landuse, st_buffer(geom, -78270 / power(2, 8), 'join=mitre') AS geom FROM osm_landuse_xl_grouped; -CREATE MATERIALIZED VIEW osm_landuse_xl_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_xl_exploded AS SELECT landuse, (st_dump(geom)).geom AS geom FROM osm_landuse_xl_buffered; -CREATE MATERIALIZED VIEW osm_landuse_xl AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_xl AS SELECT row_number() OVER () AS id, jsonb_build_object('landuse', landuse) AS tags, @@ -103,7 +103,7 @@ SELECT FROM osm_landuse_xl_buffered; -- LARGE -CREATE MATERIALIZED VIEW osm_landuse_l_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_l_filtered AS SELECT id, tags -> 'landuse' as landuse, @@ -111,14 +111,14 @@ SELECT FROM osm_landuse WHERE st_area(st_envelope(geom)) > 5 * power(78270 / power(2, 7), 2); -CREATE MATERIALIZED VIEW osm_landuse_l_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTSosm_landuse_l_clustered AS SELECT landuse, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY landuse) AS cluster FROM osm_landuse_l_filtered; -CREATE MATERIALIZED VIEW osm_landuse_l_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTSosm_landuse_l_grouped AS SELECT landuse, cluster, @@ -126,19 +126,19 @@ SELECT FROM osm_landuse_l_clustered GROUP BY landuse, cluster; -CREATE MATERIALIZED VIEW osm_landuse_l_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTSosm_landuse_l_buffered AS SELECT landuse, st_buffer(geom, 0.5 * -78270 / power(2, 7), 'join=mitre') AS geom FROM osm_landuse_l_grouped; -CREATE MATERIALIZED VIEW osm_landuse_l_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTSosm_landuse_l_exploded AS SELECT landuse, (st_dump(geom)).geom AS geom FROM osm_landuse_l_buffered; -CREATE MATERIALIZED VIEW osm_landuse_l AS +CREATE MATERIALIZED VIEW IF NOT EXISTSosm_landuse_l AS SELECT row_number() OVER () AS id, jsonb_build_object('landuse', landuse) AS tags, @@ -146,7 +146,7 @@ SELECT FROM osm_landuse_l_buffered; -- MEDIUM -CREATE MATERIALIZED VIEW osm_landuse_m_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_m_filtered AS SELECT id, tags -> 'landuse' as landuse, @@ -154,14 +154,14 @@ SELECT FROM osm_landuse WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 6), 2); -CREATE MATERIALIZED VIEW osm_landuse_m_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_m_clustered AS SELECT landuse, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY landuse) AS cluster FROM osm_landuse_m_filtered; -CREATE MATERIALIZED VIEW osm_landuse_m_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_m_grouped AS SELECT landuse, cluster, @@ -169,19 +169,19 @@ SELECT FROM osm_landuse_m_clustered GROUP BY landuse, cluster; -CREATE MATERIALIZED VIEW osm_landuse_m_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_m_buffered AS SELECT landuse, st_buffer(geom, 0.1 * -78270 / power(2, 6), 'join=mitre') AS geom FROM osm_landuse_m_grouped; -CREATE MATERIALIZED VIEW osm_landuse_m_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_m_exploded AS SELECT landuse, (st_dump(geom)).geom AS geom FROM osm_landuse_m_buffered; -CREATE MATERIALIZED VIEW osm_landuse_m AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_m AS SELECT row_number() OVER () AS id, jsonb_build_object('landuse', landuse) AS tags, @@ -189,7 +189,7 @@ SELECT FROM osm_landuse_m_buffered; -- SMALL -CREATE MATERIALIZED VIEW osm_landuse_s_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_s_filtered AS SELECT id, tags -> 'landuse' as landuse, @@ -197,14 +197,14 @@ SELECT FROM osm_landuse WHERE st_area(st_envelope(geom)) > 15 * power(78270 / power(2, 5), 2); -CREATE MATERIALIZED VIEW osm_landuse_s_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_s_clustered AS SELECT landuse, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY landuse) AS cluster FROM osm_landuse_s_filtered; -CREATE MATERIALIZED VIEW osm_landuse_s_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_s_grouped AS SELECT landuse, cluster, @@ -212,19 +212,19 @@ SELECT FROM osm_landuse_s_clustered GROUP BY landuse, cluster; -CREATE MATERIALIZED VIEW osm_landuse_s_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_s_buffered AS SELECT landuse, st_buffer(geom, 0, 'join=mitre') AS geom FROM osm_landuse_s_grouped; -CREATE MATERIALIZED VIEW osm_landuse_s_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_s_exploded AS SELECT landuse, (st_dump(geom)).geom AS geom FROM osm_landuse_s_buffered; -CREATE MATERIALIZED VIEW osm_landuse_s AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_s AS SELECT row_number() OVER () AS id, jsonb_build_object('landuse', landuse) AS tags, diff --git a/basemap/layers/landuse/simplify.sql b/basemap/layers/landuse/simplify.sql index 5b8b9e4eb..40674507a 100644 --- a/basemap/layers/landuse/simplify.sql +++ b/basemap/layers/landuse/simplify.sql @@ -13,86 +13,86 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_landuse_z20 AS +CREATE OR REPLACE VIEW osm_landuse_z20 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z19 AS +CREATE OR REPLACE VIEW osm_landuse_z19 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z18 AS +CREATE OR REPLACE VIEW osm_landuse_z18 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z17 AS +CREATE OR REPLACE VIEW osm_landuse_z17 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z16 AS +CREATE OR REPLACE VIEW osm_landuse_z16 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z15 AS +CREATE OR REPLACE VIEW osm_landuse_z15 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z14 AS +CREATE OR REPLACE VIEW osm_landuse_z14 AS SELECT id, tags, geom FROM osm_landuse; -CREATE VIEW osm_landuse_z13 AS +CREATE OR REPLACE VIEW osm_landuse_z13 AS SELECT id, tags, geom FROM osm_landuse; -CREATE MATERIALIZED VIEW osm_landuse_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z12 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_landuse WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 12), 2); -CREATE MATERIALIZED VIEW osm_landuse_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z11 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_landuse WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 11), 2); -CREATE MATERIALIZED VIEW osm_landuse_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z10 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_landuse WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 10), 2); -CREATE MATERIALIZED VIEW osm_landuse_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z9 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_landuse WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 9), 2); -CREATE MATERIALIZED VIEW osm_landuse_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z8 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_landuse_xl WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 8), 2); -CREATE MATERIALIZED VIEW osm_landuse_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z7 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_landuse_l WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 7), 2); -CREATE MATERIALIZED VIEW osm_landuse_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z6 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_landuse_m WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 6), 2); -CREATE MATERIALIZED VIEW osm_landuse_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z5 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_landuse_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 5), 2); -CREATE MATERIALIZED VIEW osm_landuse_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z4 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_landuse_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 4), 2); -CREATE MATERIALIZED VIEW osm_landuse_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z3 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_landuse_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 3), 2); -CREATE MATERIALIZED VIEW osm_landuse_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z2 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_landuse_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 2), 2); -CREATE MATERIALIZED VIEW osm_landuse_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_landuse_z1 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_landuse_s WHERE st_area(st_envelope(geom)) > 250 * power(78270 / power(2, 1), 2); diff --git a/basemap/layers/leisure/create.sql b/basemap/layers/leisure/create.sql index e42b14e70..29e784c93 100644 --- a/basemap/layers/leisure/create.sql +++ b/basemap/layers/leisure/create.sql @@ -13,49 +13,57 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_leisure_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_filtered AS SELECT tags -> 'leisure' AS leisure, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_polygon WHERE geom IS NOT NULL AND st_area(geom) > 78270 / power(2, 12) * 100 - AND tags ->> 'leisure' IN ('garden', 'golf_course', 'marina', 'nature_reserve', 'park', 'pitch', 'sport_center', 'stadium', 'swimming_pool', 'track'); + AND tags ->> 'leisure' IN ('garden', 'golf_course', 'marina', 'nature_reserve', 'park', 'pitch', 'sport_center', 'stadium', 'swimming_pool', 'track') +WITH NO DATA; + CREATE INDEX IF NOT EXISTS osm_leisure_filtered_geom_idx ON osm_leisure_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_leisure_filtered_tags_idx ON osm_leisure_filtered (leisure); -CREATE MATERIALIZED VIEW osm_leisure_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_clustered AS SELECT leisure, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster FROM osm_leisure_filtered -WHERE geom IS NOT NULL; +WHERE geom IS NOT NULL +WITH NO DATA; + CREATE INDEX IF NOT EXISTS osm_leisure_clustered_geom_idx ON osm_leisure_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_leisure_clustered_tags_idx ON osm_leisure_clustered (leisure); -CREATE MATERIALIZED VIEW osm_leisure_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_grouped AS SELECT leisure, st_collect(geom) AS geom FROM osm_leisure_clustered -GROUP BY leisure, cluster; +GROUP BY leisure, cluster +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_leisure_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_buffered AS SELECT leisure, st_buffer(geom, 0, 'join=mitre') AS geom -FROM osm_leisure_grouped; +FROM osm_leisure_grouped +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_leisure_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_exploded AS SELECT leisure, (st_dump(geom)).geom AS geom -FROM osm_leisure_buffered; +FROM osm_leisure_buffered +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_leisure AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure AS SELECT row_number() OVER () AS id, jsonb_build_object('leisure', leisure) AS tags, geom -FROM osm_leisure_exploded; +FROM osm_leisure_exploded +WITH NO DATA; diff --git a/basemap/layers/leisure/prepare.sql b/basemap/layers/leisure/prepare.sql index aba4c2abb..452bcae39 100644 --- a/basemap/layers/leisure/prepare.sql +++ b/basemap/layers/leisure/prepare.sql @@ -13,7 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_leisure_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_filtered AS SELECT tags -> 'leisure' AS leisure, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom @@ -24,7 +24,7 @@ WHERE geom IS NOT NULL CREATE INDEX IF NOT EXISTS osm_leisure_filtered_geom_idx ON osm_leisure_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_leisure_filtered_tags_idx ON osm_leisure_filtered (leisure); -CREATE MATERIALIZED VIEW osm_leisure_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_clustered AS SELECT leisure, geom, @@ -34,26 +34,26 @@ WHERE geom IS NOT NULL; CREATE INDEX IF NOT EXISTS osm_leisure_clustered_geom_idx ON osm_leisure_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_leisure_clustered_tags_idx ON osm_leisure_clustered (leisure); -CREATE MATERIALIZED VIEW osm_leisure_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_grouped AS SELECT leisure, st_collect(geom) AS geom FROM osm_leisure_clustered GROUP BY leisure, cluster; -CREATE MATERIALIZED VIEW osm_leisure_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_buffered AS SELECT leisure, st_buffer(geom, 0, 'join=mitre') AS geom FROM osm_leisure_grouped; -CREATE MATERIALIZED VIEW osm_leisure_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_exploded AS SELECT leisure, (st_dump(geom)).geom AS geom FROM osm_leisure_buffered; -CREATE MATERIALIZED VIEW osm_leisure AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure AS SELECT row_number() OVER () AS id, jsonb_build_object('leisure', leisure) AS tags, @@ -61,7 +61,7 @@ SELECT FROM osm_leisure_exploded; -- XTRA LARGE -CREATE MATERIALIZED VIEW osm_leisure_xl_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_xl_filtered AS SELECT id, tags -> 'leisure' as leisure, @@ -69,14 +69,14 @@ SELECT FROM osm_leisure WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 8), 2); -CREATE MATERIALIZED VIEW osm_leisure_xl_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_xl_clustered AS SELECT leisure, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster FROM osm_leisure_xl_filtered; -CREATE MATERIALIZED VIEW osm_leisure_xl_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_xl_grouped AS SELECT leisure, cluster, @@ -84,19 +84,19 @@ SELECT FROM osm_leisure_xl_clustered GROUP BY leisure, cluster; -CREATE MATERIALIZED VIEW osm_leisure_xl_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_xl_buffered AS SELECT leisure, st_buffer(geom, -78270 / power(2, 8), 'join=mitre') AS geom FROM osm_leisure_xl_grouped; -CREATE MATERIALIZED VIEW osm_leisure_xl_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_xl_exploded AS SELECT leisure, (st_dump(geom)).geom AS geom FROM osm_leisure_xl_buffered; -CREATE MATERIALIZED VIEW osm_leisure_xl AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_xl AS SELECT row_number() OVER () AS id, jsonb_build_object('leisure', leisure) AS tags, @@ -104,7 +104,7 @@ SELECT FROM osm_leisure_xl_buffered; -- LARGE -CREATE MATERIALIZED VIEW osm_leisure_l_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_l_filtered AS SELECT id, tags -> 'leisure' as leisure, @@ -112,14 +112,14 @@ SELECT FROM osm_leisure WHERE st_area(st_envelope(geom)) > 5 * power(78270 / power(2, 7), 2); -CREATE MATERIALIZED VIEW osm_leisure_l_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_l_clustered AS SELECT leisure, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster FROM osm_leisure_l_filtered; -CREATE MATERIALIZED VIEW osm_leisure_l_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_l_grouped AS SELECT leisure, cluster, @@ -127,19 +127,19 @@ SELECT FROM osm_leisure_l_clustered GROUP BY leisure, cluster; -CREATE MATERIALIZED VIEW osm_leisure_l_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_l_buffered AS SELECT leisure, st_buffer(geom, 0.5 * -78270 / power(2, 7), 'join=mitre') AS geom FROM osm_leisure_l_grouped; -CREATE MATERIALIZED VIEW osm_leisure_l_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_l_exploded AS SELECT leisure, (st_dump(geom)).geom AS geom FROM osm_leisure_l_buffered; -CREATE MATERIALIZED VIEW osm_leisure_l AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_l AS SELECT row_number() OVER () AS id, jsonb_build_object('leisure', leisure) AS tags, @@ -147,7 +147,7 @@ SELECT FROM osm_leisure_l_buffered; -- MEDIUM -CREATE MATERIALIZED VIEW osm_leisure_m_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_m_filtered AS SELECT id, tags -> 'leisure' as leisure, @@ -155,14 +155,14 @@ SELECT FROM osm_leisure WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 6), 2); -CREATE MATERIALIZED VIEW osm_leisure_m_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_m_clustered AS SELECT leisure, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster FROM osm_leisure_m_filtered; -CREATE MATERIALIZED VIEW osm_leisure_m_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_m_grouped AS SELECT leisure, cluster, @@ -170,19 +170,19 @@ SELECT FROM osm_leisure_m_clustered GROUP BY leisure, cluster; -CREATE MATERIALIZED VIEW osm_leisure_m_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_m_buffered AS SELECT leisure, st_buffer(geom, 0.1 * -78270 / power(2, 6), 'join=mitre') AS geom FROM osm_leisure_m_grouped; -CREATE MATERIALIZED VIEW osm_leisure_m_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_m_exploded AS SELECT leisure, (st_dump(geom)).geom AS geom FROM osm_leisure_m_buffered; -CREATE MATERIALIZED VIEW osm_leisure_m AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_m AS SELECT row_number() OVER () AS id, jsonb_build_object('leisure', leisure) AS tags, @@ -190,7 +190,7 @@ SELECT FROM osm_leisure_m_buffered; -- SMALL -CREATE MATERIALIZED VIEW osm_leisure_s_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_s_filtered AS SELECT id, tags -> 'leisure' as leisure, @@ -198,14 +198,14 @@ SELECT FROM osm_leisure WHERE st_area(st_envelope(geom)) > 15 * power(78270 / power(2, 5), 2); -CREATE MATERIALIZED VIEW osm_leisure_s_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_s_clustered AS SELECT leisure, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster FROM osm_leisure_s_filtered; -CREATE MATERIALIZED VIEW osm_leisure_s_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_s_grouped AS SELECT leisure, cluster, @@ -213,19 +213,19 @@ SELECT FROM osm_leisure_s_clustered GROUP BY leisure, cluster; -CREATE MATERIALIZED VIEW osm_leisure_s_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_s_buffered AS SELECT leisure, st_buffer(geom, 0, 'join=mitre') AS geom FROM osm_leisure_s_grouped; -CREATE MATERIALIZED VIEW osm_leisure_s_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_s_exploded AS SELECT leisure, (st_dump(geom)).geom AS geom FROM osm_leisure_s_buffered; -CREATE MATERIALIZED VIEW osm_leisure_s AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_s AS SELECT row_number() OVER () AS id, jsonb_build_object('leisure', leisure) AS tags, diff --git a/basemap/layers/leisure/simplify.sql b/basemap/layers/leisure/simplify.sql index cdd6756c7..094280bf1 100644 --- a/basemap/layers/leisure/simplify.sql +++ b/basemap/layers/leisure/simplify.sql @@ -13,86 +13,86 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_leisure_z20 AS +CREATE OR REPLACE VIEW osm_leisure_z20 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z19 AS +CREATE OR REPLACE VIEW osm_leisure_z19 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z18 AS +CREATE OR REPLACE VIEW osm_leisure_z18 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z17 AS +CREATE OR REPLACE VIEW osm_leisure_z17 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z16 AS +CREATE OR REPLACE VIEW osm_leisure_z16 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z15 AS +CREATE OR REPLACE VIEW osm_leisure_z15 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z14 AS +CREATE OR REPLACE VIEW osm_leisure_z14 AS SELECT id, tags, geom FROM osm_leisure; -CREATE VIEW osm_leisure_z13 AS +CREATE OR REPLACE VIEW osm_leisure_z13 AS SELECT id, tags, geom FROM osm_leisure; -CREATE MATERIALIZED VIEW osm_leisure_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z12 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_leisure WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 12), 2); -CREATE MATERIALIZED VIEW osm_leisure_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z11 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_leisure WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 11), 2); -CREATE MATERIALIZED VIEW osm_leisure_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z10 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_leisure WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 10), 2); -CREATE MATERIALIZED VIEW osm_leisure_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z9 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_leisure WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 9), 2); -CREATE MATERIALIZED VIEW osm_leisure_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z8 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_leisure_xl WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 8), 2); -CREATE MATERIALIZED VIEW osm_leisure_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z7 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_leisure_l WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 7), 2); -CREATE MATERIALIZED VIEW osm_leisure_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z6 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_leisure_m WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 6), 2); -CREATE MATERIALIZED VIEW osm_leisure_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z5 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_leisure_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 5), 2); -CREATE MATERIALIZED VIEW osm_leisure_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z4 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_leisure_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 4), 2); -CREATE MATERIALIZED VIEW osm_leisure_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z3 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_leisure_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 3), 2); -CREATE MATERIALIZED VIEW osm_leisure_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z2 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_leisure_s WHERE st_area(st_envelope(geom)) > 150 * power(78270 / power(2, 2), 2); -CREATE MATERIALIZED VIEW osm_leisure_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_leisure_z1 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_leisure_s WHERE st_area(st_envelope(geom)) > 250 * power(78270 / power(2, 1), 2); diff --git a/basemap/layers/linestring/create.sql b/basemap/layers/linestring/create.sql index 4cf0976d4..d3de1821e 100644 --- a/basemap/layers/linestring/create.sql +++ b/basemap/layers/linestring/create.sql @@ -1,4 +1,4 @@ -CREATE MATERIALIZED VIEW osm_linestring AS +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' diff --git a/basemap/layers/linestring/prepare.sql b/basemap/layers/linestring/prepare.sql index 7134fd5e2..2666b2f59 100644 --- a/basemap/layers/linestring/prepare.sql +++ b/basemap/layers/linestring/prepare.sql @@ -15,7 +15,7 @@ DROP MATERIALIZED VIEW IF EXISTS osm_linestring CASCADE; -CREATE MATERIALIZED VIEW osm_linestring AS +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 diff --git a/basemap/layers/man_made/create.sql b/basemap/layers/man_made/create.sql index 82cd09f40..26c06bef5 100644 --- a/basemap/layers/man_made/create.sql +++ b/basemap/layers/man_made/create.sql @@ -1,3 +1,3 @@ -CREATE VIEW osm_man_made AS +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 diff --git a/basemap/layers/member/create.sql b/basemap/layers/member/create.sql index 3f3f505f1..58b791dd9 100644 --- a/basemap/layers/member/create.sql +++ b/basemap/layers/member/create.sql @@ -1,4 +1,4 @@ -CREATE MATERIALIZED VIEW osm_member AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_member AS SELECT DISTINCT member_ref as member_ref FROM osm_relation, unnest(member_types, member_refs) AS way(member_type, member_ref) diff --git a/basemap/layers/member/prepare.sql b/basemap/layers/member/prepare.sql index 6367792e1..e055f7262 100644 --- a/basemap/layers/member/prepare.sql +++ b/basemap/layers/member/prepare.sql @@ -14,7 +14,7 @@ -- limitations under the License. DROP MATERIALIZED VIEW IF EXISTS osm_member CASCADE; -CREATE MATERIALIZED VIEW osm_member AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_member AS SELECT DISTINCT member_ref as member_ref FROM osm_relation, unnest(member_types, member_refs) AS way(member_type, member_ref) WHERE geom IS NOT NULL diff --git a/basemap/layers/natural/create.sql b/basemap/layers/natural/create.sql index d24abb483..0af84236a 100644 --- a/basemap/layers/natural/create.sql +++ b/basemap/layers/natural/create.sql @@ -15,53 +15,60 @@ -- ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree'); -CREATE MATERIALIZED VIEW osm_natural_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_filtered AS SELECT tags -> 'natural' AS natural_value, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_polygon WHERE geom IS NOT NULL AND st_area(geom) > 78270 / power(2, 12) * 100 - AND tags ->> 'natural' IN ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree'); + AND tags ->> 'natural' IN ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree') +WITH NO DATA; + CREATE INDEX IF NOT EXISTS osm_natural_filtered_geom_idx ON osm_natural_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_filtered_tags_idx ON osm_natural_filtered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_clustered AS SELECT natural_value, geom, st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY natural_value) AS cluster FROM osm_natural_filtered -WHERE geom IS NOT NULL; +WHERE geom IS NOT NULL +WITH NO DATA; 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); -CREATE MATERIALIZED VIEW osm_natural_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_grouped AS SELECT natural_value, st_collect(geom) AS geom FROM osm_natural_clustered -GROUP BY natural_value, cluster; +GROUP BY natural_value, cluster +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_natural_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_buffered AS SELECT natural_value, st_buffer(geom, 0, 'join=mitre') AS geom -FROM osm_natural_grouped; +FROM osm_natural_grouped +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_natural_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_exploded AS SELECT natural_value, (st_dump(geom)).geom AS geom -FROM osm_natural_buffered; +FROM osm_natural_buffered +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_natural AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural AS SELECT row_number() OVER () AS id, jsonb_build_object('natural', natural_value) AS tags, geom -FROM osm_natural_exploded; +FROM osm_natural_exploded +WITH NO DATA; 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/prepare.sql b/basemap/layers/natural/prepare.sql index 3bc1e3627..6d5d8fe9f 100644 --- a/basemap/layers/natural/prepare.sql +++ b/basemap/layers/natural/prepare.sql @@ -15,7 +15,7 @@ -- ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree'); -CREATE MATERIALIZED VIEW osm_natural_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_filtered AS SELECT tags -> 'natural' AS natural_value, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom @@ -26,7 +26,7 @@ WHERE geom IS NOT NULL CREATE INDEX IF NOT EXISTS osm_natural_filtered_geom_idx ON osm_natural_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_filtered_tags_idx ON osm_natural_filtered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_clustered AS SELECT natural_value, geom, @@ -36,26 +36,26 @@ 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); -CREATE MATERIALIZED VIEW osm_natural_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_grouped AS SELECT natural_value, st_collect(geom) AS geom FROM osm_natural_clustered GROUP BY natural_value, cluster; -CREATE MATERIALIZED VIEW osm_natural_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_buffered AS SELECT natural_value, st_buffer(geom, 0, 'join=mitre') AS geom FROM osm_natural_grouped; -CREATE MATERIALIZED VIEW osm_natural_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_exploded AS SELECT natural_value, (st_dump(geom)).geom AS geom FROM osm_natural_buffered; -CREATE MATERIALIZED VIEW osm_natural AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural AS SELECT row_number() OVER () AS id, jsonb_build_object('natural', natural_value) AS tags, @@ -65,7 +65,7 @@ CREATE INDEX IF NOT EXISTS osm_natural_geom_idx ON osm_natural_filtered USING GI CREATE INDEX IF NOT EXISTS osm_natural_tags_idx ON osm_natural_filtered USING GIN (natural_value); -- XTRA LARGE -CREATE MATERIALIZED VIEW osm_natural_xl_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_xl_filtered AS SELECT id, tags -> 'natural' as natural_value, @@ -75,7 +75,7 @@ WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 8), 2); CREATE INDEX IF NOT EXISTS osm_natural_xl_filtered_geom_idx ON osm_natural_xl_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_xl_filtered_tags_idx ON osm_natural_xl_filtered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_xl_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_xl_clustered AS SELECT natural_value, geom, @@ -84,7 +84,7 @@ FROM osm_natural_xl_filtered; CREATE INDEX IF NOT EXISTS osm_natural_xl_clustered_geom_idx ON osm_natural_xl_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_xl_clustered_tags_idx ON osm_natural_xl_clustered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_xl_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_xl_grouped AS SELECT natural_value, cluster, @@ -92,19 +92,19 @@ SELECT FROM osm_natural_xl_clustered GROUP BY natural_value, cluster; -CREATE MATERIALIZED VIEW osm_natural_xl_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_xl_buffered AS SELECT natural_value, st_buffer(geom, -78270 / power(2, 8), 'join=mitre') AS geom FROM osm_natural_xl_grouped; -CREATE MATERIALIZED VIEW osm_natural_xl_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_xl_exploded AS SELECT natural_value, (st_dump(geom)).geom AS geom FROM osm_natural_xl_buffered; -CREATE MATERIALIZED VIEW osm_natural_xl AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_xl AS SELECT row_number() OVER () AS id, jsonb_build_object('natural', natural_value) AS tags, @@ -114,7 +114,7 @@ CREATE INDEX IF NOT EXISTS osm_natural_xl_geom_idx ON osm_natural_xl USING GIST CREATE INDEX IF NOT EXISTS osm_natural_xl_tags_idx ON osm_natural_xl USING GIN (tags); -- MEDIUM -CREATE MATERIALIZED VIEW osm_natural_m_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_m_filtered AS SELECT id, tags -> 'natural' as natural_value, @@ -124,7 +124,7 @@ WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 6), 2); CREATE INDEX IF NOT EXISTS osm_natural_m_filtered_geom_idx ON osm_natural_m_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_m_filtered_tags_idx ON osm_natural_m_filtered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_m_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_m_clustered AS SELECT natural_value, geom, @@ -133,7 +133,7 @@ FROM osm_natural_m_filtered; CREATE INDEX IF NOT EXISTS osm_natural_m_clustered_geom_idx ON osm_natural_m_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_m_clustered_tags_idx ON osm_natural_m_clustered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_m_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_m_grouped AS SELECT natural_value, cluster, @@ -141,19 +141,19 @@ SELECT FROM osm_natural_m_clustered GROUP BY natural_value, cluster; -CREATE MATERIALIZED VIEW osm_natural_m_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_m_buffered AS SELECT natural_value, st_buffer(geom, 0.5 * -78270 / power(2, 6), 'join=mitre') AS geom FROM osm_natural_m_grouped; -CREATE MATERIALIZED VIEW osm_natural_m_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_m_exploded AS SELECT natural_value, (st_dump(geom)).geom AS geom FROM osm_natural_m_buffered; -CREATE MATERIALIZED VIEW osm_natural_m AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_m AS SELECT row_number() OVER () AS id, jsonb_build_object('natural', natural_value) AS tags, @@ -163,7 +163,7 @@ CREATE INDEX IF NOT EXISTS osm_natural_m_geom_idx ON osm_natural_m USING GIST (g CREATE INDEX IF NOT EXISTS osm_natural_m_tags_idx ON osm_natural_m USING GIN (tags); -- SMALL -CREATE MATERIALIZED VIEW osm_natural_s_filtered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_s_filtered AS SELECT id, tags -> 'natural' as natural_value, @@ -173,7 +173,7 @@ WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 6), 2); CREATE INDEX IF NOT EXISTS osm_natural_s_filtered_geom_idx ON osm_natural_s_filtered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_s_filtered_tags_idx ON osm_natural_s_filtered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_s_clustered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_s_clustered AS SELECT natural_value, geom, @@ -182,7 +182,7 @@ FROM osm_natural_s_filtered; CREATE INDEX IF NOT EXISTS osm_natural_s_clustered_geom_idx ON osm_natural_s_clustered USING GIST (geom); CREATE INDEX IF NOT EXISTS osm_natural_s_clustered_tags_idx ON osm_natural_s_clustered (natural_value); -CREATE MATERIALIZED VIEW osm_natural_s_grouped AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_s_grouped AS SELECT natural_value, cluster, @@ -190,19 +190,19 @@ SELECT FROM osm_natural_s_clustered GROUP BY natural_value, cluster; -CREATE MATERIALIZED VIEW osm_natural_s_buffered AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_s_buffered AS SELECT natural_value, st_buffer(geom, 0.1 * -78270 / power(2, 6), 'join=mitre') AS geom FROM osm_natural_s_grouped; -CREATE MATERIALIZED VIEW osm_natural_s_exploded AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_s_exploded AS SELECT natural_value, (st_dump(geom)).geom AS geom FROM osm_natural_s_buffered; -CREATE MATERIALIZED VIEW osm_natural_s AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_s AS SELECT row_number() OVER () AS id, jsonb_build_object('natural', natural_value) AS tags, diff --git a/basemap/layers/natural/simplify.sql b/basemap/layers/natural/simplify.sql index 8f4523675..d09475f5f 100644 --- a/basemap/layers/natural/simplify.sql +++ b/basemap/layers/natural/simplify.sql @@ -13,86 +13,86 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_natural_z20 AS +CREATE OR REPLACE VIEW osm_natural_z20 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z19 AS +CREATE OR REPLACE VIEW osm_natural_z19 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z18 AS +CREATE OR REPLACE VIEW osm_natural_z18 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z17 AS +CREATE OR REPLACE VIEW osm_natural_z17 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z16 AS +CREATE OR REPLACE VIEW osm_natural_z16 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z15 AS +CREATE OR REPLACE VIEW osm_natural_z15 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z14 AS +CREATE OR REPLACE VIEW osm_natural_z14 AS SELECT id, tags, geom FROM osm_natural; -CREATE VIEW osm_natural_z13 AS +CREATE OR REPLACE VIEW osm_natural_z13 AS SELECT id, tags, geom FROM osm_natural; -CREATE MATERIALIZED VIEW osm_natural_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z12 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_natural WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 12), 2); -CREATE MATERIALIZED VIEW osm_natural_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z11 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_natural WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 11), 2); -CREATE MATERIALIZED VIEW osm_natural_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z10 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_natural WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 10), 2); -CREATE MATERIALIZED VIEW osm_natural_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z9 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_natural WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 9), 2); -CREATE MATERIALIZED VIEW osm_natural_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z8 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_natural_xl WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 8), 2); -CREATE MATERIALIZED VIEW osm_natural_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z7 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_natural_xl WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 7), 2); -CREATE MATERIALIZED VIEW osm_natural_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z6 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_natural_m WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 6), 2); -CREATE MATERIALIZED VIEW osm_natural_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z5 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_natural_s WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 5), 2); -CREATE MATERIALIZED VIEW osm_natural_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z4 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_natural_s WHERE st_area(st_envelope(geom)) > 10 * power(78270 / power(2, 4), 2); -CREATE MATERIALIZED VIEW osm_natural_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z3 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_natural_s WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 3), 2); -CREATE MATERIALIZED VIEW osm_natural_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z2 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_natural_s WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 2), 2); -CREATE MATERIALIZED VIEW osm_natural_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_natural_z1 AS SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_natural_s WHERE st_area(st_envelope(geom)) > 25 * power(78270 / power(2, 1), 2); diff --git a/basemap/layers/ocean/create.sql b/basemap/layers/ocean/create.sql index 77399e228..0e83f6d61 100644 --- a/basemap/layers/ocean/create.sql +++ b/basemap/layers/ocean/create.sql @@ -12,8 +12,10 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_ocean AS -SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM water_polygons_shp; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_ocean AS +SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM water_polygons_shp +WITH NO DATA; -CREATE MATERIALIZED VIEW osm_ocean_simplified AS -SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM simplified_water_polygons_shp; +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_ocean_simplified AS +SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM simplified_water_polygons_shp +WITH NO DATA; diff --git a/basemap/layers/ocean/prepare.sql b/basemap/layers/ocean/prepare.sql index 77399e228..06575e847 100644 --- a/basemap/layers/ocean/prepare.sql +++ b/basemap/layers/ocean/prepare.sql @@ -12,8 +12,8 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_ocean AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_ocean AS SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM water_polygons_shp; -CREATE MATERIALIZED VIEW osm_ocean_simplified AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_ocean_simplified AS SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM simplified_water_polygons_shp; diff --git a/basemap/layers/point/create.sql b/basemap/layers/point/create.sql index ea3effdf1..bfec5d4b2 100644 --- a/basemap/layers/point/create.sql +++ b/basemap/layers/point/create.sql @@ -1,17 +1,17 @@ -CREATE VIEW osm_point AS +CREATE OR REPLACE VIEW osm_point AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z20 AS SELECT * FROM point; -CREATE VIEW osm_point_z19 AS SELECT * FROM point; -CREATE VIEW osm_point_z18 AS SELECT * FROM point; -CREATE VIEW osm_point_z17 AS SELECT * FROM point; -CREATE VIEW osm_point_z16 AS SELECT * FROM point; -CREATE VIEW osm_point_z15 AS SELECT * FROM point; -CREATE VIEW osm_point_z14 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z20 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z19 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z18 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z17 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z16 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z15 AS SELECT * FROM point; +CREATE OR REPLACE VIEW osm_point_z14 AS SELECT * FROM point; -CREATE MATERIALIZED VIEW osm_point_z13 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z13 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','municipality','city','town','village','quarter','hamlet']) @@ -21,7 +21,7 @@ WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','mun OR (tags->>'waterway') = 'waterfall' OR (tags->>'railway') = 'level_crossing'; -CREATE MATERIALIZED VIEW osm_point_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z12 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','municipality','city','town','village']) @@ -30,61 +30,61 @@ WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','mun OR (tags->>'tourism') = 'wilderness_hut' OR (tags->>'waterway') = 'waterfall'; -CREATE MATERIALIZED VIEW osm_point_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z11 AS SELECT id, tags, geom FROM 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'; -CREATE MATERIALIZED VIEW osm_point_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z10 AS SELECT id, tags, geom FROM 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'; -CREATE MATERIALIZED VIEW osm_point_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z9 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']); -CREATE MATERIALIZED VIEW osm_point_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z8 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']); -CREATE MATERIALIZED VIEW osm_point_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z7 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); -CREATE MATERIALIZED VIEW osm_point_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z6 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); -CREATE MATERIALIZED VIEW osm_point_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z5 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); -CREATE MATERIALIZED VIEW osm_point_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z4 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']); -CREATE MATERIALIZED VIEW osm_point_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z3 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']); -CREATE MATERIALIZED VIEW osm_point_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z2 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = 'country'; -CREATE MATERIALIZED VIEW osm_point_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z1 AS SELECT id, tags, geom FROM point WHERE (tags->>'place') = 'country'; diff --git a/basemap/layers/point/simplify.sql b/basemap/layers/point/simplify.sql index 5ca51a019..e1670db6d 100644 --- a/basemap/layers/point/simplify.sql +++ b/basemap/layers/point/simplify.sql @@ -13,103 +13,103 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_point_z20 AS +CREATE OR REPLACE VIEW osm_point_z20 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z19 AS +CREATE OR REPLACE VIEW osm_point_z19 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z18 AS +CREATE OR REPLACE VIEW osm_point_z18 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z17 AS +CREATE OR REPLACE VIEW osm_point_z17 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z16 AS +CREATE OR REPLACE VIEW osm_point_z16 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z15 AS +CREATE OR REPLACE VIEW osm_point_z15 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE VIEW osm_point_z14 AS +CREATE OR REPLACE VIEW osm_point_z14 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}'; -CREATE MATERIALIZED VIEW osm_point_z13 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z13 AS SELECT id, tags, geom 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 +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z12 AS SELECT id, tags, geom 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 +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z11 AS SELECT id, tags, geom 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 +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z10 AS SELECT id, tags, geom 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 +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z9 AS SELECT id, tags, geom 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 +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z8 AS SELECT id, tags, geom 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 +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z7 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea', 'state', 'county'); -CREATE MATERIALIZED VIEW osm_point_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z6 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea', 'state', 'county'); -CREATE MATERIALIZED VIEW osm_point_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z5 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea', 'state', 'county'); -CREATE MATERIALIZED VIEW osm_point_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z4 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea'); -CREATE MATERIALIZED VIEW osm_point_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z3 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country', 'city', 'sea'); -CREATE MATERIALIZED VIEW osm_point_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z2 AS SELECT id, tags, geom FROM osm_node WHERE tags != '{}' AND tags ->> 'place' IN ('country'); -CREATE MATERIALIZED VIEW osm_point_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_point_z1 AS SELECT id, tags, geom 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 2e824fc38..ea70ab309 100644 --- a/basemap/layers/polygon/create.sql +++ b/basemap/layers/polygon/create.sql @@ -1,4 +1,4 @@ -CREATE MATERIALIZED VIEW osm_polygon AS +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' diff --git a/basemap/layers/polygon/prepare.sql b/basemap/layers/polygon/prepare.sql index 7de1d122c..4d694cfac 100644 --- a/basemap/layers/polygon/prepare.sql +++ b/basemap/layers/polygon/prepare.sql @@ -13,7 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_polygon AS +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' diff --git a/basemap/layers/power/create.sql b/basemap/layers/power/create.sql index 4e89ce8ee..398e97a5d 100644 --- a/basemap/layers/power/create.sql +++ b/basemap/layers/power/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_power AS +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 diff --git a/basemap/layers/railway/create.sql b/basemap/layers/railway/create.sql index 321e294a0..128ce70dc 100644 --- a/basemap/layers/railway/create.sql +++ b/basemap/layers/railway/create.sql @@ -1,4 +1,4 @@ -CREATE MATERIALIZED VIEW osm_railway AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway AS SELECT id, tags, geom FROM (SELECT min(id) as id, jsonb_build_object('railway', tags -> 'railway', 'service', tags -> 'service') as tags, diff --git a/basemap/layers/railway/prepare.sql b/basemap/layers/railway/prepare.sql index 038b9bb99..ceb8b36c4 100644 --- a/basemap/layers/railway/prepare.sql +++ b/basemap/layers/railway/prepare.sql @@ -13,7 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_railway AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway AS SELECT id, tags, geom FROM ( SELECT diff --git a/basemap/layers/railway/simplify.sql b/basemap/layers/railway/simplify.sql index 11e015f5a..a222fff5d 100644 --- a/basemap/layers/railway/simplify.sql +++ b/basemap/layers/railway/simplify.sql @@ -12,86 +12,86 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_railway_z20 AS +CREATE OR REPLACE VIEW osm_railway_z20 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z19 AS +CREATE OR REPLACE VIEW osm_railway_z19 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z18 AS +CREATE OR REPLACE VIEW osm_railway_z18 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z17 AS +CREATE OR REPLACE VIEW osm_railway_z17 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z16 AS +CREATE OR REPLACE VIEW osm_railway_z16 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z15 AS +CREATE OR REPLACE VIEW osm_railway_z15 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z14 AS +CREATE OR REPLACE VIEW osm_railway_z14 AS SELECT id, tags, geom FROM osm_railway; -CREATE VIEW osm_railway_z13 AS +CREATE OR REPLACE VIEW osm_railway_z13 AS SELECT id, tags, geom FROM osm_railway; -CREATE MATERIALIZED VIEW osm_railway_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z12 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 12)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z11 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 11)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z10 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 10)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z9 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 9)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z8 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 8)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z7 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 7)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z6 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 6)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z5 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 5)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z4 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 4)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z3 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 3)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z2 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 2)), 2)); -CREATE MATERIALIZED VIEW osm_railway_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_railway_z1 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_railway) AS osm_railway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 1)), 2)); diff --git a/basemap/layers/route/create.sql b/basemap/layers/route/create.sql index a996564c2..cb1f26266 100644 --- a/basemap/layers/route/create.sql +++ b/basemap/layers/route/create.sql @@ -13,7 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_route AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route AS SELECT id, tags, geom FROM ( SELECT diff --git a/basemap/layers/route/prepare.sql b/basemap/layers/route/prepare.sql index c70c24ce5..c789d1656 100644 --- a/basemap/layers/route/prepare.sql +++ b/basemap/layers/route/prepare.sql @@ -15,7 +15,7 @@ DROP MATERIALIZED VIEW IF EXISTS osm_route CASCADE; -CREATE MATERIALIZED VIEW osm_route AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route AS SELECT id, tags, geom FROM ( SELECT diff --git a/basemap/layers/route/simplify.sql b/basemap/layers/route/simplify.sql index ef5284247..43b49f70f 100644 --- a/basemap/layers/route/simplify.sql +++ b/basemap/layers/route/simplify.sql @@ -13,86 +13,86 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_route_z20 AS +CREATE OR REPLACE VIEW osm_route_z20 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z19 AS +CREATE OR REPLACE VIEW osm_route_z19 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z18 AS +CREATE OR REPLACE VIEW osm_route_z18 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z17 AS +CREATE OR REPLACE VIEW osm_route_z17 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z16 AS +CREATE OR REPLACE VIEW osm_route_z16 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z15 AS +CREATE OR REPLACE VIEW osm_route_z15 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z14 AS +CREATE OR REPLACE VIEW osm_route_z14 AS SELECT id, tags, geom FROM osm_route; -CREATE VIEW osm_route_z13 AS +CREATE OR REPLACE VIEW osm_route_z13 AS SELECT id, tags, geom FROM osm_route; -CREATE MATERIALIZED VIEW osm_route_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z12 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 12)), 2)); -CREATE MATERIALIZED VIEW osm_route_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z11 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 11)), 2)); -CREATE MATERIALIZED VIEW osm_route_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z10 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 10)), 2)); -CREATE MATERIALIZED VIEW osm_route_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z9 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 9)), 2)); -CREATE MATERIALIZED VIEW osm_route_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z8 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 8)), 2)); -CREATE MATERIALIZED VIEW osm_route_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z7 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 7)), 2)); -CREATE MATERIALIZED VIEW osm_route_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z6 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 6)), 2)); -CREATE MATERIALIZED VIEW osm_route_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z5 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 5)), 2)); -CREATE MATERIALIZED VIEW osm_route_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z4 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 4)), 2)); -CREATE MATERIALIZED VIEW osm_route_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z3 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 3)), 2)); -CREATE MATERIALIZED VIEW osm_route_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z2 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 2)), 2)); -CREATE MATERIALIZED VIEW osm_route_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_route_z1 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_route) AS osm_route WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 1)), 2)); diff --git a/basemap/layers/tourism/create.sql b/basemap/layers/tourism/create.sql index ad00b54fa..962c7dca5 100644 --- a/basemap/layers/tourism/create.sql +++ b/basemap/layers/tourism/create.sql @@ -1,2 +1,2 @@ -CREATE VIEW osm_tourism AS +CREATE OR REPLACE VIEW osm_tourism AS SELECT id, tags, geom FROM osm_relation WHERE tags ? 'tourism'; \ No newline at end of file diff --git a/basemap/layers/waterway/create.sql b/basemap/layers/waterway/create.sql index d3117d546..45605a2dd 100644 --- a/basemap/layers/waterway/create.sql +++ b/basemap/layers/waterway/create.sql @@ -13,7 +13,7 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE MATERIALIZED VIEW osm_waterway AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway AS WITH -- Filter the linestrings filtered AS ( diff --git a/basemap/layers/waterway/prepare.sql b/basemap/layers/waterway/prepare.sql index 1e079d36c..395aacd26 100644 --- a/basemap/layers/waterway/prepare.sql +++ b/basemap/layers/waterway/prepare.sql @@ -15,7 +15,7 @@ DROP MATERIALIZED VIEW IF EXISTS osm_waterway CASCADE; -CREATE MATERIALIZED VIEW osm_waterway AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway AS WITH -- Filter the linestrings filtered AS ( diff --git a/basemap/layers/waterway/simplify.sql b/basemap/layers/waterway/simplify.sql index 10182a56f..0575e6cd7 100644 --- a/basemap/layers/waterway/simplify.sql +++ b/basemap/layers/waterway/simplify.sql @@ -12,86 +12,86 @@ -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE VIEW osm_waterway_z20 AS +CREATE OR REPLACE VIEW osm_waterway_z20 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z19 AS +CREATE OR REPLACE VIEW osm_waterway_z19 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z18 AS +CREATE OR REPLACE VIEW osm_waterway_z18 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z17 AS +CREATE OR REPLACE VIEW osm_waterway_z17 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z16 AS +CREATE OR REPLACE VIEW osm_waterway_z16 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z15 AS +CREATE OR REPLACE VIEW osm_waterway_z15 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z14 AS +CREATE OR REPLACE VIEW osm_waterway_z14 AS SELECT id, tags, geom FROM osm_waterway; -CREATE VIEW osm_waterway_z13 AS +CREATE OR REPLACE VIEW osm_waterway_z13 AS SELECT id, tags, geom FROM osm_waterway; -CREATE MATERIALIZED VIEW osm_waterway_z12 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z12 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 12)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z11 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z11 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 11)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z10 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z10 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 10)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z9 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z9 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 9)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z8 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z8 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 8)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z7 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z7 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 7)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z6 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z6 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 6)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z5 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z5 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 5)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z4 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z4 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 4)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z3 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z3 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 3)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z2 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z2 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 2)), 2)); -CREATE MATERIALIZED VIEW osm_waterway_z1 AS +CREATE MATERIALIZED VIEW IF NOT EXISTS osm_waterway_z1 AS SELECT id, tags, geom FROM (SELECT id, tags, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM osm_waterway) AS osm_waterway WHERE geom IS NOT NULL AND (st_area(st_envelope(geom)) > power((78270 / power(2, 1)), 2));
