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
The following commit(s) were added to refs/heads/optimize-postgres-tilestore by
this push:
new aaf81d19 Cleanup tests
aaf81d19 is described below
commit aaf81d192613a72501db6cdd1efbefd31fa458b8
Author: Bertil Chapuis <[email protected]>
AuthorDate: Wed Oct 25 13:38:23 2023 +0200
Cleanup tests
---
.../tilestore/postgres/PostgresTileStore.java | 20 ++----
.../tilestore/postgres/PostgresTileStoreTest.java | 79 ++--------------------
2 files changed, 9 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..e57bd51e 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,8 @@ 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 +159,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());
}
}
