This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch optimize-postgres-tilestore in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit 9b469f45db6b9c97e6cb77d1c28ddbb31366ac91 Author: Bertil Chapuis <[email protected]> AuthorDate: Wed Oct 25 13:38:23 2023 +0200 Cleanup tests --- .../tilestore/postgres/PostgresTileStore.java | 22 ++---- .../tilestore/postgres/PostgresTileStoreTest.java | 79 ++-------------------- 2 files changed, 11 insertions(+), 90 deletions(-) diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java index e254e7e1..2160a889 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java @@ -81,7 +81,7 @@ public class PostgresTileStore implements TileStore { } - public static TileQuery prepareQuery(Tileset tileset, int zoom) { + protected static TileQuery prepareQuery(Tileset tileset, int zoom) { // Initialize a builder for the tile query var tileQuery = new StringBuilder(); tileQuery.append("SELECT ("); @@ -117,11 +117,10 @@ public class PostgresTileStore implements TileStore { .replace(";", "") .replace("?", "??") .replace("$zoom", String.valueOf(zoom)); - var queryWithParams = String.format(""" - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom, t.tags, t.id - FROM (%s) AS t - WHERE t.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096)) - """, sql); + var queryWithParams = String.format( + "SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom, t.tags, t.id " + + "FROM (%s) AS t WHERE t.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))", + sql); layerQuery.append(queryWithParams); // Increase the parameter count (e.g. ?) and query count @@ -162,16 +161,7 @@ public class PostgresTileStore implements TileStore { return new TileQuery(query, paramCount); } - public static class TileQuery { - - private final String query; - - private final int paramCount; - - public TileQuery(String query, int paramCount) { - this.query = query; - this.paramCount = paramCount; - } + public record TileQuery(String query, int paramCount) { public ByteBuffer execute(Connection connection, TileCoord tileCoord) throws SQLException, IOException { diff --git a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java index 0e01747f..2539d17a 100644 --- a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java +++ b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java @@ -21,7 +21,6 @@ import static org.junit.jupiter.api.Assertions.assertEquals; import java.util.List; import java.util.Map; -import org.apache.baremaps.tilestore.TileCoord; import org.apache.baremaps.vectortile.tileset.Tileset; import org.apache.baremaps.vectortile.tileset.TilesetLayer; import org.apache.baremaps.vectortile.tileset.TilesetQuery; @@ -30,8 +29,8 @@ import org.junit.jupiter.api.Test; class PostgresTileStoreTest { @Test - void sameQueries() { - Tileset tileset = new Tileset(); + void prepareQuery() { + var tileset = new Tileset(); tileset.setMinzoom(0); tileset.setMaxzoom(20); tileset.setVectorLayers(List.of( @@ -39,77 +38,9 @@ class PostgresTileStoreTest { List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table"))), new TilesetLayer("b", Map.of(), "", 0, 20, List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table"))))); - PostgresTileStore tileStore = new PostgresTileStore(null, tileset); - String query = tileStore.withQuery(new TileCoord(0, 0, 10)); + var query = PostgresTileStore.prepareQuery(tileset, 10); assertEquals( - """ - SELECT ((WITH mvtgeom AS ( - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id - FROM (SELECT id, tags, geom FROM table) AS t - WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) - ) SELECT ST_AsMVT(mvtgeom.*, 'a') FROM mvtgeom - ) || (WITH mvtgeom AS ( - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id - FROM (SELECT id, tags, geom FROM table) AS t - WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) - ) SELECT ST_AsMVT(mvtgeom.*, 'b') FROM mvtgeom - )) mvtTile""", - query); - } - - @Test - void differentConditions1() { - Tileset tileset = new Tileset(); - tileset.setMinzoom(0); - tileset.setMaxzoom(20); - tileset.setVectorLayers(List.of( - new TilesetLayer("a", Map.of(), "", 0, 20, - List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table"))), - new TilesetLayer("b", Map.of(), "", 0, 20, List - .of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table WHERE condition = 1"))))); - PostgresTileStore tileStore = new PostgresTileStore(null, tileset); - String query = tileStore.withQuery(new TileCoord(0, 0, 10)); - assertEquals(""" - SELECT ((WITH mvtgeom AS ( - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id - FROM (SELECT id, tags, geom FROM table) AS t - WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) - ) SELECT ST_AsMVT(mvtgeom.*, 'a') FROM mvtgeom - ) || (WITH mvtgeom AS ( - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id - FROM (SELECT id, tags, geom FROM table WHERE condition = 1) AS t - WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) - ) SELECT ST_AsMVT(mvtgeom.*, 'b') FROM mvtgeom - )) mvtTile""", - query); - } - - @Test - void differentConditions2() { - Tileset tileset = new Tileset(); - tileset.setMinzoom(0); - tileset.setMaxzoom(20); - tileset.setVectorLayers(List.of( - new TilesetLayer("a", Map.of(), "", 0, 20, - List.of( - new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table WHERE condition = 1"))), - new TilesetLayer("b", Map.of(), "", 0, 20, List - .of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table WHERE condition = 2"))))); - PostgresTileStore tileStore = new PostgresTileStore(null, tileset); - String query = tileStore.withQuery(new TileCoord(0, 0, 10)); - assertEquals( - """ - SELECT ((WITH mvtgeom AS ( - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id - FROM (SELECT id, tags, geom FROM table WHERE condition = 1) AS t - WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) - ) SELECT ST_AsMVT(mvtgeom.*, 'a') FROM mvtgeom - ) || (WITH mvtgeom AS ( - SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id - FROM (SELECT id, tags, geom FROM table WHERE condition = 2) AS t - WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) - ) SELECT ST_AsMVT(mvtgeom.*, 'b') FROM mvtgeom - )) mvtTile""", - query); + "SELECT ((WITH mvtGeom AS (SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom, t.tags, t.id FROM (SELECT id, tags, geom FROM table) AS t WHERE t.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))) SELECT ST_AsMVT(mvtGeom.*, 'a') FROM mvtGeom) || (WITH mvtGeom AS (SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom, t.tags, t.id FROM (SELECT id, tags, geom FROM table) AS t WHERE t.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))) SELECT ST_AsMVT(mv [...] + query.query()); } }
