2020-11-16 Shield tables bug found, at last

I finally found the source of the duplicated data in the OSM shield tables. It turns out that since the 'shieldway' table is a per-way table, it's cleaned out only if a way is deleted or replaced, and not if the way is just updated.

I wrote it up in detail in an issue at the osm2pgsql project. so I'm not going to repeat a lot of text here. With various work-arounds, I'm to the point where I can handle:

• inserts, updates, and deletes of ways
• inserts and updates of route relations

Deleting route relations causes a mess. Everything is deleted cleanly by referential integrity constraints ON DELETE CASCADE, but then the Lua script goes and reinserts the relation memberships back into the shieldway table, violating its foreign key constraint since the relation is no longer there.

I can't see a workaround for that bit, so I opened the issue above as a cry for help.

Fortunately, it's vanishingly rare to delete a route relation, so I think I can run for a while this way, and move on to trying for minutely updates. I think the next step is just that - work out how to switch osmosis to pulling from the main OSM database and trigger it minutely. I see there's a Wiki page on the subject, and I have the polygon files for the extracts I'm working with. How hard can it be? (Yes, I know, that phrase is right up there with “hold my beer,” but I'm a crazy programmer.

Next up after that, I think, will be to see if I can switch my personal map server over to auto-generated and cached tiles. That'll probably involve retooling the server to run off Apache or Nginx rather than althttpd, which fills me with dread, because I love the “zero administration” aspects of the latter - Richard Hipp is a master of the “It Just Works” school of software design. But I really want to see if going to tiles generated on the fly would allow it to scale to a whole continent, rather than about a fourth of the Lower 48 of the US.

Read more...

2020-11-15 Trying to update shield tables

Now that I have managed to speed up the part of updating the shield tables so that it could possibly run once a minute, I'm trying to run tests first with daily updates from geofabrik.de. I'm not having all that much success yet.

The update is running without complaint, but I'm finding that if a route relation is modified in the update, I get two or three copies of the member ways in the database. That surely won't work!

It's not obvious to me what's going on. There are no errors from the Lua script that's loading the database, It's just generating redundant entries. The initial load doesn't do that, and it's the same code.

I've also removed the primary key from the 'shieldways' table for now, because with that left in place, the duplicate rows did indeed cause a crash.

Gotta look into this more tomorrow.

Read more...

2020-11-09 Lua module for highway shields committed

Today, I tidied up, tested and committed a Lua module for processing highway shields in osm2pgsql.

I've tried to keep the design as non-intrusive as possible to people's existing workflow, assuming they already use the flex backend. There are just four changes needed to the Lua style file.

In initialization, add the lines:

      local shieldtables = require("shieldtables")
      local shieldt = shieldtables.new(prefix)
      

where prefix is the prefix (e.g., planet_osm) being used in the database for the OSM tables.

In the osm2pgsql.process_way function, add the line:

      shieldt:process_way(object);
      

In the osm2pgsql.process_relation function, add the line:

      shieldt:process_way(object);
      

Create a osm2pgsql.select_relation_members function if you don't already have one. There's a call:

      shieldt:select_relation_members(object)
      

with the same API that returns the necessary selection. For all the style sheets that I was using, I was able just to use:

      function osm2pgsql.select_relation_members(object)
	     return shieldt:select_relation_members(object)
      end
      

And that's pretty much it, for how to use it. The database tables prefix_shieldroute and prefix_shieldway are managed entirely by those procedures.

I still need to test to make sure that incremental update works with the new setup. If it does, that's half the job of making minutely updates work, I think.

Read more...

2020-11-07 First whack at a stylesheet for osm2pgsql

I managed to do enough Lua coding today to make my first attempt at a stylesheet for the flex backend of osm2pgsql to create the tables that the renderer for pictorial highway shields in OpenStreetMap will need.

The code is still pretty nasty - I'll clean it up on Monday - but it's producing the tables. I tried it on an export for Connecticut (rather than attempting the whole map), and it's turning out tiles that look just as they did before.

True to form, I found a bug in osm2pgsql while I was working on this. Nothing I can't work around. (2020-11-09: The maintainers are amazing. It's fixed already.)
Read more...

2020-11-06 Learning Lua

I spent pretty much all of my hacking time today munching on Lua documentation and trying out random snippets of code. (Once you know a couple of dozen programming languages, you typically learn a new one by taking a few hours to read the reference documentation and try a few short programs, and you're good to go.)

This is mostly so that I'll know enough Lua to write a library for extending the style sheets of osm2pgsql to create the tables that the current incarnation of OSM shield rendering uses.

Read more...

2020-11-04 North American highway shields

As I mentioned earlier, I'm trying to resume work on the project of rendering North American highway banners on OpenStreetMap.

I had left off work on the project this spring, when I was starting the sprint toward retirement, trying to make sure that my work was handed off responsibly. I had also left with a tremendous sense of frustration.

The osm2pgsql tool, which I had been using (like most people who try to serve up map tiles) to populate a database for rendering, was not capable at the time of creating the tables I needed. I had written a detailed proposal of how what I needed might be accomplished. It was badly received; in fact, one of the core team of the project suggested that osm2pgsql was simply the wrong tool for the job.

I then withdrew the proposal, and began to contemplate alternative tools, but was quite discouraged at the time that it would take to retool. Given the pressures at work, I put the project on hold.

Fortunately, since then, the plan for alternative programs to populate the database has been overcome by events. Jochen Topf, unbeknownst to me, was working on a new flex back end for the program, which allows a significantly wider selection of database schemata than the original program did, and is, in fact, a superset of what I had proposed. While some post-processing will be required, it appears fit for purpose.

So, the next tasks will be (a) retool my existing database population to use that back end (testing against a small extract, most likely Connecticut or Rhode Island), and (b) expand that back end to produce the tables that I need to describe numbered highways. With any luck, I can make some progress on that stuff today.

Read more...