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-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.

Read more...

2020-11-14 Delhi trails

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

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!

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

Read more...

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!

Read more...

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!'

Read more...

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?

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!

Read more...

2020-11-11 Fixing the rivers

[OOPS - I made a typo in the date when I first posted this!]

I noticed that in my topo map, I was losing rivers. For instance, the entire east branch of the Neversink had disappeared, although several of the minor tributaries were still shown. I blew a lot of today tracking down the issue.

It turned out to be that I'd downloaded updates to NHD when I was rebuilding the database after upgrading to PostgreSQL release 12. It appears that USGS changed the file layout so that the NHDWaterbody and NHDFlowline tables now are in multiple shapefiles, all of which have to be loaded to have the full data set.

Layout of a zip file from NHD

Since the first shapefile still has the same name that the old one did, the program that does the loading had no trouble, but loaded only a third of the data for New York (and even less for some other states!), so I never got an indication when loading the files that there was any sort of trouble.

Downloading and importing all the ZIPs another time took most of the day. I could have worked on other things, but the database was pretty unusable while it was thrashing over the rivers and streams.

Anyway, now it's fixed.

Before (White's Run is missing):

After (White's Run is back):

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-08 Buck Mountain, Pilot Knob, NY

No progress on software development Sunday - I was out hiking. My daughter and I climbed Buck Mountain, on the east shore of Lake George, NY.

Buck is one of the Lake George 12. I know that I climbed it before, but have no idea when. Cathy, too, may have climbed it, too, on a backpacking trip from Camp Chingachgook. In any case, now we have both logged it properly with a date and photo evidence.

I'll do a proper trip report later. I've got a back catalog to get through.

Kevin on the summit of Buck Mountain

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-05 Highway shield graphics - minor updates

Not a lot of progress today. I sketched some more ideas for using the flex back end of osm2pgsql to process concurrent routs in my notebook, and I puttered about verifying that the picayune changes that I made to the shield graphics worked.

The main change there is that the highway shields are back in New Brunswick, Prince Edward Island, Saskatchewan and Alberta. (The Canadian mappers had made wholesale changes to the network tag to unify the processing across Canada, and the graphics generation needed to be updated to match.)

Map of Woodstock, New Brunswick showing rendering of highway shields. Green: primary; blue: secondary; black: tertiary; maple leaf: Trans-Canada Highway.

There are also a lot more numbered county roads in West Virginia.

Map of the area west of Womelsdorf, West Virginia, showing that state's distinctive numbering scheme for secondary and tertiary roads.

And suburban Suffolk County, New York has its numbered county highways now.

Map of the area south of MacArthur Airport, showing numbered county roads (blue pentagonal shields) rendered in a distinct style from state highways (white shields in New York's distinctive shape).

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...

2020-11-03 The Erie Canal in Niskayuna

I've been curious for a while now about the old right-of-way of the Erie Canal along the Mohawk River in Niskayuna, and I've finally managed to explore some of it.

Most of my curiosity came from the fact that when I joined the project, someone had already drawn on OpenStreetMap a trail that followed the canal right-of-way from Aqueduct Road near the traffic circle nearly to the Schenectady city line.

Within the maintained area of Aqueduct Park, the trail pretty much follows the canal tow path, and you can see a very wet area to the southe where the canal has been filled in. There's then a rather less manicured trail that follows a natural gas pipeline (and old trolley right-of-way) up to the Mohawk-Hudson Bike/Hike Trail (Empire State Trail, Erie Canalway).

West of there, the canal right-of-way was obviously impassable. There were, however, numerous unmarked trails that headed north and west from the gas pipeline. I'd only been in there once before, and turned back because the nettles, raspberries, and poison-ivy were just too dense for my liking. Now that we've had a hard frost, I wanted to explore. Part of the area is state land, and while the tax rolls show another part as privately owned, I saw no posters, so had few reservations about bashing off down the herd paths.

Because the days are getting short, and walking there and back again from home is 10 km already, I kept the walks in that area fairly short. What I found was that there's a loop around the beaver pond near the bike path, and another, longer loop consisting of a path in the ditch and another on the south towpath. The flooded section is relatively short, and looks either to be intermittent or the result of beaver activity. Crossing it would still involve wading, so I scrambled the bank of the ditch and found a short beaten path through open woods to the gas line at that end.

There is also a well-defined unmarked trail connecting the west side of the beaver pond down to the trail on the towpath and the one in the canal bed. A very short section of that is flooded, with a well-beaten workaround.

There may be a connection with the National Grid maintenance track under the power line at the west end of the section that I explored. There's more state land to the west, so it's possible that the network goes a little farther. (There's a puzzling area on aerials - it looks as if there's a large clearing with a service building and no road access. No idea what's going on there!)

Anyway, I uploaded a rough draft of the paths that I found and some of the soggy spots that they skirted. That was the mapping activity for this couple of days.

One remaining puzzle: The pond by the bike path is full year-round. The stream under the bridge bu the power line runs continuously. But in a dry season, there's no water flowing across the trails, and no bridges or culverts that I could see. Where does the water go?

Read more...

2020-11-02 Beginning a new chapter

Today was my first day of not reporting to work at my former employer. I'm now a full-time self-funded open-source developer (that is to say, a retiree). This post is possibly the first of a series chronicling my adventures in retirement.

Many friends and colleagues have asked me, “so, what are your plans?”

I can't say that I have firm plans for anything at this point. The year 2020 has been a striking example of, “do you want to make the Almighty laugh? Tell Him your plans." My immediate plan is to try to rest up and decompress a little bit.

Frankly, I've never in my life been so without structure. I've pretty much never had a vacation longer than a week or two without an externally-imposed schedule. Even as a child, I had my parents filling, and overfilling, my “free&rdquo time with “enrichment activities”. Mary Ann and I would up doing a lot of the same with our daughter - although trying to keep her on a looser leash than the one I had - which tended to fill our time as well as hers. “Where do I go from here?” is going to taks some soul-searching. Still, I can drop some random thoughts.

I'd really like to do more hiking. I'd been putting on weight the past few years with enforced inactivity. Having the doctors work repeatedly on my eyes and feet, with activity restrictions on the rehab, has been confining. I've been doing a lot more walking in town since COVID-19 hit - it's one of the few things I can do outside the house, and working up the distance again to where I'm regularly doing 10 km a day (without a pack, of course, or with a very light day pack.) Catherine and I have also made a few trips again into the Catskills, most of which I've yet to write about. (Slovenly of me!)

In fact, if COVID-19 is ever not a blocker for crossing county and state lines, resupplying, or sharing a campsite, I'd like to try another big hike. My thru-hike of the Northville-Placid Trail turned into a series of sections, because of illnesses and injuries. Unlike a traditional 'thru hiker,' I couldn't just rest a few days after a problem and pick it up again - I'd need to go back to work! I've kind of an itch to do the Long Path of New York - the idea of hiking from Manhattan to the Adirondacks just intrigues me. (Yes, I know, the trail is built only to Altamont. There's a description of the route beyond there: who needs a maintained trail? I'm an experienced bush-whacko.) Given the current state of the world, I'm pretty sure that isn't going to happen in 2021. 2022, maybe, if all goes well.

I also have several unfinished open-source projects.

The biggest one of these, by far, is the “quadcode” system: a machine-code compiler for the Tcl programming language. Tcl is a highly dynamic language whose core paradigm is string substitution. Extracting data type information and constraining side effects to the point that safe machine code can be generated is an insanely difficult problem. Donal Fellows and I have had some limited success, but making the beast into something that's serviceable, that's deployable, and that covers much more of the language is going to be a long slog.

The next phase here will likely involve some major refactoring, and will require some really intense brain work - something that's not compatible with my goal of getting some rest after my final sprint on the job. It'll likely be a little while before I start picking up the pieces - but watch this space!

Another big unfinished project is to make a rendering pipeline for OpenStreetMap that deals in a graceful way with North American highway numbering systems. Identifying named and numbered routes with pictorial symbols on the map is a big part of it. Many North American jurisdictions have three or four different numbering schemata overlaid, so the shape of the marker is important to a driver trying to follow a route. Moreover, unlike the way most of the rest of the world does it, routes are often concurrent - it's not uncommon for a road over a bridge to have three or four different highway numbers. There's one pathological example near Indianapolis that has eight! This sort of overlaying has caused rendering gaffes, and a better approach than the current one is needed.

Again, I've had some limited success making a map that handles numbered routes in more or less the way I like, but now comes the long slog to make the implementation scalable (e.g., dealing with minutely diffs on a planetary scale; managing the expiration of tiles so that the whole map need not be pre-rendered), fortify it to be robust against future change, make it fit more closely with the rendering tools used by other OpenStreetMap servers, and find a path to widespread deployment (rather than just serving to a few friends off my home machine).

I've also been neglecting my music. I might have time in retirement to take it up again, particularly if conditions in the world improve to where people can perform again. I've been doing just a little bit - including playing recorder for a televised church service this past weekend. (The offertory , starting at 31:14, has a solo by Yours Truly.)

What else? Well, I've got a long history of going off-script. I may find myself itching to contribute to open-source machine vision, or get back into making stuff (particularly electronics), or just try something entirely new.

Or the perfect consulting gig might just fall in my lap. You never know. I'll just have to see what happens. Wish me luck!

Read more...