Tuesday, November 17, 2020

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.


Monday, November 16, 2020

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.


2020-11-15 Delhi trails mapped

With uncharacteristic promptness, I was able to edit the GPS tracks that Cathy logged on her smartwatch yesterday and get the Delaware Academy trails into OpenStreetMap.

I was only moderately surprised that nobody had mapped them previously. I was able to see some of the trails, which mostly follow long-abandoned carriage roads, on aerial images, and traced them where I could. Otherwise, I copied-n-pasted segments of GPS tracks.

The results don't look too different from the official map, so I'm fairly satisfied.


2020-11-14 Delhi trails

Today, Cathy and I went hiking on the trails near Delaware Academy in Delhi (pronounced DELL-high), New York.

Kevin and Cathy

The original plan had been to hike Mount Sherrill, a trailless peak in the Catskills reported to be fairly moderate as Catskill bushwhacks go. (Which is to say, a hard-core hike! Catskill bushwhacks are never easy!)

I spend way too much time yesterday worrying and re-re-doing the trip plan; somehow this one had me anxious. Cathy reported that she was uneasy, too, and neither of us knew why!

But the morning went fine right up to when I started loading the car. I couldn't find my trekking poles - which I knew I had less than a week ago, and I don't think I'd had the car out since. I checked the back seat of the car. I checked under all the seats. I checked the garage. I checked the corner by the door where I sometimes lean them. I checked my backpack, and under my backpack. I checked the gear shelf. I checked all the places multiple times. Nothing doing!

With my knees, I can't hike without poles, and eventually, I said, “There's no help for it. They're gone. We'll stop by EMS on the way out and get another pair.” We headed out, with me still berating myself, and got to the outfitter, which of course wouldn't open until 10 AM. Add 1.5-2 hours to get down to the mountain, and that's way too late a start when daylight is as short as it is.

We went back to my house to make other plans. I suggested that we might want to start on the CMC Hiking Trails Challenge a patch program for a set of relatively easy trails. (Not to be confused with their 350-mile All Trails Hiking Challenge which is for mountain goats only!

We got back, printed a couple of copies of the map and set off for Delhi to do the first set of trails - the set near the Delaware Academy. The weather started to look considerably worse than forecast: we got some spatters of rain and even a few flurries of snow, and decided that we were glad we weren't going to be at significant elevation.

As Cathy put it, “This is Someone Up There telling us, ‘all right, you both ignored the bad feelings that I gave you about this trip? Fine, I'll send a clearer message!’ ”

The trip wasn't terribly difficult, but it was strenuous. The trails up the hills had only piddly 600-foot (200 m) elevation gains, but because we were trying to do them all, we were doing several of them out-and-back and probably had a couple of thousand feet of elevation (600 m) gained as we hiked 7.5 miles (12 km) to map about 5 miles (8 km) of trail.

The area has charming names for trails and viewpoints, taken from the children's book My Side of the Mountain by Jean Craighead George.

With the leaves off the trees, there were a couple of pretty views. The village of Delhi is charming from above!

Delhi, NY as seen from the Gribley Trail above Delaware Academy

More fun than Sherrill would have been trying to hurry and in marginal weather, so the change of plans was a good call!


2020-11-13 Trip planning and route graphics

I spent about half of today working up a trip plan for climbing Mount Sherrill in the Catskills. (I was mostly looking at following the route recommended by Catskill Mountaineer but in usual Kevin form, I was kind of obsessing about it, and combing the aerial images to spot where the ledges were and making GPS waypoints, that sort of thing.

I also made a little more progress toward managing highway shields. I managed to add a 'changed' flag in the database that is set when a route is brought in from OSM, and reset when the graphic for the shield becomes available. Having this flag means that the 'routeGraphics' program doesn't have to look at all the route relations every time for ones that don't have graphics files. It cuts the run time down from minutes to an eyeblink.

Onward to testing the beast, but first, a day off for hiking!


2020-11-12 Slow day

I spent a lot of today trying to catch up on some reading, vegetating in front of videos, and so on.

I was also trying to sketch out a design to handle minutely updates to the tables that manage highway shields, but kept falling into 'boil the ocean' mode - looking ahead to future problems and not making actual progress toward the immediate issue. I need to be more disciplined about 'do the simplest thing that could possibly work!'


Thursday, November 12, 2020

2020-11-10 Tcl / SQLite Zoom meeting

Today's screen time was entirely taken up with SQLite & Tcl 2020 It was great to see over the 'Net so many of my colleagues from that community, but I felt as if I missed the most important part of the in-person conference: the discussions in the hallway and hospitality suite. (Not to mention Donal Fellows's whisky!) Still, it's 2020, what can you do?

SQLite & Tcl 2020
I understand that the videos of the talks will be on YouTube eventually. I'll let others weigh in with their impressions of the talks; I'd largely been away from the community for paid work for a while, and was kind of drinking from the fire hose.

Detlef Groth's work on literate programming is probably at the top of my list of “presentations that I must investigate further.”

Dan Shearer's shirt was something else!