This is an automated email from the ASF dual-hosted git repository.
bchapuis pushed a commit to branch 913-temporary-solution
in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
The following commit(s) were added to refs/heads/913-temporary-solution by this
push:
new edbe3657d Remove duplication
edbe3657d is described below
commit edbe3657d3292e36acde34fb150bebb0a877fe0d
Author: Bertil Chapuis <[email protected]>
AuthorDate: Sat Jan 11 16:37:02 2025 +0100
Remove duplication
---
.../tilestore/postgres/PostgresTileStore.java | 174 +++++++--------------
1 file changed, 54 insertions(+), 120 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 4eca9711f..b6191c37d 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
@@ -127,22 +127,8 @@ public class PostgresTileStore implements
TileStore<ByteBuffer> {
* @param zoom the zoom level
* @return the prepared query
*/
- protected Query prepareQuery(int zoom) {
- if (postgresVersion >= 16) {
- return prepareNewQuery(zoom);
- } else {
- return prepareLegacyQuery(zoom);
- }
- }
-
- /**
- * Prepare the sql query for a given zoom level that uses the new version of
postgresql (>= 16).
- *
- * @param zoom the zoom level
- * @return the prepared query
- */
@SuppressWarnings("squid:S3776")
- private Query prepareNewQuery(int zoom) {
+ protected Query prepareQuery(int zoom) {
// Initialize a builder for the tile sql
var tileSql = new StringBuilder();
tileSql.append("SELECT ");
@@ -179,17 +165,10 @@ public class PostgresTileStore implements
TileStore<ByteBuffer> {
.replace(";", "")
.replace("?", "??")
.replace("$zoom", String.valueOf(zoom));
- var querySqlWithParams = String.format(
- """
- SELECT
- mvtData.id AS id,
- mvtData.tags - 'id' AS tags,
- ST_AsMVTGeom(mvtData.geom, ST_TileEnvelope(?, ?, ?)) AS geom
- FROM (%s) AS mvtData
- WHERE mvtData.geom IS NOT NULL
- AND mvtData.geom && ST_TileEnvelope(?, ?, ?, margin =>
(64.0/4096))
- """,
- querySql);
+
+ var querySqlWithParams =
+ postgresVersion >= 16 ? prepareNewQuery(querySql) :
prepareLegacyQuery(querySql);
+
layerSql.append(querySqlWithParams);
// Increase the parameter count (e.g. ?) and sql count
@@ -223,113 +202,68 @@ public class PostgresTileStore implements
TileStore<ByteBuffer> {
tileSql.append(tileQueryTail);
// Format the sql query
- var sql = tileSql.toString().replace("\n", " ");
+ var sql = tileSql.toString().replaceAll("\\s+", " ");
return new Query(sql, paramCount);
}
/**
- * Prepare the sql query for a given zoom level that uses the legacy
versions of postgresql (<
- * 16).
+ * Prepare the sql query for the new versions of postgresql (>= 16).
+ * <p>
+ * Recent versions of the postgresql database better optimize subqueries.
Using subqueries is more
+ * robust and allows for more complex queries.
*
- * @param zoom the zoom level
+ * @param sql the sql query
* @return the prepared query
*/
@SuppressWarnings("squid:S3776")
- private Query prepareLegacyQuery(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 sql
- var layers = tileset.getVectorLayers();
- var layerCount = 0;
- var paramCount = 0;
- for (var layer : layers) {
-
- // Initialize a builder for the layer sql
- var layerSql = new StringBuilder();
- var layerHead = String.format("(SELECT ST_AsMVT(mvtGeom.*, '%s') FROM
(", layer.getId());
- layerSql.append(layerHead);
-
- // Iterate over the queries and keep track of the number of queries
included in the final
- // sql
- var queries = layer.getQueries();
- var queryCount = 0;
- for (var query : queries) {
-
- // 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) {
- layerSql.append("UNION ALL ");
- }
-
- // Add the sql to the layer sql
- var querySql = query.getSql().trim()
- .replaceAll("\\s+", " ")
- .replace(";", "")
- .replace("?", "??")
- .replace("$zoom", String.valueOf(zoom));
-
- // Append a new condition or a where clause
- if (querySql.toLowerCase().contains("where")) {
- querySql += " AND ";
- } else {
- querySql += " WHERE ";
- }
-
- // Append the condition to the query sql
- querySql +=
- "geom IS NOT NULL AND geom && ST_TileEnvelope(?, ?, ?, margin =>
(64.0/4096))";
-
- var querySqlWithParams = String.format(
- """
- SELECT
- mvtData.id AS id,
- mvtData.tags - 'id' AS tags,
- ST_AsMVTGeom(mvtData.geom, ST_TileEnvelope(?, ?, ?)) AS
geom
- FROM (%s) as mvtData
- """,
- querySql);
- layerSql.append(querySqlWithParams);
-
- // Increase the parameter count (e.g. ?) and sql count
- paramCount += 6;
- queryCount++;
- }
- }
-
- // Add the tail of the layer sql
- var layerQueryTail = ") AS mvtGeom)";
- layerSql.append(layerQueryTail);
-
- // Only include the layer sql if queries were included for this layer
- if (queryCount > 0) {
-
- // Add the concatenation between layer queries
- if (layerCount > 0) {
- tileSql.append(" || ");
- }
+ private String prepareNewQuery(final String sql) {
+ return String.format(
+ """
+ SELECT
+ mvtData.id AS id,
+ mvtData.tags - 'id' AS tags,
+ ST_AsMVTGeom(mvtData.geom, ST_TileEnvelope(?, ?, ?)) AS geom
+ FROM (%s) AS mvtData
+ WHERE mvtData.geom IS NOT NULL
+ AND mvtData.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))
+ """,
+ sql);
+ }
- // Add the layer sql to the mvt sql
- tileSql.append(layerSql);
+ /**
+ * Prepare the sql query for the legacy versions of postgresql (< 16).
+ * <p>
+ * Older versions of the postgresql database do not optimize subqueries.
Therefore, the conditions
+ * are appended to the sql query, which is less robust and error-prone.
+ *
+ * @param sql the sql query
+ * @return the prepared query
+ */
+ @SuppressWarnings("squid:S3776")
+ private String prepareLegacyQuery(final String sql) {
+ String query = sql;
- // Increase the layer count
- layerCount++;
- }
+ // Append a new condition or a where clause
+ if (sql.toLowerCase().contains("where")) {
+ query += " AND ";
+ } else {
+ query += " WHERE ";
}
- // Add the tail of the tile sql
- var tileQueryTail = " AS mvtTile";
- tileSql.append(tileQueryTail);
-
- // Format the sql query
- var sql = tileSql.toString().replaceAll("\\s+", " ");
-
- return new Query(sql, paramCount);
+ // Append the condition to the query sql
+ query +=
+ "geom IS NOT NULL AND geom && ST_TileEnvelope(?, ?, ?, margin =>
(64.0/4096))";
+
+ return String.format(
+ """
+ SELECT
+ mvtData.id AS id,
+ mvtData.tags - 'id' AS tags,
+ ST_AsMVTGeom(mvtData.geom, ST_TileEnvelope(?, ?, ?)) AS geom
+ FROM (%s) as mvtData
+ """,
+ query);
}
/**
