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 00262643 Improve documentation and naming
00262643 is described below
commit 0026264385c0fe4381518e85bc05e798e9433e64
Author: Bertil Chapuis <[email protected]>
AuthorDate: Thu Oct 26 10:45:06 2023 +0200
Improve documentation and naming
---
.../tilestore/postgres/PostgresTileStore.java | 184 +++++++++++----------
.../tilestore/postgres/PostgresTileStoreTest.java | 2 +-
2 files changed, 101 insertions(+), 85 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 2160a889..a1125033 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
@@ -19,7 +19,6 @@ package org.apache.baremaps.tilestore.postgres;
import java.io.ByteArrayOutputStream;
-import java.io.IOException;
import java.io.OutputStream;
import java.nio.ByteBuffer;
import java.sql.*;
@@ -37,7 +36,7 @@ import org.slf4j.LoggerFactory;
/**
* A read-only {@code TileStore} implementation that uses the PostgreSQL to
generate vector tiles.
* This {@code TileStore} combines the input queries, identifies common table
expressions (CTE), and
- * generates a single optimized query that hits the database.
+ * generates a single optimized sql that hits the database.
*/
public class PostgresTileStore implements TileStore {
@@ -47,155 +46,172 @@ public class PostgresTileStore implements TileStore {
private final Tileset tileset;
+ /**
+ * Constructs a {@code PostgresTileStore}.
+ *
+ * @param datasource the datasource
+ * @param tileset the tileset
+ */
public PostgresTileStore(DataSource datasource, Tileset tileset) {
this.datasource = datasource;
this.tileset = tileset;
}
- private Map<Integer, TileQuery> cache = new ConcurrentHashMap<>();
+ /**
+ * A cache of queries.
+ */
+ private Map<Integer, Query> cache = new ConcurrentHashMap<>();
+
+ /**
+ * A record that holds the sql of a prepared statement and the number of
parameters.
+ *
+ * @param sql
+ * @param parameters
+ */
+ protected record Query(String sql, int parameters) {
+ }
@Override
public ByteBuffer read(TileCoord tileCoord) throws TileStoreException {
+ var start = System.currentTimeMillis();
+
+ // Prepare and cache the query
var query = cache.computeIfAbsent(tileCoord.z(), z ->
prepareQuery(tileset, z));
- try (var connection = datasource.getConnection()) {
- return query.execute(connection, tileCoord);
+
+ // Fetch and compress the tile data
+ try (var connection = datasource.getConnection();
+ ByteArrayOutputStream data = new ByteArrayOutputStream();
+ var statement = connection.prepareStatement(query.sql())) {
+
+ // Set the parameters for the tile
+ for (int i = 0; i < query.parameters(); i += 3) {
+ statement.setInt(i + 1, tileCoord.z());
+ statement.setInt(i + 2, tileCoord.x());
+ statement.setInt(i + 3, tileCoord.y());
+ }
+
+ try (ResultSet resultSet = statement.executeQuery();
+ OutputStream gzip = new GZIPOutputStream(data)) {
+ while (resultSet.next()) {
+ byte[] bytes = resultSet.getBytes(1);
+ gzip.write(bytes);
+ }
+ }
+
+ // Log slow queries (> 10s)
+ long stop = System.currentTimeMillis();
+ long duration = stop - start;
+ if (duration > 10_000) {
+ logger.warn("Executed sql for tile {} in {} ms", tileCoord, duration);
+ }
+
+ return ByteBuffer.wrap(data.toByteArray());
+
} catch (Exception e) {
throw new TileStoreException(e);
}
}
/**
- * This operation is not supported.
- */
- @Override
- public void write(TileCoord tileCoord, ByteBuffer blob) {
- throw new UnsupportedOperationException("The postgis tile store is read
only");
- }
-
- /**
- * This operation is not supported.
+ * Prepare the sql query for a given tileset and zoom level.
+ *
+ * @param tileset the tileset
+ * @param zoom the zoom level
+ * @return
*/
- @Override
- public void delete(TileCoord tileCoord) {
- throw new UnsupportedOperationException("The postgis tile store is read
only");
- }
-
-
- protected static TileQuery prepareQuery(Tileset tileset, int zoom) {
- // Initialize a builder for the tile query
- var tileQuery = new StringBuilder();
- tileQuery.append("SELECT (");
+ protected static Query prepareQuery(Tileset tileset, int zoom) {
+ // Initialize a builder for the tile sql
+ var tileSql = new StringBuilder();
+ tileSql.append("SELECT (");
// Iterate over the layers and keep track of the number of layers and
parameters included in the
- // final query
+ // final sql
var layers = tileset.getVectorLayers();
var layerCount = 0;
var paramCount = 0;
for (var layer : layers) {
- // Initialize a builder for the layer query
- var layerQuery = new StringBuilder();
+ // Initialize a builder for the layer sql
+ var layerSql = new StringBuilder();
var layerHead = "(WITH mvtGeom AS (";
- layerQuery.append(layerHead);
+ layerSql.append(layerHead);
// Iterate over the queries and keep track of the number of queries
included in the final
- // query
+ // sql
var queries = layer.getQueries();
var queryCount = 0;
for (var query : queries) {
- // Only include the query if the zoom level is in the range
+ // Only include the sql if the zoom level is in the range
if (query.getMinzoom() <= zoom && zoom < query.getMaxzoom()) {
// Add a union between queries
if (queryCount > 0) {
- layerQuery.append("UNION ");
+ layerSql.append("UNION ");
}
- // Add the query to the layer query
- var sql = query.getSql()
+ // Add the sql to the layer sql
+ var querySql = query.getSql()
.replace(";", "")
.replace("?", "??")
.replace("$zoom", String.valueOf(zoom));
- var queryWithParams = String.format(
+ var querySqlWithParams = 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);
+ querySql);
+ layerSql.append(querySqlWithParams);
- // Increase the parameter count (e.g. ?) and query count
+ // Increase the parameter count (e.g. ?) and sql count
paramCount += 6;
queryCount++;
}
}
- // Add the tail of the layer query
+ // Add the tail of the layer sql
var layerQueryTail =
String.format(") SELECT ST_AsMVT(mvtGeom.*, '%s') FROM mvtGeom)",
layer.getId());
- layerQuery.append(layerQueryTail);
+ layerSql.append(layerQueryTail);
- // Only include the layer query if queries were included for this layer
+ // Only include the layer sql if queries were included for this layer
if (queryCount > 0) {
// Add the concatenation between layer queries
if (layerCount > 0) {
- tileQuery.append(" || ");
+ tileSql.append(" || ");
}
- // Add the layer query to the mvt query
- tileQuery.append(layerQuery);
+ // Add the layer sql to the mvt sql
+ tileSql.append(layerSql);
// Increase the layer count
layerCount++;
}
}
- // Add the tail of the tile query
+ // Add the tail of the tile sql
var tileQueryTail = ") mvtTile";
- tileQuery.append(tileQueryTail);
+ tileSql.append(tileQueryTail);
- // Log the resulting query
- var query = tileQuery.toString().replace("\n", " ");
- logger.debug("query: {}", query);
+ // Log the resulting sql
+ var sql = tileSql.toString().replace("\n", " ");
+ logger.debug("sql: {}", sql);
- return new TileQuery(query, paramCount);
+ return new Query(sql, paramCount);
}
- public record TileQuery(String query, int paramCount) {
-
- public ByteBuffer execute(Connection connection, TileCoord tileCoord)
- throws SQLException, IOException {
- long start = System.currentTimeMillis();
- try (var statement = connection.prepareStatement(query)) {
-
- // Set the parameters for the tile
- for (int i = 0; i < paramCount; i += 3) {
- statement.setInt(i + 1, tileCoord.z());
- statement.setInt(i + 2, tileCoord.x());
- statement.setInt(i + 3, tileCoord.y());
- }
-
- // Fetch and compress the tile data
- try (ByteArrayOutputStream data = new ByteArrayOutputStream();) {
- try (ResultSet resultSet = statement.executeQuery();
- OutputStream gzip = new GZIPOutputStream(data)) {
- while (resultSet.next()) {
- byte[] bytes = resultSet.getBytes(1);
- gzip.write(bytes);
- }
- }
- return ByteBuffer.wrap(data.toByteArray());
-
- } finally {
- // Log slow queries (> 10s)
- long stop = System.currentTimeMillis();
- long duration = stop - start;
- if (duration > 10_000) {
- logger.warn("Executed query for tile {} in {} ms", tileCoord,
duration);
- }
- }
- }
- }
+ /**
+ * This operation is not supported.
+ */
+ @Override
+ public void write(TileCoord tileCoord, ByteBuffer blob) {
+ throw new UnsupportedOperationException("The postgis tile store is read
only");
}
+ /**
+ * This operation is not supported.
+ */
+ @Override
+ public void delete(TileCoord tileCoord) {
+ throw new UnsupportedOperationException("The postgis tile store is read
only");
+ }
}
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 2539d17a..f146a2cf 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
@@ -41,6 +41,6 @@ class PostgresTileStoreTest {
var query = PostgresTileStore.prepareQuery(tileset, 10);
assertEquals(
"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());
+ query.sql());
}
}
