Thanks for your answers. So the common practice seems to be to not have one
feature/expire table per minimum zoom level, but group the minimum zoom levels
together into (usually 3) groups and so have 3 feature tables and 3 expire
tables. While having multiple tables certainly adds a bit of complexity to the
Lua script and the CartoCSS project, this complexity is far outweighed by the
possibility to add the feature tables as separate layers in the CartoCSS
project and set the minimum zoom level for those layers as well. This way at
low zoom levels, Mapnik will need to query only the low zoom level tables. I
found this to cause a great performance improvement at low zoom levels, and on
the other hand I didn’t notice a performance penalty at high zoom levels where
Mapnik now has to query several tables at once.
In my case, it was sensible to use 4 tables for minzoom levels 0, 10, 13 and
16. I created these through 4 identical `osm2pgsql.define_way_table()` calls,
with their only difference being that each of them references a separate
expiration table. For the expiration tables, I made sure to specify the
`minzoom` property:
```lua
local cycling_restrictions_expire2 = osm2pgsql.define_expire_output({
minzoom = 10,
maxzoom = maxzoom,
table = 'cycling_restrictions_expire2'
})
```
In my `project.mml` file, I added the 4 feature tables as separate layers with
a `minzoom`. I found out that the `minzoom` property has to be under a
`properties` property. This was not clear from the documentation, and having
`minzoom` directly on the layer did not have an effect but also did not raise
any errors.
```yaml
-
id: "cycling_restrictions_lines2"
name: "cycling_restrictions_lines2"
srs: "+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0
+y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over"
geometry: "linestring"
properties:
minzoom: 10
Datasource:
...
```
In my `style.mss`, I did not have to change much. Where I previously had a
`#cycling_restrictions_lines` selector in which all the styles were grouped, I
simply changed that selector to `#cycling_restrictions_lines1,
#cycling_restrictions_lines2, #cycling_restrictions_lines3,
#cycling_restrictions_lines4`. This led to a quadrupling of the resulting
`mapnik.xml` file, since CartoCSS seems to resolve all nested rules and create
a separate entry in the XML file for them, but I’m not aware of any negative
consequences of that.
Now to the tile expiration. In my specific setup, it was beneficial to specify
one pattern for the table name and then look up the expiration tables
automatically, rather than having to specify the names of all the expiration
tables. I used the following code to look up the tables:
```bash
expire_table_pattern="cycling_restrictions_expire%"
mapfile -t expire_tables < <(psql -Aqtc "SELECT table_name FROM
information_schema.tables WHERE table_schema = 'public' AND table_name SIMILAR
TO '${expire_table_pattern}';")
```
`expire_tables` is now a bash array of the table names. I then used the
following code to actually expire the tiles:
```bash
EXPIRE_MAP=mymap
EXPIRE_WAIT=60
EXPIRE_DELETE_FROM=13
join() {
echo "$2${3+$(printf "$1%s" "${@:3}")}"
}
while true; do
date="$(date -u +'%Y-%m-%dT%H:%M:%SZ')"
mapfile -t queries < <(printf "SELECT zoom || '/' || x || '/' || y AS
tile_path FROM %s WHERE last < '${date}'\n" "${expire_tables[@]}")
if psql -Aqtc "$(join " UNION " "${queries[@]}")" | render_expired -m
"$EXPIRE_MAP" -c /etc/renderd.conf -d "$EXPIRE_DELETE_FROM"; then
for table in "${tables_ref[@]}"; do
if ! psql -Aqtc "delete from \"${table}\" where last <
'${date}';"; then
echo "Cleaning up expiration table ${table}
failed." >&2
fi
done
else
echo "Expiring tiles failed." >&2
fi
sleep "$EXPIRE_WAIT"
done
```
Like in my original code, I look up the expired tiles with a `last` date before
the current date, but now through the `UNION` call I select them from multiple
tables at once, and tiles that appear in multiple tables are only returned
once. After the successful expiration, I delete those tiles from each table.
--
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2495#discussioncomment-17622390
You are receiving this because you are subscribed to this thread.
Message ID:
<osm2pgsql-dev/osm2pgsql/repo-discussions/2495/comments/[email protected]>_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
