[Tile-serving] [openstreetmap/osm2pgsql] Add tilesets to flex output allowing more flexible expire configuration (PR #1912)

[Sarah Hoffmann]

@lonvia commented on this pull request.



> +                                  std::string const &schema,
+                                  std::string const &table)
+{
+    auto const qn = qualified_name(schema, table);
+
+    pg_conn_t connection{conninfo};
+
+    connection.exec("PREPARE insert_tiles(int4, int4, int4) AS"
+                    " INSERT INTO {} (zoom, x, y) VALUES ($1, $2, $3)",
+                    qn);
+
+    auto const count = for_each_tile(
+        tiles_at_maxzoom, minzoom, maxzoom, [&](tile_t const &tile) {
+            connection.exec_prepared("insert_tiles", tile.zoom(), tile.x(),
+                                     tile.y());
+        });

Is it worth looking into upserts here (`ON CONFLICT (zoom, x, y) DO NOTHING`) to avoid duplicates?

> +          .. " maxzoom=" .. ts:maxzoom()
+          .. " filename=" .. ts:filename()
+          .. " schema=" .. ts:schema()
+          .. " table=" .. ts:table()
+          .. " (" .. tostring(ts) .. ")")
+end
+print(")")
+
+local tables = {}
+
+tables.pois = osm2pgsql.define_node_table('pois', {
+    { column = 'tags', type = 'jsonb' },
+    -- Zero, one or more tilesets are referenced in an `expire` field in
+    -- the definition of any geometry column using the Web Mercator (3857)
+    -- projection.
+    { column = 'geom', type = 'point', not_null = true, expire = { { tileset = 'pois' } } },

Would a shortcut `expire = 'pois'` for the most frequent case make the code much more complicated? The double table here is quite a challenge for getting right on first try..

> +    } else {
+        throw std::runtime_error{
+            "Value of 'maxzoom' field must be between 1 and 20."};
+    }
+    lua_pop(lua_state, 1); // "maxzoom"
+
+    // optional "minzoom" field
+    value = luaX_get_table_optional_uint32(lua_state, "minzoom", -1,
+                                           "The 'minzoom' field in a tileset");
+    if (value >= 1 && value <= 20) {
+        new_tileset.set_minzoom(value);
+    } else if (value != 0) {
+        throw std::runtime_error{
+            "Value of 'minzoom' field must be between 1 and 20."};
+    }
+    lua_pop(lua_state, 1); // "minzoom"

No requirement that minzoom is smaller than maxzoom?

> @@ -1200,6 +1329,38 @@ output_flex_t::output_flex_t(std::shared_ptr<middle_query_t> const &mid,
             "No tables defined in Lua config. Nothing to do!"};
     }
 
+    // For backwards compatibility we add a "default" expire (with empty
+    // name) when the relevant command line options are used.
+    if (options.append && options.expire_tiles_zoom) {
+        auto &ts = m_tilesets->emplace_back("");
+        ts.set_filename(options.expire_tiles_filename);
+        ts.set_minzoom(options.expire_tiles_zoom_min);
+        ts.set_maxzoom(options.expire_tiles_zoom);
+
+        for (auto &table : *m_tables) {
+            if (table.has_geom_column() && table.geom_column().srid() == 3857) {
+                table.geom_column().add_expire({m_tilesets->size() - 1});

Should that also take into account `get_options()->expire_tiles_max_bbox`?

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/pull/1912#pullrequestreview-1297262359
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/pull/1912/review/1297262359 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230214/7b3edb89/attachment.htm>