This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch 583-geoBoundaries in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit dee836931710b7a29a566bae56c2d91d0a2cdfa0 Author: Bertil Chapuis <[email protected]> AuthorDate: Sat Feb 11 23:31:33 2023 +0100 Add geoBoundaries dataset to basemap --- .run/basemap-dev.run.xml | 22 + .../storage/postgres/PostgresDatabase.java | 4 +- basemap/layers/boundary/line.js | 4 + basemap/layers/boundary/tileset.js | 12 +- basemap/queries/globaladm0_clean.sql | 22 + basemap/queries/globaladm0_index.sql | 16 + basemap/queries/globaladm0_simplify.sql | 64 +++ basemap/queries/globaladm1_clean.sql | 22 + basemap/queries/globaladm1_index.sql | 16 + basemap/queries/globaladm1_simplify.sql | 64 +++ basemap/queries/globaladm2_clean.sql | 22 + basemap/queries/globaladm2_index.sql | 16 + basemap/queries/globaladm2_simplify.sql | 64 +++ basemap/tileset.js | 2 +- basemap/workflow.js | 600 ++++++++++++--------- 15 files changed, 695 insertions(+), 255 deletions(-) diff --git a/.run/basemap-dev.run.xml b/.run/basemap-dev.run.xml new file mode 100644 index 00000000..579f21a4 --- /dev/null +++ b/.run/basemap-dev.run.xml @@ -0,0 +1,22 @@ +<component name="ProjectRunConfigurationManager"> + <configuration default="false" name="basemap-dev" type="Application" factoryName="Application"> + <option name="MAIN_CLASS_NAME" value="org.apache.baremaps.cli.Baremaps" /> + <module name="baremaps-cli" /> + <option name="PROGRAM_PARAMETERS" value="map dev --database jdbc:postgresql://localhost:5432/baremaps?user=baremaps&password=baremaps --tileset tileset.js --style style.js" /> + <option name="WORKING_DIRECTORY" value="$PROJECT_DIR$/basemap" /> + <extension name="coverage"> + <pattern> + <option name="PATTERN" value="org.apache.baremaps.server.ogcapi.*" /> + <option name="ENABLED" value="true" /> + </pattern> + </extension> + <extension name="software.aws.toolkits.jetbrains.core.execution.JavaAwsConnectionExtension"> + <option name="credential" /> + <option name="region" /> + <option name="useCurrentConnection" value="false" /> + </extension> + <method v="2"> + <option name="Make" enabled="true" /> + </method> + </configuration> +</component> \ No newline at end of file diff --git a/baremaps-core/src/main/java/org/apache/baremaps/storage/postgres/PostgresDatabase.java b/baremaps-core/src/main/java/org/apache/baremaps/storage/postgres/PostgresDatabase.java index 22a58ef6..6aeab076 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/storage/postgres/PostgresDatabase.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/storage/postgres/PostgresDatabase.java @@ -245,7 +245,7 @@ public class PostgresDatabase implements WritableAggregate { var type = featureSet.getType(); try (var connection = dataSource.getConnection(); var statement = connection.createStatement()) { - statement.executeQuery(String.format("DROP TABLE IF EXISTS %s", type.getName())); + statement.executeQuery(String.format("DROP TABLE IF EXISTS %s CASCADE", type.getName())); } catch (SQLException e) { throw new RuntimeException(e); } @@ -255,7 +255,7 @@ public class PostgresDatabase implements WritableAggregate { } private String dropTable(FeatureType type) { - return String.format("DROP TABLE IF EXISTS %s", type.getName()); + return String.format("DROP TABLE IF EXISTS %s CASCADE", type.getName()); } } diff --git a/basemap/layers/boundary/line.js b/basemap/layers/boundary/line.js index 55590dd2..de5f429c 100644 --- a/basemap/layers/boundary/line.js +++ b/basemap/layers/boundary/line.js @@ -1,6 +1,10 @@ import {asLayerObject, withSortKeys} from "../../utils/utils.js"; let directives = [ + { + filter: ['==', ['get', 'admin_level'], "0"], + 'line-color': 'rgb(207, 155, 203)', + }, { filter: ['==', ['get', 'admin_level'], "1"], 'line-color': 'rgb(207, 155, 203)', diff --git a/basemap/layers/boundary/tileset.js b/basemap/layers/boundary/tileset.js index 0064584a..8d305424 100644 --- a/basemap/layers/boundary/tileset.js +++ b/basemap/layers/boundary/tileset.js @@ -3,18 +3,18 @@ export default { queries: [ { minzoom: 1, - maxzoom: 5, + maxzoom: 6, sql: - "SELECT id, tags, geom FROM osm_boundary_z$zoom WHERE tags ->> 'boundary' IN ('administrative') AND tags ->> 'admin_level' IN ('1', '2')", + "SELECT fid as id, jsonb_build_object('boundary', 'administrative', 'admin_level', '0') as tags, geom FROM globaladm0_z$zoom", }, { - minzoom: 5, - maxzoom: 13, + minzoom: 6, + maxzoom: 14, sql: - "SELECT id, tags, geom FROM osm_boundary_z$zoom WHERE tags ->> 'boundary' IN ('administrative') AND tags ->> 'admin_level' IN ('1', '2', '3', '4')", + "SELECT fid as id, jsonb_build_object('boundary', 'administrative', 'admin_level', '1') as tags, geom FROM globaladm1_z$zoom", }, { - minzoom: 13, + minzoom: 14, maxzoom: 20, sql: "SELECT id, tags, geom FROM osm_ways_z$zoom WHERE tags ? 'boundary'", diff --git a/basemap/queries/globaladm0_clean.sql b/basemap/queries/globaladm0_clean.sql new file mode 100644 index 00000000..75af7114 --- /dev/null +++ b/basemap/queries/globaladm0_clean.sql @@ -0,0 +1,22 @@ +DROP VIEW IF EXISTS globaladm0_z20 CASCADE; +DROP VIEW IF EXISTS globaladm0_z19 CASCADE; +DROP VIEW IF EXISTS globaladm0_z18 CASCADE; +DROP VIEW IF EXISTS globaladm0_z17 CASCADE; +DROP VIEW IF EXISTS globaladm0_z16 CASCADE; +DROP VIEW IF EXISTS globaladm0_z15 CASCADE; +DROP VIEW IF EXISTS globaladm0_z14 CASCADE; + +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z13 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z12 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z11 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z10 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z9 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z8 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z7 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z6 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z5 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z4 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z3 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z2 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm0_z1 CASCADE; + diff --git a/basemap/queries/globaladm0_index.sql b/basemap/queries/globaladm0_index.sql new file mode 100644 index 00000000..41e6288b --- /dev/null +++ b/basemap/queries/globaladm0_index.sql @@ -0,0 +1,16 @@ +CREATE INDEX IF NOT EXISTS globaladm0_index ON globaladm0 USING SPGIST(geom); +CREATE INDEX IF NOT EXISTS globaladm0_z12_index ON globaladm0_z12 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z11_index ON globaladm0_z11 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z10_index ON globaladm0_z10 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z9_index ON globaladm0_z9 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z8_index ON globaladm0_z8 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z7_index ON globaladm0_z7 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z6_index ON globaladm0_z6 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z5_index ON globaladm0_z5 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z4_index ON globaladm0_z4 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z3_index ON globaladm0_z3 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z2_index ON globaladm0_z2 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z1_index ON globaladm0_z1 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm0_z0_index ON globaladm0_z0 USING SPGIST (geom); + + diff --git a/basemap/queries/globaladm0_simplify.sql b/basemap/queries/globaladm0_simplify.sql new file mode 100644 index 00000000..37b239fc --- /dev/null +++ b/basemap/queries/globaladm0_simplify.sql @@ -0,0 +1,64 @@ +CREATE VIEW globaladm0_z20 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z19 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z18 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z17 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z16 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z15 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z14 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE VIEW globaladm0_z13 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z12 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z11 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z10 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z9 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z8 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z7 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z6 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z5 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z4 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z3 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z2 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z1 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM globaladm0; + +CREATE MATERIALIZED VIEW globaladm0_z0 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 0)) AS geom FROM globaladm0; + + diff --git a/basemap/queries/globaladm1_clean.sql b/basemap/queries/globaladm1_clean.sql new file mode 100644 index 00000000..2f427c5f --- /dev/null +++ b/basemap/queries/globaladm1_clean.sql @@ -0,0 +1,22 @@ +DROP VIEW IF EXISTS globaladm1_z20 CASCADE; +DROP VIEW IF EXISTS globaladm1_z19 CASCADE; +DROP VIEW IF EXISTS globaladm1_z18 CASCADE; +DROP VIEW IF EXISTS globaladm1_z17 CASCADE; +DROP VIEW IF EXISTS globaladm1_z16 CASCADE; +DROP VIEW IF EXISTS globaladm1_z15 CASCADE; +DROP VIEW IF EXISTS globaladm1_z14 CASCADE; + +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z13 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z12 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z11 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z10 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z9 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z8 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z7 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z6 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z5 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z4 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z3 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z2 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm1_z1 CASCADE; + diff --git a/basemap/queries/globaladm1_index.sql b/basemap/queries/globaladm1_index.sql new file mode 100644 index 00000000..8f56089a --- /dev/null +++ b/basemap/queries/globaladm1_index.sql @@ -0,0 +1,16 @@ +CREATE INDEX IF NOT EXISTS globaladm1_index ON globaladm1 USING SPGIST(geom); +CREATE INDEX IF NOT EXISTS globaladm1_z12_index ON globaladm1_z12 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z11_index ON globaladm1_z11 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z10_index ON globaladm1_z10 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z9_index ON globaladm1_z9 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z8_index ON globaladm1_z8 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z7_index ON globaladm1_z7 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z6_index ON globaladm1_z6 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z5_index ON globaladm1_z5 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z4_index ON globaladm1_z4 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z3_index ON globaladm1_z3 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z2_index ON globaladm1_z2 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z1_index ON globaladm1_z1 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm1_z0_index ON globaladm1_z0 USING SPGIST (geom); + + diff --git a/basemap/queries/globaladm1_simplify.sql b/basemap/queries/globaladm1_simplify.sql new file mode 100644 index 00000000..0ce52f03 --- /dev/null +++ b/basemap/queries/globaladm1_simplify.sql @@ -0,0 +1,64 @@ +CREATE VIEW globaladm1_z20 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z19 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z18 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z17 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z16 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z15 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z14 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE VIEW globaladm1_z13 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z12 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z11 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z10 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z9 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z8 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z7 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z6 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z5 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z4 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z3 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z2 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z1 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM globaladm1; + +CREATE MATERIALIZED VIEW globaladm1_z0 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 0)) AS geom FROM globaladm1; + + diff --git a/basemap/queries/globaladm2_clean.sql b/basemap/queries/globaladm2_clean.sql new file mode 100644 index 00000000..96353426 --- /dev/null +++ b/basemap/queries/globaladm2_clean.sql @@ -0,0 +1,22 @@ +DROP VIEW IF EXISTS globaladm2_z20 CASCADE; +DROP VIEW IF EXISTS globaladm2_z19 CASCADE; +DROP VIEW IF EXISTS globaladm2_z18 CASCADE; +DROP VIEW IF EXISTS globaladm2_z17 CASCADE; +DROP VIEW IF EXISTS globaladm2_z16 CASCADE; +DROP VIEW IF EXISTS globaladm2_z15 CASCADE; +DROP VIEW IF EXISTS globaladm2_z14 CASCADE; + +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z13 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z12 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z11 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z10 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z9 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z8 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z7 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z6 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z5 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z4 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z3 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z2 CASCADE; +DROP MATERIALIZED VIEW IF EXISTS globaladm2_z1 CASCADE; + diff --git a/basemap/queries/globaladm2_index.sql b/basemap/queries/globaladm2_index.sql new file mode 100644 index 00000000..7b7f80d5 --- /dev/null +++ b/basemap/queries/globaladm2_index.sql @@ -0,0 +1,16 @@ +CREATE INDEX IF NOT EXISTS globaladm2_index ON globaladm2 USING SPGIST(geom); +CREATE INDEX IF NOT EXISTS globaladm2_z12_index ON globaladm2_z12 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z11_index ON globaladm2_z11 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z10_index ON globaladm2_z10 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z9_index ON globaladm2_z9 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z8_index ON globaladm2_z8 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z7_index ON globaladm2_z7 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z6_index ON globaladm2_z6 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z5_index ON globaladm2_z5 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z4_index ON globaladm2_z4 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z3_index ON globaladm2_z3 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z2_index ON globaladm2_z2 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z1_index ON globaladm2_z1 USING SPGIST (geom); +CREATE INDEX IF NOT EXISTS globaladm2_z0_index ON globaladm2_z0 USING SPGIST (geom); + + diff --git a/basemap/queries/globaladm2_simplify.sql b/basemap/queries/globaladm2_simplify.sql new file mode 100644 index 00000000..6657cb5e --- /dev/null +++ b/basemap/queries/globaladm2_simplify.sql @@ -0,0 +1,64 @@ +CREATE VIEW globaladm2_z20 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z19 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z18 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z17 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z16 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z15 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z14 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE VIEW globaladm2_z13 AS +SELECT fid, shapegroup, shapetype, geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z12 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z11 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 11)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z10 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 10)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z9 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 9)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z8 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 8)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z7 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 7)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z6 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 6)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z5 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 5)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z4 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 4)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z3 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 3)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z2 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 2)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z1 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 1)) AS geom FROM globaladm2; + +CREATE MATERIALIZED VIEW globaladm2_z0 AS +SELECT fid, shapegroup, shapetype, st_simplifypreservetopology(geom, 78270 / power(2, 0)) AS geom FROM globaladm2; + + diff --git a/basemap/tileset.js b/basemap/tileset.js index 16a366bf..28ec0130 100644 --- a/basemap/tileset.js +++ b/basemap/tileset.js @@ -27,7 +27,7 @@ export default { "tiles": [ `${config.host}/tiles/{z}/{x}/{y}.mvt` ], - attribution: '© <a href="https://www.openstreetmap.org/";>OpenStreetMap</a> contributors', + attribution: '© <a href="https://www.openstreetmap.org/";>OpenStreetMap</a> © <a href="https://www.geoboundaries.org";>geoBoundaries</a>', "vector_layers": [ aerialway, aeroway, diff --git a/basemap/workflow.js b/basemap/workflow.js index cb2a11b8..2952732e 100644 --- a/basemap/workflow.js +++ b/basemap/workflow.js @@ -2,319 +2,427 @@ import config from "./config.js"; export default { "steps": [ + // { + // "id": "natural-earth", + // "needs": [], + // "tasks": [ + // { + // "type": "DownloadUrl", + // "url": "https://naciscdn.org/naturalearth/packages/natural_earth_vector.gpkg.zip";, + // "path": "data/natural_earth_vector.gpkg.zip" + // }, + // { + // "type": "UnzipFile", + // "file": "data/natural_earth_vector.gpkg.zip", + // "directory": "data/natural_earth_vector" + // }, + // { + // "type": "ImportGeoPackage", + // "file": "data/natural_earth_vector/packages/natural_earth_vector.gpkg", + // "database": config.database, + // "sourceSRID": 4326, + // "targetSRID": 3857 + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/ne_index.sql", + // "database": config.database, + // "parallel": true, + // } + // ] + // }, { - "id": "natural-earth", + "id": "globaladm0", "needs": [], "tasks": [ { "type": "DownloadUrl", - "url": "https://naciscdn.org/naturalearth/packages/natural_earth_vector.gpkg.zip";, - "path": "data/natural_earth_vector.gpkg.zip" - }, - { - "type": "UnzipFile", - "file": "data/natural_earth_vector.gpkg.zip", - "directory": "data/natural_earth_vector" + "url": "https://github.com/wmgeolab/geoBoundaries/raw/main/releaseData/CGAZ/geoBoundariesCGAZ_ADM0.gpkg";, + "path": "data/geoBoundariesCGAZ_ADM0.gpkg" }, { "type": "ImportGeoPackage", - "file": "data/natural_earth_vector/packages/natural_earth_vector.gpkg", + "file": "data/geoBoundariesCGAZ_ADM0.gpkg", "database": config.database, "sourceSRID": 4326, "targetSRID": 3857 }, { "type": "ExecuteSql", - "file": "queries/ne_index.sql", + "file": "queries/globaladm0_clean.sql", "database": config.database, "parallel": true, - } - ] - }, - { - "id": "water-polygons", - "needs": [], - "tasks": [ - { - "type": "DownloadUrl", - "url": "https://osmdata.openstreetmap.de/download/water-polygons-split-3857.zip";, - "path": "data/water-polygons-split-3857.zip" - }, - { - "type": "UnzipFile", - "file": "data/water-polygons-split-3857.zip", - "directory": "data" - }, - { - "type": "ImportShapefile", - "file": "data/water-polygons-split-3857/water_polygons.shp", - "database": config.database, - "sourceSRID": 3857, - "targetSRID": 3857 }, { "type": "ExecuteSql", - "file": "queries/osm_water_index.sql", - "database": "jdbc:postgresql://localhost:5432/baremaps?&user=baremaps&password=baremaps" - } - ] - }, - { - "id": "simplified-water-polygons", - "needs": [], - "tasks": [ - { - "type": "DownloadUrl", - "url": "https://osmdata.openstreetmap.de/download/simplified-water-polygons-split-3857.zip";, - "path": "data/simplified-water-polygons-split-3857.zip" - }, - { - "type": "UnzipFile", - "file": "data/simplified-water-polygons-split-3857.zip", - "directory": "data" - }, - { - "type": "ImportShapefile", - "file": "data/simplified-water-polygons-split-3857/simplified_water_polygons.shp", + "file": "queries/globaladm0_simplify.sql", "database": config.database, - "sourceSRID": 3857, - "targetSRID": 3857 + "parallel": true, }, { "type": "ExecuteSql", - "file": "queries/osm_simplified_water_index.sql", + "file": "queries/globaladm0_index.sql", "database": config.database, - }, + "parallel": true, + } ] }, { - "id": "openstreetmap-data", + "id": "globaladm1", "needs": [], "tasks": [ { "type": "DownloadUrl", - "url": "https://download.geofabrik.de/europe/switzerland-latest.osm.pbf";, - "path": "data/data.osm.pbf" - }, - { - "type": "ImportOpenStreetMap", - "file": "data/data.osm.pbf", - "database": config.database, - "databaseSrid": 3857 + "url": "https://github.com/wmgeolab/geoBoundaries/raw/main/releaseData/CGAZ/geoBoundariesCGAZ_ADM1.gpkg";, + "path": "data/geoBoundariesCGAZ_ADM1.gpkg" }, - ] - }, - { - "id": "openstreetmap-nodes", - "needs": ["openstreetmap-data"], - "tasks": [ { - "type": "ExecuteSql", - "file": "queries/osm_nodes_clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_nodes_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_nodes_simplify.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_nodes_index.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-ways", - "needs": ["openstreetmap-data"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_ways_clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_ways_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_ways_simplify.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_ways_index.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-relations", - "needs": ["openstreetmap-data"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_relations_clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_relations_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_relations_simplify.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_relations_index.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-linestring", - "needs": ["openstreetmap-ways"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_linestring.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-polygon", - "needs": ["openstreetmap-ways", "openstreetmap-relations"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_polygon_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_polygon_index.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-boundary", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_boundary_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_boundary_simplify.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_boundary_index.sql", + "type": "ImportGeoPackage", + "file": "data/geoBoundariesCGAZ_ADM1.gpkg", "database": config.database, + "sourceSRID": 4326, + "targetSRID": 3857 }, - ] - }, - { - "id": "openstreetmap-highway", - "needs": ["openstreetmap-linestring"], - "tasks": [ { "type": "ExecuteSql", - "file": "queries/osm_highway_prepare.sql", + "file": "queries/globaladm1_clean.sql", "database": config.database, + "parallel": true, }, { "type": "ExecuteSql", - "file": "queries/osm_highway_simplify.sql", + "file": "queries/globaladm1_simplify.sql", "database": config.database, + "parallel": true, }, { "type": "ExecuteSql", - "file": "queries/osm_highway_index.sql", + "file": "queries/globaladm1_index.sql", "database": config.database, - }, + "parallel": true, + } ] }, { - "id": "openstreetmap-railway", - "needs": ["openstreetmap-linestring"], + "id": "globaladm2", + "needs": [], "tasks": [ { - "type": "ExecuteSql", - "file": "queries/osm_railway_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_railway_simplify.sql", - "database": config.database, + "type": "DownloadUrl", + "url": "https://github.com/wmgeolab/geoBoundaries/raw/main/releaseData/CGAZ/geoBoundariesCGAZ_ADM2.gpkg";, + "path": "data/geoBoundariesCGAZ_ADM2.gpkg" }, { - "type": "ExecuteSql", - "file": "queries/osm_railway_index.sql", + "type": "ImportGeoPackage", + "file": "data/geoBoundariesCGAZ_ADM2.gpkg", "database": config.database, + "sourceSRID": 4326, + "targetSRID": 3857 }, - ] - }, - { - "id": "openstreetmap-natural", - "needs": ["openstreetmap-polygon"], - "tasks": [ { "type": "ExecuteSql", - "file": "queries/osm_natural_prepare.sql", + "file": "queries/globaladm2_clean.sql", "database": config.database, + "parallel": true, }, { "type": "ExecuteSql", - "file": "queries/osm_natural_simplify.sql", + "file": "queries/globaladm2_simplify.sql", "database": config.database, + "parallel": true, }, { "type": "ExecuteSql", - "file": "queries/osm_natural_index.sql", + "file": "queries/globaladm2_index.sql", "database": config.database, - "parallel": true - }, + "parallel": true, + } ] }, - { - "id": "openstreetmap-landuse", - "needs": ["openstreetmap-polygon"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_landuse_prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_landuse_simplify.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "queries/osm_landuse_index.sql", - "database": config.database, - "parallel": true - }, - ] - } + // { + // "id": "water-polygons", + // "needs": [], + // "tasks": [ + // { + // "type": "DownloadUrl", + // "url": "https://osmdata.openstreetmap.de/download/water-polygons-split-3857.zip";, + // "path": "data/water-polygons-split-3857.zip" + // }, + // { + // "type": "UnzipFile", + // "file": "data/water-polygons-split-3857.zip", + // "directory": "data" + // }, + // { + // "type": "ImportShapefile", + // "file": "data/water-polygons-split-3857/water_polygons.shp", + // "database": config.database, + // "sourceSRID": 3857, + // "targetSRID": 3857 + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_water_index.sql", + // "database": "jdbc:postgresql://localhost:5432/baremaps?&user=baremaps&password=baremaps" + // } + // ] + // }, + // { + // "id": "simplified-water-polygons", + // "needs": [], + // "tasks": [ + // { + // "type": "DownloadUrl", + // "url": "https://osmdata.openstreetmap.de/download/simplified-water-polygons-split-3857.zip";, + // "path": "data/simplified-water-polygons-split-3857.zip" + // }, + // { + // "type": "UnzipFile", + // "file": "data/simplified-water-polygons-split-3857.zip", + // "directory": "data" + // }, + // { + // "type": "ImportShapefile", + // "file": "data/simplified-water-polygons-split-3857/simplified_water_polygons.shp", + // "database": config.database, + // "sourceSRID": 3857, + // "targetSRID": 3857 + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_simplified_water_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-data", + // "needs": [], + // "tasks": [ + // { + // "type": "DownloadUrl", + // "url": "https://download.geofabrik.de/europe/switzerland-latest.osm.pbf";, + // "path": "data/data.osm.pbf" + // }, + // { + // "type": "ImportOpenStreetMap", + // "file": "data/data.osm.pbf", + // "database": config.database, + // "databaseSrid": 3857 + // }, + // ] + // }, + // { + // "id": "openstreetmap-nodes", + // "needs": ["openstreetmap-data"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_nodes_clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_nodes_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_nodes_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_nodes_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-ways", + // "needs": ["openstreetmap-data"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_ways_clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_ways_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_ways_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_ways_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-relations", + // "needs": ["openstreetmap-data"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_relations_clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_relations_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_relations_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_relations_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-linestring", + // "needs": ["openstreetmap-ways"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_linestring.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-polygon", + // "needs": ["openstreetmap-ways", "openstreetmap-relations"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_polygon_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_polygon_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-boundary", + // "needs": ["openstreetmap-linestring"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_boundary_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_boundary_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_boundary_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-highway", + // "needs": ["openstreetmap-linestring"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_highway_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_highway_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_highway_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-railway", + // "needs": ["openstreetmap-linestring"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_railway_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_railway_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_railway_index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-natural", + // "needs": ["openstreetmap-polygon"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_natural_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_natural_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_natural_index.sql", + // "database": config.database, + // "parallel": true + // }, + // ] + // }, + // { + // "id": "openstreetmap-landuse", + // "needs": ["openstreetmap-polygon"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_landuse_prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_landuse_simplify.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "queries/osm_landuse_index.sql", + // "database": config.database, + // "parallel": true + // }, + // ] + // } ] }
