After finding that most docker images and manuals seem to use quite outdated
software versions and methods to import OSM data and render tiles from it, I
decided my own image:
[FacilMap/openstreetmap-tile-server](https://github.com/FacilMap/openstreetmap-tile-server).
One thing that I still need to improve is the expiration of tiles. I wasn’t
able to find any real-world examples using the new expire table feature (Gemini
even insisted that such a feature does not exist), so my work on this is
entirely based on the osm2pgsql user manual and my own thoughts.
So far I have defined a single expire table for my map style. I run the
following bash script to expire the tiles from this table:
```bash
EXPIRE_MAP=mymap
EXPIRE_TABLE=mymap_expire
EXPIRE_WAIT=60
EXPIRE_DELETE_FROM=13
while true; do
date="$(date -u +'%Y-%m-%dT%H:%M:%SZ')"
if psql -Aqtc "select zoom || '/' || x || '/' || y from
\"${EXPIRE_TABLE}\" where last < '${date}';" | render_expired -m
"${EXPIRE_MAP}" -c /etc/renderd.conf -d "${EXPIRE_DELETE_FROM}"; then
if ! psql -Aqtc "delete from \"${EXPIRE_TABLE}\" where last <
'${date}';"; then
echo "Cleaning up expiration table failed." >&2
fi
else
echo "Expiring tiles failed." >&2
fi
sleep "$EXPIRE_WAIT"
done
```
So I generate a list of tiles whose `last` time is at least one second before
the current time (because more tiles might become expired in the current second
after reading out the list). Then I expire this list of tiles (which can take
several minutes depending on the configuration). If expiring the tiles
succeeds, I delete all rows from the expire table whose `last` time is still at
least one second before the time when I queried the table the first time. Any
tiles that were expired (again) while the tile expiration was running would
have a later `last` date now, so this way they will be handled (again) during
the next iteration.
Firstly, I would like some feedback on my script. Is this how expiration is
commonly done? One thing that I know I could still improve there is calling the
expiration each time right after `osm2pgsql-replication` instead of in a
separate infinite loop.
-----
Now to my actual question. I have one map where I render a large variety of way
types at different zoom levels. On the one end of the spectrum, all motorways
globally are rendered starting at zoom level 6. On the other end of the
spectrum, most footways globally are rendered starting at zoom level 14. This
is equivalent to the OSM Carto style, but I only render a small subset of it,
which mostly consists of motorways, footpaths and some other roads.
Since rendering tiles takes quite long for this map on my server, especially at
low zoom levels, I prerendered all tiles up to zoom level 9 and configured the
expiration script to rerender expired tiles up to zoom level 9 as well and
delete expired tiles for zoom levels above. To my surprise, this caused my
server to basically be busy rerendering expired tiles all the time, causing
significant CPU and memory/swap usage on my server. At first I was confused,
since at zoom level 9 I only render motorways and a few major roads and I
didn’t expect those to change so often, but now I think I understand what’s
going on. Since I have only a single expire table that does not distinguish
between zoom levels, a simple change to a footpath anywhere causes tiles
containing it to be expired, even if those tiles are at a zoom level so low
that they don’t actually contain that footpath. And I imagine small things like
footpaths change in many places all the time, which is why so many tiles at
zoom levels <= 9 keep getting expired that my server can barely keep up with
it. So what I need is a more sophisticated mechanism for tile expiration where
the minimum zoom level of a feature is considered when determining what tiles
to expire.
I found out that I can configure a
[`minzoom`](https://osm2pgsql.org/doc/manual.html#defining-and-using-expire-outputs)
option for an expire table. Because I can only define it for the whole table,
it means that I basically need one expire table per minimum zoom level. As a
consequence, I also need one feature table per minimum zoom level that is
connected to the expire table. Right now I have a single table for ways with a
`highway` column to determine the way type. Now I will need separate
`ways_minzoom6`, `ways_minzoom8`, `ways_minzoom9`, … tables with corresponding
expire tables where each way type is put into the table corresponding to the
minimum zoom level where it is rendered. I have several questions about this:
* Is there a technical reason why I cannot define the expire minzoom in the
`:insert()` call instead of defining a fixed minzoom for the expire table? That
would make my setup much easier, since I would be able to keep using a single
way table and a single expire table.
* Might splitting up my ways into about 10 different tables have a negative
impact on the rendering of my tiles? I don’t know enough details about how
geospatial indexes work in PostGIS, but I could imagine that querying features
from 10 different tables to render a tile would be slower than querying them
from a single table?
* Are there examples of how to query and clear multiple expire tables in my
expiration script? I imagine I need to adapt my postgres query to query all
expire tables at the same time, but still return the tiles uniquely, since the
same tile might appear in multiple tables. After the expiration I need to clear
the tiles separately for each table, since I assume postgres does not allow
deleting from multiple tables in one query.
--
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2495
You are receiving this because you are subscribed to this thread.
Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/[email protected]>_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
