Openstreetmap-carto: Render ref from route relationship

[matthijsmelissen]

The following issue has been moved over from trac. It needs more discussion as it is not clear how this should be implemented.

Highway shields are only rendered when the ways themselves have a ref tag. Now, the different ways that form a road route are assembled into relations. For the sake of avoiding duplicate data the ref tags should be removed from the individual ways and applied to the relation only.

However, currently we only use ref tags on roads for rendering, and we ignore the ref tag on the relation.

Any idea how we could implement this?

[matthijsmelissen]

Actually it is not entirely clear to me what is the recommended tagging. Perhaps tagging ref's on routes is incorrect tagging?

See also https://trac.openstreetmap.org/ticket/2599.

[matthijsmelissen]

The same holds for name's on relations.

See also https://trac.openstreetmap.org/ticket/2599,

[dieterdreist]

Am 03/giu/2014 um 00:09 schrieb math1985 [email protected]:

Actually it is not entirely clear to me what is the recommended tagging. Perhaps tagging ref's on routes is incorrect tagging?

no, recommended are refs on routes, and eventually on road ways as well, but the latter just for mapnik ;-)

[skylerbunny]

I'd like to put in a vote to have this implemented ASAP.

OSM recommended tagging for a road route with a ref= is to use relations, e.g. http://www.openstreetmap.org/way/21548058 and NOT to put this information on individual ways.

Since Mapnik does not render road route relations at all, map editors don't consider a route 'done' until all the information is duplicated on individual ways, meaning it all has to be cleaned up later - and the longer this is unimplemented (and the more editors we get, especially say, in the US where OSM is growing), the worse this gets.

This is a case of users modifying OSM's database to work around a missing feature in a tool (here, support of road route relations).

A quick and dirty way of implementing this, presuming that relations can be read to render, would be to recursively get all road route relations on a way if the way is tagged with highway={motorway,trunk,primary,secondary,tertiary,unclassified, or residential}. De-dup any identical ref names and drop them - those could come from duplication on the way/relation/super-relations. Then, render them one per line.

So, for example...this example render would be three lines.
US 41 (appears in 'ref' on way, on road route relation, and super relation, de-duped)
CTH H (appears in 'ref' only on the way)
WI 77 (appears in 'ref' only on road route relation)

I realize this introduces the additional problem of 'making the render look pretty', but as it is, people are doing concurrency in other 'wrong' ways, like 'WI 71;WI 99' or 'WI 71/99' which usually don't fit in the shield _anyway_. We'd be trading one poor render for another, but encouraging the correct OSM editing behavior.

[pnorman]

OSM recommended tagging for a road route with a ref= is to use relations, e.g. http://www.openstreetmap.org/way/21548058 and NOT to put this information on individual ways.

No, recommended is to have both, which isn't ideal, but that's what's recommended.

[skylerbunny]

No, recommended is to have both, which isn't ideal, but that's what's recommended.

I suspect that if this were implemented in openstreetmap-carto, the OSM recommendation would be changed to reflect that. That is, to prefer route relations _over_ tagging ways when a route spans "significantly more than one" way.

I believe tagging both ways and relations is done because the most common renderers, (but most especially openstreetmap-carto) doesn't render the refs that appear on the route relations at all. If it did, the OSM project could revisit that best practice. Until 'ref' on relations is supported in rendering, telling the userbase 'Please put refs on route relations only' would appear to them to be "removing data from the rendered map" at http://www.openstreetmap.org/ .

[mvexel]

Copied my comment from https://github.com/gravitystorm/openstreetmap-carto/issues/508#issuecomment-47234853 as per @skylerbunny's request:

FWIW, we spent a ton of time augmenting route relations (Interstates and US Highways mainly) in the U.S. with appropriate network and ref tags, so that we wouldn't have to rely on the brittle ref-on-ways tagging convention for determining the road designation. This is by no means finished - many state level networks remain to be done - but we have some tools in place in the U.S. to help guide this work, like the relation pages http://184.73.220.107/relationpages/

[mvexel]

I would be hugely in favor of shield tagging based on route network / ref being rendered on osm.org - this would encourage what I consider proper tagging of network information and stop folks from duplicating this information on the ways, which is brittle and hard to maintain.

[mvexel]

Insight from the Telenav perspective as a large scale user of OSM data: we ingest both refs on ways and network / ref on route relations to determine road network designations, but we'd much rather just use the relations for getting that data, and we're seeing lots of inconsistencies in the ref information on the ways, both between way and parent relation info and within the ways.

[pnorman]

Given the current scarcity of route relations, we'll need to continue rendering ref tags.

(Highlighting of type=route route=road relations)

What I'm wondering is, how, while avoiding duplication.

The rendering tables don't have relation membership information, so I think we have to use postgis. Thoughts?

[skylerbunny]

Well, the deeper technical implementation aside, assuming the existence of a tag can be compared between a way and a relation, I'd suggest this:

• If a ref exists on a way and also on a route relation, the route relation's ref wins and is rendered, and the one on the way is ignored. (If multiple route relations exist on the way, then concurrency applies. For 596, this may mean 'don't render anything', but we have the case of dealing with route concurrency open in issue #508.)

For long term maintainability of routes, we need to actively _discourage_ 'ref on ways'. Doing this supports it merely for backwards compatibility but puts thrust behind using relations.

[pnorman]

assuming the existence of a tag can be compared between a way and a relation

This is a fairly big assumption without membership information.

If a ref exists on a way and also on a route relation, the route relation's ref wins and is rendered, and the one on the way is ignored.

This would fail in the region I showed. It would also fail on the other side of the tunnel. The only route relations for roads I saw were for the E road network.

[pnorman]

we have the case of dealing with route concurrency open in issue #508.)

508 is about pictorial signs, not concurrency

[mvexel]

On Thu, Jun 26, 2014 at 6:08 PM, Paul Norman [email protected]
wrote:

assuming the existence of a tag can be compared between a way and a
relation

This is a fairly big assumption without membership information.

If a ref exists on a way and also on a route relation, the route
relation's ref wins and is rendered, and the one on the way is ignored.

This would fail in the region I showed. It would also fail on the other
side of the tunnel. The only route relations for roads I saw were for the E
road network.

This is however how we chose to do it for Scout. Take the relation network
and ref tags, and fall back on ways where needed. Granted we have the
'luxury' of being US only. But you have to start somewhere, and if we're
ever going to get to a state where folks tag road networks in a scalable,
maintainable way, setting the right example in the main stylesheet is one
of the better ways to do it. I know - tagging for the renderer is not
supposed to be a guideline, but in a way that is what folks have been doing
so far.

Martijn van Exel
http://oegeo.wordpress.com/
http://openstreetmap.us/

[pnorman]

Could use some European feedback, as I didn't see any route relations for country motorway systems in the area I looked, just cross-Europe networks. Is that the general way it's tagged, or are there variations by country within Europe?

[1ec5]

It seems like many European mappers have been hesitant to have route refs duplicated on relations and their member ways. To be sure, route relations are being used, but the network and ref tags are much less common than in the U.S.

I think that’s because most countries in Europe distinguish route networks by alphabetic prefixes. There are secondary differences in signage; however, if an “M” is enough to get the point across, I would totally understand if a road map omits the symbol in to save space.

Meanwhile, in the U.S, we’ve tried for years to shoehorn route network names into the eight character allotment, but it’s hopeless when there’s a concurrency. To give but one example, Interstate 71 and Interstate 75 (highways of roughly equal importance to the region) share a major stretch of road in Kentucky. Alas, ref=I 71;I 75 is nine characters, so the shield won’t show no matter how far you zoom in. And that’s the best case: the rest of the abbreviations are two characters long.

[systemed]

Highway refs are tagged on ways in Great Britain. No way can have more than one ref (apart from the unsigned and generally unused E routes) - i.e. no "multiplexing" or "concurrency" - and each signposted ref is, in theory, unique within GB. Route relations would therefore be needless duplication, and significant extra work for the mapper.

If openstreetmap-carto gets support for road route relations for the US guys then that's great, but there is no intention (and nor should there be) to stop tagging refs on ways in GB and similar countries.

[pnorman]

I would totally understand if a road map omits the symbol

I think you're mixing up issues - this issue is about using relations instead of ref on ways, not symbols.

eight character allotment

There's no reason we can't handle wider refs that I see.

[sb12]

In Germany, there are routes for motorways and some primaries ("Bundesstraßen"). I haven't seen many relations for secondary and tertiary highways ("Kreis-"/"Landesstraßen"). Note that some road relations are only parent relations for the TMC-Relations (Example: http://www.openstreetmap.org/relation/2166961).

Usually there's only one ref per road, except of some rare cases on motorways (Example on the A 8/A 81 here: http://www.openstreetmap.org/#map=14/48.7536/9.0367)

[pnorman]

apart from the unsigned and generally unused E routes

This here is the big problem I'm seeing with implementing this. Taking a section of the M1 as an example, we have ref=M1 on the way and a relation with ref=E 13 on it, running concurrent. We can't not render the way refs.

One option is to slice up the way refs on ;, deduplicate with relation refs, and render the deduplicated refs. This would get a shield for both M1 and E 13 in the example above.

This would also reveal a particular class of mistake, where refs disagree between route relations and their ways. As an example, the Rodovia Washington Luiz has BR040 on some ways and BR-040 on the relation.

Does anyone have any other suggestions that will work in the M1 case I gave?

[1ec5]

refs on relations aren’t supposed to include prefixes like “BR” or “BR-”. That’s what network is for; ref should just be “040”. The real problem is that the relation’s network isn’t guaranteed to match the prefix in the way’s ref. (For example, Ohio state routes are ref=SR ### on the ways and network=US:OH ref=### on the relations.)

Perhaps the test could be whether the relation’s entire ref (e.g., “040”) can be found anywhere in the way’s ref. If so, just use the already formatted way ref value. We wouldn’t have to make assumptions about spaces, hyphens, or how to shorten the network value (none of which would be a problem with graphical shields), or sorting for that matter. In other words, the way would be used as scaffolding, as it were, for the relation data. It’d be a compromise between the American goal of relying on route relations and the European practice of tagging ways instead.

If the map adopts a deduplication algorithm, entry-level editors like iD would need to present a consolidated ref field using the same algorithm. Otherwise, an inexperienced mapper would be left wondering about phantom refs.

[pnorman]

refs on relations aren’t supposed to include prefixes like “BR” or “BR-”.

The documentation you linked doesn't have any examples of road route relations.

The examples I found in Germany, France, England, Brazil, Spain, Russia, Italy and Mexico all have ref tags that are not purely numeric and include prefixes.

[dieterdreist]

2014-06-27 2:08 GMT+02:00 Paul Norman [email protected]:

This would fail in the region I showed. It would also fail on the other
side of the tunnel. The only route relations for roads I saw were for the E
road network.

In my area there are lots of route-relations so I thought they were common,
looking at taginfo I saw these numbers:
route=road 91000
ref=* and highway=* 4.7M

not sure how many highway ways are in the average route=road relation, the
above relation is 51.6 highway+ref per route=road relation, which seems not
too bad (just a gut feeling I admit).

Regardless of those numbers I agree with Martijn that rendering refs for
route relations will encourage mappers to create those relations and will
remove the incentive for people to duplicate the info on the ways.

[systemed]

@pnorman The obvious solution for the M1/E13 issue is that the E13 relation in the UK shouldn't be tagged with ref=, but instead admin_ref= or some similar tag used for unsigned, administrative-only numbers.

[dieterdreist]

2014-06-27 12:22 GMT+02:00 Paul Norman [email protected]:

The examples I found in Germany
https://api.openstreetmap.org/relation/21250, France
https://api.openstreetmap.org/relation/1139312, England
https://api.openstreetmap.org/relation/107076, Brazil
https://www.openstreetmap.org/relation/62885, Spain
http://www.openstreetmap.org/relation/76946, Russia
http://www.openstreetmap.org/relation/2708563, Italy
http://www.openstreetmap.org/relation/10256 and Mexico
https://www.openstreetmap.org/relation/3232571 all have ref tags that
are not purely numeric and include prefixes.

In Germany there are not many overlapping highway-road-routes, but only as
long as you don't care for "U"(Umleitung= alternative roads in case of
traffic jam / major accidents), but they do exist for small parts (aside
from the E-Road network, which can also be expressed with an additional tag
"int_ref"). Adding a character (e.g. "B"undesstraße, "A"utobahn,
"L"andesstraße, "K"reisstraße, "U"mleitung) is mandatory to understand the
type of road (maintainer), they will always be used. In Italy the situation
is very similar (most common abbreviations are "A", "SS", "SR", "SP"). Then
there are scenic routes (like "deutsche Weinstraße", "deutsche
Alleenstraße") etc. which also are signposted, and which also do overlap
with the main classification (which is indicating the maintainer).

[pnorman]

@pnorman The obvious solution for the M1/E13 issue is that the E13 relation in the UK shouldn't be tagged with ref=, but instead admin_ref= or some similar tag used for unsigned, administrative-only numbers.

unsigned_ref. My concern is, are there roads with ref tags where they are in road route relations with a different ref _and_ the ref on the way would not normally have a route relation created.

[dieterdreist]

2014-06-27 12:52 GMT+02:00 Richard Fairhurst [email protected]:

@pnorman https://github.com/pnorman The obvious solution for the M1/E13
issue is that the E13 relation in the UK shouldn't be tagged with ref=, but
instead admin_ref= or some similar tag used for unsigned,
administrative-only numbers.

@richard: the common ref tag for "E" in continental Europe is "int_ref",
but they are signed e.g. in Germany, so the situation is slightly different
there. Still, using different tags in different countries for the same type
of network would seem strange.

[pnorman]

I looked at what we've got in the database for rendering route relations

• Route relations are present
• We know way vs. relation from osm_id<0.
• We don't have type, but we do have route, so can work on route=road.
• A query like select w.osm_id,w.name,w.ref from planet_osm_line w join planet_osm_line r on(st_within(w.way,r.way)) where r.osm_id=:rid and w.osm_id>0; seems to find ways that are a member of the relation
  
  
  
  • This fails for two ways that are identical where one is a member
  
  
• performance is going to be a bugger to solve

[matthijsmelissen]

No way can have more than one ref (apart from the unsigned and generally unused E routes) - i.e. no "multiplexing" or "concurrency" - and each signposted ref is, in theory, unique within GB.

That seems to be false: the A6/A591 overlap.

Concurrency is certainly not a US-only thing. There are overlapping sections in the Netherlands, for example.

More on concurrency on Wikipedia.

[Klumbumbus]

In Germany there are not many overlapping highway-road-routes

Routes of "Bundesstraßen" (primaries) do also often overlapp. Example way. These routes (B196 and B173) take the same route nearly to the whole city of Chemnitz.

[systemed]

No, Wikipedia is talking rot. The supposed "A6/A591" is simply the A591: see https://www.google.co.uk/maps/@54.274877,-2.765902,3a,75y,21.65h,92.6t/data=!3m4!1e1!3m2!1sz0ICKln6poNuszEasYtUAA!2e0 , https://www.google.co.uk/maps/@54.272723,-2.758914,3a,75y,320.77h,81.82t/data=!3m4!1e1!3m2!1sb1x1JcktbUIu3N4Df6mpIQ!2e0 , https://www.google.co.uk/maps/@54.308431,-2.758175,3a,75y,195.49h,81.52t/data=!3m4!1e1!3m2!1sPHUvOnku3Sl-B0Ag4W2a3w!2e0 , https://www.google.co.uk/maps/@54.309761,-2.763812,3a,75y,172.43h,83.94t/data=!3m4!1e1!3m2!1shVlzWFdOhBu1U2zzdgQ4Mw!2e0 , https://www.google.co.uk/maps/@54.298062,-2.759611,3a,75y,172.43h,75.13t/data=!3m4!1e1!3m2!1sdQ-PIcR2cPsvKzd4imCxJA!2e0 etc. The road is clearly signed A591 as would be expected, with A6 in brackets for the usual "leads to" signage. Similarly, at the https://www.google.co.uk/maps/@54.280274,-2.76669,3a,75y,181.98h,81.97t/data=!3m4!1e1!3m2!1s76O7FXUiRLuYbmTKKIEkyw!2e0 turn it's signed A590, with the A6 in brackets.

The fact that each road in the UK has no more than one number is extensively documented in SABRE etc.

[mvexel]

Concurrency is probably why the E-routes in the EU are done using
relations. I can think of no other than historical reasons why this tagging
convention would not propagate to other road networks.

@Klumbumbus - are route relations used in Germany for maintaining the
numbered road networks? Or is it mostly refs on ways?

I see the challenge @pnorman raised with inconsistent use of the network
tag. In the US, this is well defined, but I am curious if this just a lack
of documentation?

On Fri, Jun 27, 2014 at 7:49 AM, Klumbumbus [email protected]
wrote:

In Germany there are not many overlapping highway-road-routes

Routes of "Bundesstraßen" (primaries) do also often overlapp. Example way
http://www.openstreetmap.org/way/44026067. These routes (B196
http://www.openstreetmap.org/relation/3422875#map=12/50.8289/12.9670
and B173
http://www.openstreetmap.org/relation/2105830#map=12/50.8239/12.9652)
take the same route nearly to the whole city of Chemnitz.

—
Reply to this email directly or view it on GitHub
https://github.com/gravitystorm/openstreetmap-carto/issues/596#issuecomment-47346002
.

Martijn van Exel
http://oegeo.wordpress.com/
http://openstreetmap.us/

[Klumbumbus]

@Klumbumbus - are route relations used in Germany for maintaining the numbered road networks? Or is it mostly refs on ways?

As far as I know, the refs are always on the ways (because this gets rendered atm). Route relations for primaries exist "often", for secondaries and tertiaries "less often". For some routes there is only the TMC relation and no proper route relation.

If the rendering changes from ways to relations I don't see a big problem for germany, since we have a strong community, which will create the missing route relations then.

[mvexel]

I suspect that will happen in most places - although there will be a fair
amount of resistance from (route) relation haters... Which I understand
fully, but I think it's time to get over that. Some logic is just too hard
to capture adequately without them.

One hurdle may be editor support. I haven't used iD much, but I hear that
relation support there is not so great. Perhaps for the better, people
maintaining relations are more likely to be using power tools anyway.

On Fri, Jun 27, 2014 at 8:22 AM, Klumbumbus [email protected]
wrote:

@Klumbumbus https://github.com/Klumbumbus - are route relations used in
Germany for maintaining the numbered road networks? Or is it mostly refs on
ways?

As far as I know, the refs are always on the ways (because this gets
rendered atm). Route relations for primaries exist "often", for secondaries
and tertiaries "less often". For some routes there is only the TMC relation
and no proper route relation.

If the rendering changes from ways to relations I don't see a big problem
for germany, since we have a strong community, which will create the
missing route relations then.

—
Reply to this email directly or view it on GitHub
https://github.com/gravitystorm/openstreetmap-carto/issues/596#issuecomment-47350670
.

Martijn van Exel
http://oegeo.wordpress.com/
http://openstreetmap.us/

[skylerbunny]

One hurdle may be editor support. I haven't used iD much, but I hear that
relation support there is not so great

I'm one of those weird people who started with iD and kind of never branched out much into other tools. I can tell you that once you know how iD handles them, relations work fine and are reasonably maintainable in the tool. (Others may work BETTER in this regard, sure, but I can in fact modify and work with them in iD.) In fact, iD could probably be programmed to throw warnings in cases where a ref is on a way and a relation, causing a conflict.

[1ec5]

pnorman wrote:

The examples I found in Germany, France, England, Brazil, Spain, Russia, Italy and Mexico all have ref tags that are not purely numeric and include prefixes.

Yikes, I didn’t realize there was such a discrepancy between the standard in the U.S. versus elsewhere. The consensus on talk-us has been for numeric refs and fully qualified networks (see the Interstate and U.S. route relation listings), but then again we seem to rely on route relations a lot more than these countries. The only point of contention on talk-us has been where to put banners like “ALT” or “TRUCK” (ref vs. modifier vs. network).

[pnorman]

Yikes, I didn’t realize there was such a discrepancy between the standard in the U.S. versus elsewhere. The consensus on talk-us has been for numeric refs and fully qualified networks (see the Interstate and U.S. route relation listings), but then again we seem to rely on route relations a lot more than these countries.

This is one of the dangers of doing tagging for not purely local matters on a local list.

I do not see a way to convert what the wiki documents for the US to a rendered shield. There is no consistent standard for the network tag, so we couldn't use it without a lot of country-specific logic. network is fine for pictorial shields since you need country-specific shields, but the design seems to have ignored text shields

[pnorman]

There is no consistent standard for the network tag

I decided to look at the US practice of using a colon. Of the 3.3k distinct values for network _outside_ the US (not having network LIKE 'US%'), 234 (7%) have a colon. When counting relations, not distinct values, it's 24k/172k (14%).

[pnorman]

So, after having done some research and thinking about the SQL side, I'm inclined to leave this open to resolve the technical side even through it will take some time to get the US relation tagging consistent with the rest of the world.

Regardless of if we slice and combine or use refs on ways as a fallback, we need a way to do matching between ways and relations. Doing this isn't hard (for me, at least). Doing this in a performant manner is.

I did some checking. Route relations are only present in planet_osm_line, not planet_osm_roads, which immediately is a performance hit. A partial index WHERE route='road' AND ref IS NOT NULL AND osm_id<0 could help here.

Next, we have the choice of finding relations for ways or finding ways for the relations. The latter has rendering advantages (they come pre-merged) but has performance implications: route relations can be very long. We also have the issue that it makes it more difficult for GroupSymbolizer and concurrencies (not a current issue) because the concurrencies will change along the length of the relation linestring.

The above dictates the strategy, now to write some non-trivial SQL

[mvexel]

On Fri, Jun 27, 2014 at 2:23 PM, Paul Norman [email protected]
wrote:

I do not see a way to convert what the wiki documents for the US to a
rendered shield. There is no consistent standard for the network tag, so
we couldn't use it without a lot of country-specific logic.

If we're just out to duplicate the rendering styles we already have but
using data from the relations instead of the ways, then country-specific
logic should be avoided, obviously. If countries want nice country-specific
shield rendering, they can set up their own tile server, like Toby did for
the US (concurrency hell here:
http://openstreetmap.us/~toby/shields.html#14/39.7834/-86.0322)

Or at least that used to be the answer. Other map providers can do country
specific things, so why can't we?

Martijn van Exel
http://oegeo.wordpress.com/
http://openstreetmap.us/

[pnorman]

In the common case, we've got this (1d roads)

Any way's linestring is within any parent relation's linestring.

It, however, is not obvious that this is always the case. osm2pgsql splits long linestrings for performance reasons.

You can have a scenario where there is a break in the relation linestrings from distance splitting, but not a break in the way linestrings.

Relation linestrings in brown, way linestrings in green, breaks indicated with arrows. (I 5 in Washington.

On the other hand, limiting linestring length to 100 Mercator km does help with performance, particularly once you start manipulating the linstrings.

[1ec5]

pnorman wrote:

So, after having done some research and thinking about the SQL side, I'm inclined to leave this open to resolve the technical side even through it will take some time to get the US relation tagging consistent with the rest of the world.

On second thought, it’s entirely appropriate that the U.S. omits prefixes in relation refs while Europe and Brazil include them. Relation refs hold whatever’s on the signage. In the U.S., that would generally be a plain route number; in Europe, that would include a prefix. Either way, the goal is for renderers to avoid having to parse the relation ref for something to put in a pictoral shield. So I don’t think it’s necessary to change any tagging at all. (And we’ve had this discussion before on talk-us. The current usage is intentional, it avoids edit wars over hyphens, and it’ll make implementing #508 a bit easier.)

[matthijsmelissen]

Relation refs hold whatever’s on the signage. In the U.S., that would generally be a plain route number; in Europe, that would include a prefix.

It's unfortunately not that easy. There are also some European countries, such as Germany and Poland, where the shields don't contain the prefix.

[pnorman]

On second thought, it’s entirely appropriate that the U.S. omits prefixes in relation refs while Europe and Brazil include them.

Then the textual shields will say "405" instead of "I 405".

[skylerbunny]

It's unfortunately not that easy. There are also some European countries, such as Germany and Poland, where the shields don't contain the prefix.

The base assumption, however:

In the U.S., that would generally be a plain route number; in Europe, that would include a prefix.

...already assumes we're at least continent if not country aware of where the route relation in question is. The 'network' tag provides exactly this information, so we would know which to do. Or, if we don't want to program that logic into the renderer, we could always have the tag 'include_prefix' yes/no on a route relation, default assumed to be one or the other.

[1ec5]

pnorman wrote:

Then the textual shields will say "405" instead of "I 405".

Which would be unfortunate. But then we can add the three rules required for implementing #508 in the U.S., one for each shield-fill (white, black, and yellow), and it won’t matter anymore.

[pnorman]

We have to be able to render generic text shields, and #508 is more a follow up to this than the other way around.

[1ec5]

Yes, I’m in total agreement. I just meant this work will make #508 trivial to fix, hopefully in short order. :-)

[pnorman]

I took a first pass at rewriting the low-zoom query for refs. This is just the 20 minutes of query-writing collection of my thoughts so far

(
  SELECT 
    COALESCE(r.geom,h.way) AS way, COALESCE(r.ref,h.ref) AS ref -- if r.ref is not null then r.geom is also not null
      FROM (
        SELECT 
            way, 
            ref
          FROM planet_osm_roads
          WHERE highway in ('motorway','trunk','primary','secondary')
            AND way && !bbox! -- The only spatial check that limits the results
          ) AS h
        LEFT JOIN (
          SELECT 
              ST_Collect(way) AS geom, -- Is this the right way to do this? Do we need to ST_Dump first? All we can safely assume is that way is a linestring
              ref 
            FROM planet_osm_line 
            WHERE ref IS NOT NULL 
              AND h.way && way -- Is a ST_Intersects faster? Is this even necessary with the join condition?
              AND route = 'road' 
              AND osm_id < 0 -- relations
            GROUP BY osm_id
          ) AS r
          ON (ST_Within(h.way, r.geom))
      WHERE r.ref IS NOT NULL or h.ref IS NOT NULL
  ) AS roads_text_ref_low_zoom

Some thoughts in-line.

One obvious flaw with this is that it will return a linestring for the relation as many times as there are ways in that relation in the bbox. I could probably do a SELECT DISTINCT to eliminate this, but I won't know until I start performance profiling if it's better to do that or to let mapnik try to render the shields, but not do anything because they're exactly the same. It might cause issues if we start trying to re-position to avoid collisions.

Any thoughts?

[pnorman]

select ref,highway,length from
(
  SELECT 
    COALESCE(r.geom,h.way) AS way, COALESCE(r.ref,h.ref) AS ref, -- if r.ref is not null then r.geom is also not null
    h.highway, char_length(COALESCE(r.ref,h.ref)) as length
      FROM (
        SELECT 
            way, ref, highway
          FROM planet_osm_roads
          WHERE highway in ('motorway','trunk','primary','secondary')
            AND way && !bbox!
          ) AS h
        LEFT JOIN (
          SELECT 
              ST_Collect(way) AS geom, -- Is this the right way to do this? Do we need to ST_Dump first? All we can safely assume is that way is a linestring
              ref 
            FROM planet_osm_line 
            WHERE ref IS NOT NULL 
              AND route = 'road' 
              AND planet_osm_line.way && st_expand(!bbox!,100000)
              AND osm_id < 0 -- relations
            GROUP BY osm_id,ref
          ) AS r
          ON (ST_Within(h.way, r.geom))
      WHERE r.ref IS NOT NULL or h.ref IS NOT NULL
  ) AS roads_text_ref_low_zoom

This appears to work, but using st_expand(!bbox!,100000) only works with prior knowledge of the depths of osm2pgsql splitting, and is a decidedly sub-optimal way, particularly as zooms increase. I can't do planet_osm_line.way && h.way, so it looks like I need to completely restructure the query.

[pnorman]

Scratch the idea of using the linestrings from the relations. This falls down on its face for routes which switch highway types.

[pnorman]

This works.

(
  SELECT 
      w.way, string_agg(COALESCE(r.ref, w.ref),';') AS ref,
      w.highway, char_length(string_agg(COALESCE(r.ref, w.ref),';')) AS length
    FROM (
      SELECT 
          ctid, way, highway, ref
        FROM planet_osm_roads r
        WHERE r.highway in ('motorway','trunk','primary','secondary')
          AND r.way && !bbox!) AS w
      LEFT JOIN (
        SELECT 
            w.ctid AS wctid, ST_Collect(rels.way) AS geom, rels.ref as ref
          FROM (
            SELECT 
                ctid, way, highway, ref
              FROM planet_osm_roads r
              WHERE r.highway in ('motorway','trunk','primary','secondary')
                AND r.way && !bbox!) AS w
            JOIN (
              SELECT osm_id, way, ref
                FROM planet_osm_line l
                WHERE osm_id < 0
                  AND route = 'road'
                  AND ref IS NOT NULL) AS rels
              ON (ST_Intersects(w.way, rels.way)) -- Doing filtering here is cheaper than the left join
          GROUP BY ctid, rels.ref, rels.osm_id) AS r
        ON (w.ctid = r.wctid -- optimization because we did a ST_Intersects earlier
          AND ST_Within(w.way, r.geom))
    WHERE r.ref IS NOT NULL OR w.ref IS NOT NULL
    GROUP BY w.way, w.highway
    ORDER BY CASE highway WHEN 'motorway' THEN 0 WHEN 'trunk' THEN 1 WHEN 'primary' THEN 2 WHEN 'secondary' THEN 3 END
  ) AS roads_text_ref_low_zoom

low-zoom only, high zoom is pending on #701.

I need a better way of rendering #670

[pnorman]

@gravitystorm, I see a few options here, and think an executive decision needs to be taken about the direction. I am taking it as a given that we need to handle refs on ways not in route relations, both refs on ways and ways in route relations, and ways in route relations without refs.

1. Use SQL to match roads to relations with the rendering tables.

Pros: Can be done with a standard database and no changes. Moderately performant.

Cons: Complicated SQL. I might be able to simplify the above somewhat.

1. Use slim tables to match road ways to relations with the rendering tables.

Pros: Perhaps simpler SQL.

Cons: Unknown performance (GIN indexes can be slow), introduces a dependency on --slim tables, prevents use with the new multi backend in osm2pgsql

1. Use Lua transforms to move information from route relations to ways, either directly or by referencing IDs

Pros: Fastest. Simplest SQL.

Cons: Makes osm-carto dependent on a custom transform and not usable on a more generic database. Requires switching to lua transforms. Requires a database reload.

Personally I'm in favour of 1, with an eye to modifying the osm2pgsql linestring split code to eliminate the differing split point case which would make the above SQL substantially simpler. There are also some other cleanups I can do to make it a bit easier to read, but not much can be simplified out without going to sequential scans. Keep in mind that I was a PostgreSQL/PostGIS consultant, and my idea of a complex query is substantially different than other peoples.

[gravitystorm]

See also #711 !

I'm narrowly in favour of option 1, but with particular note on the "Con" that you mention. As I hinted elsewhere, my priorities are single references on ways, and single/multiple references from relations. If dropping multiple references from ways (i.e. ignoring ref=A;B) makes life any easier when writing the SQL, I can be persuaded.

[matthijsmelissen]

Assigned back to @pnorman.

[pnorman]

dropping multiple references from ways (i.e. ignoring ref=A;B) makes life any easier when writing the SQL, I can be persuaded.

Unfortunately, no. The query is going to end up basically being an inner SELECT which generates the refs array, and an outer query that turns them into a shield that works pre-Mapnik 3. Completely dropping refs from ways simplifies it, but as only refs on ways are a standard way of tagging, that's obviously not an option.

[pnorman]

See also #711 !

It's worth noting that #750 makes it worse, as you need the text E'\n' in the JSON, which becomes E'\\n' in the text representation, which means my normal way of converting between serialized JSON and text (search and replace \n) breaks down here.

[pnorman]

Now that #711 is resolved, I can look at this in a sane way.
Unfortunately, #750 wraps another SELECT around the outside, and I also found some bugs I wasn't handling correctly.

(SELECT 
    way, 
    highway, 
    height, 
    width, 
    refs 
  FROM 
    (SELECT 
        way, highway, 
        array_length(refs,1) AS height, 
        (SELECT MAX(char_length(ref)) FROM unnest(refs) AS u(ref)) AS width, 
        array_to_string(refs,E'\n') AS refs
      FROM (
        WITH w AS (SELECT -- This fairly simple CTE gets the roads in the bbox. Mapnik calls it with a world-wide bbox, but a limit 0, and fortunately the CTE won't be executed then
            ctid, 
            way, 
            highway,
            string_to_array(ref,';') AS refs
          FROM planet_osm_roads
          WHERE highway IN ('motorway','trunk','primary','secondary')
            AND way && !bbox!)
        SELECT w.way, highway, COALESCE(array_agg(r.ref), w.refs) AS refs
          FROM w
            LEFT JOIN
            (SELECT -- This gets all the road relations with ref tags for a given way
                w.ctid, 
                r.ref, 
                ST_Collect(r.way) AS geom -- Because osm2pgsql can split a route relation differently than its ways, we need to reform the unsplit route relation
              FROM w 
                JOIN (SELECT
                    osm_id, way, ref
                  FROM planet_osm_line l
                  WHERE osm_id < 0
                    AND route = 'road'
                    AND ref IS NOT NULL) AS r
                  ON (ST_Intersects(w.way, r.way)) -- performance optimization to speed up the left join
              GROUP BY w.ctid, r.osm_id, r.ref -- to reform the relation. we need r.ref so we can return it in the select
            ) AS r 
            ON (w.ctid = r.ctid -- No primary keys, so we re-match with ctid
              AND ST_Within(w.way, r.geom)) -- true iff the way is part of the relation, or lies exactly along it as a duplicate way)
          WHERE w.refs IS NOT NULL OR r.ref IS NOT NULL
          GROUP BY w.way, highway, w.refs -- To aggregate up all the road relations for a way
      ) AS merged_refs
    ) AS q
  WHERE height <= 4 AND width <= 11
  ORDER BY 
    CASE highway 
      WHEN 'motorway' THEN 0 
      WHEN 'trunk' THEN 1 
      WHEN 'primary' THEN 2 
      WHEN 'secondary' THEN 3 
    END
) AS roads_text_ref_low_zoom

Throwing together a demo to show

[pnorman]

Stuck with some performance issues, but I have a sample

[pnorman]

I'm contemplating closing this. As above, there are currently serious data issues in the US that prevent route relations from being used for rendering shields. If someone wants to take up the data quality issue, I could leave it open or re-open in the future, but unless it changes, I just can't see any way to make use of the route relations.

[mvexel]

Not sure that I understand the data issues, happy to talk about it and see what I can do on the U.S. side perhaps with help from TNAV editors or MapRoulette. Let's talk after SOTM.

[1ec5]

@pnorman Are you referring to the double “513” in your sample above? Looking at the SR 513 relation, I don’t see anything wrong with the tagging being used here. Or are you referring how in the U.S. we don’t put alphabetic prefixes in relation refs?

[pnorman]

I'm referring to that the road refs are 5, 405, etc; not what people would expect.

[pnorman]

Unassigning because I have no plans of working on this in the near or medium term, and I don't want others to not work on it because they think I am.

I will caution for anyone planning on picking this up that this has been a fairly long-running issue, reading it is somewhat difficult because

• a large number of people suggesting "easy" mechanisms which are not technically possible, so you need to know what will work
• sections are off-topic, talking about pictorial shields
• people assume that their local tagging practices apply world-wide

If you want to take a stab at implementing it, the requirements are

• it works without slim tables
• route relations being split by osm2pgsql differently than ways is handled
• the tagging practice of ref information on ways and the practice of ref information on route relations are both handled
  
  
  
  • ref information simultaneously on ways and relations is an open question
  
  
• When writing the query, performance and readability need to be considered

[SomeoneElseOSM]

(just spotted this while looking at https://github.com/gravitystorm/openstreetmap-carto/issues/508#issuecomment-328315877 )

The comments above (admittedly some of which date from 2014) seem to assume an entirely SQL-based approach. An alternative and frankly much less horrible option would be process the route relations in lua first (as per http://www.openstreetmap.org/user/SomeoneElse/diary/38988 ). This could control as much or as little in the map display as needed (perhaps only shields).

[pnorman]

The comments above (admittedly some of which date from 2014) seem to assume an entirely SQL-based approach. An alternative and frankly much less horrible option would be process the route relations in lua first (as per http://www.openstreetmap.org/user/SomeoneElse/diary/38988 ). This could control as much or as little in the map display as needed (perhaps only shields).

The problem is because we need to handle shields from either relations or the ways, and we need to colour shields based on way information, not relation information we need to deal with some complexities that don't apply to what you're doing.

[skylerbunny]

The problem is because we need to handle shields from either relations or the ways

I think if it's additive per way, this could work.

• If at least one relation on a way exists with a route type and network/ref/modifier keys, render one route tag each based on a relation.

• Any (unique) refs that exist on the way after that could be appended to the end?

[skylerbunny]

...ugh. Well, I see that this is only dealing with relations in a list, not shields at all.

Apologies.

I'll modify my comments appropriately. But in those cases I still think it would be best to let relations supercede way tagging if it exists on a way, only adding way tags if they're different, at the end. The principle is the same.

[pnorman]

I'll modify my comments appropriately. But in those cases I still think it would be best to let relations supercede way tagging if it exists on a way, only adding way tags if they're different, at the end. The principle is the same.

This doesn't change the problems of complexity - it's coloring the shields based on the highway=* tag, not dealing with ref information on both.

[1ec5]

If you want to take a stab at implementing it, the requirements are

• it works without slim tables
• route relations being split by osm2pgsql differently than ways is handled
• the tagging practice of ref information on ways and the practice of ref information on route relations are both handled
  
  
  
  • ref information simultaneously on ways and relations is an open question
  
  
• When writing the query, performance and readability need to be considered

@kennykb, to what extent does the new osm-shields project satisfy these requirements?

[imagico]

From a quick look without actually testing - relies on slim tables and is incompatible with minutely updates (which isn't even on the list from @pnorman since it is an obvious requirement).

Solving https://github.com/openstreetmap/osm2pgsql/issues/230 seem by far the best approach to this.

[SomeoneElseOSM]

Solving https://github.com/openstreetmap/osm2pgsql/issues/230 seem by far the best approach to this.

The key bit that needs an osm2pgsql change is if you want to render route refs differently (e.g. in a different colour) depending on the type of highway way it's being displayed on. If a US-centric map was happy to use the same style of ref representation ("shield" in US terms I guess) regardless of highway type then I don't believe you'd need an osm2pgsql change. As an example, the walking and cycling routes at https://map.atownsend.org.uk/maps/map/map.html#zoom=14&lat=54.12077&lon=-0.96084 are done by directly rendering from the relation.

[imagico]

Yes, but if you want to arrange multiple shields in a certain way it is still fairly awkward to do so based on the current _individual ways plus separate route geometries_ structure generated by osm2pgsql. Hence my general remark regarding the best approach.

[kennykb]

Please note that I did my osm-shields for my own use - the proposal to adopt it for the main rendering chain is not mine! I shared it primarily because I could not find a worked example of how to use the GroupSymbolizer to render marker clusters for concurrent routes in multiple networks, and encountered enough difficulty trying that a project to share the results seemed to be in order.

With that said, don't be quick to discount the possibility that this approach could be reworked in such a way as to scale to the main map.

minutely updates

The current implementation works entirely without either parsing the OSM changeset or modifying osm2pgsql. It's actually pretty well suited to the 'daily update from GeoFabrik' workflow. Minutely updates are a different workflow. 'Incompatible with minutely updates,' as @imagico puts it, is _far_ too strong a statement; 'minutely updates need a little more coding, ideally with some help from osm2pgsql' is more accurate. To me, it's quite obvious how to handle them, and I give three possible integration strategies on the project wiki. The integration alteratives depend more on social issues than technical ones; if osm2pgsql cannot be modified to maintain the auxiliary tables, then fall back on reparsing the changes delivered by osmosis, or use database triggers to keep them in sync.

I haven't troubled to develop the minutely workflow, because I want to reassure myself that it won't be a complete waste of time. Given that I'm not all that well connected in the community, I strongly suspect that I may not be able to negotiate the social and political hurdles of developing to the 'big' rendering systems, or that the approach will fail to scale in some way that I have not anticipated. I haven't the resources personally to host a full-planet, minutely-updating system, so testing would be a problem. The technical problem is easy compared with getting access to the facilities needed to test a solution, and getting such a solution integrated into the standard tools. The latter issues are what worry me.

With regards to the other remarks from @pnorman, relayed by @1ec5:

slim tables

@imagico statement that the implementation 'relies on slim tables' is also rather too strong a statement. It relies on the slim tables to perform its updates - _just as osm2pgsql does._ The slim tables are not used at all at render time.

What is used at render time is three new tables. One has a row for each route=road relation, and maintains the 'network' and 'ref' tags. A second has a row for each way that is a member of one or more route=road relations, and holds simply the relation ID, the way ID, and the role. The third is a directory that maps network/ref pairs to the file names for the corresponding graphics.

Having something in the database to identify where to find the graphic elements is essential. Alas, US road networks are anything but a 'small fixed set' and so need a database to track them. The other two tables could, I suppose, be replaced with a modification to osm2pgsql that would copy the network and ref information down to the individual items in the line table. This would, I suspect, prove less performant (because of replicated data) than the JOIN operation needed to go from 'I am rendering this set of ways' to 'this is the set of route markers that I need to place'. Given that a schema change to support the graphic elements would be required anyway, I decided to go with the JOIN.

Having the tables available for joining allows for queries to go in both directions: 'what is the set of ways that I will encounter if I follow County Road 34?' and 'what is the set of route markers that I may expect on way 2718281828?'

In order to have the smallest possible footprint, I confine myself to relations with 'type=route', 'route=road', and 'network' and 'ref' both present. Obviously,

approach to splitting linestrings

The osm2pgsql approach of reassembling route relations back into long linestrings is fundamentally not going to work with route concurrencies, and in fact I totally ignore it and work with the original ways plus the auxiliary tables. The problem with the approach is that the linestrings need to represent distinct _sets_ of relations, not individual relations. In the diagram below, the red and blue lines represent distinct routes. In the section where the routes are concurrent, there's really no practicable way for a renderer to tell that if all it has is the separate red and blue lines. It really needs five linestrings (or three multilinestrings) in this case: the bits that belong only to the red route (two linestrings or one multilinestring), the bits that belong only to the blue route (again, two linestrings or one multilinestring), and the segment that is shared by both (one linestring).

The implementation as coded defers splitting and merging linestrings to render time, when there will always be a set of manageable size (because it consists only of the ways that will be rendered on one supertile). It uses the powerful PostGIS 'ST_LineMerge' operation to group ways that belong to the same _sets_ of routes together and coalesce them where possible. About the only thing that it runs afoul of is that Mapnik winds up trying to place a marker on each carriageway of a motorway, even at low zoom levels, leading to a proliferation of markers. This appears to be a deviation from what the Mapnik documentation promises, and I've entered a query at mapnik/mapnik/issues/3978 about how best to solve the problem.

handling both the tagging practice of putting the ref on the way and putting it in the relation

Since I was focusing on a North American map, and the practice of placing route references on relations has become near-universal here, I simply didn't bother coding up the alternative. As long as you don't need to have both styles of tagging supported _on the same way_, it's easy to implement. Existing tagging practice in the US will mean that the ways with the tag on the way will not get shaped markers, but rather use something like today's OSM style. It's too long a leap to expect the renderer to take a ref that looks like 'CR 34', figure out from administrative boundaries what county the 'CR' refers to, and look up the appropriate marker.

performance and readability

The performance appears adequate. On the one largish (about a third of the United States) map that I render, the rendering remains I/O bound - it's dominated by the fact that on my machine, owing to fairly limited SSD space, rendering a supertile takes at least two seeks on magnetic disks, one to retrieve a hillshading image and another to retrieve raster-encoded land cover data.

It is, of course, impossible to offer assurances regarding performance in the fielded environment without testing in a very similar setup to the production system. I do believe that what performance problems we will encounter are surmountable.

As far as readability goes, given that the assembly of route concurrences is done in the database, we are dealing with some fairly complex SQL here. I can, of course, comment it much more extensively, but there is complexity there that simply cannot be eliminated while continuing to satisfy all the other constraints. From Mapnik's point of view, the complexity is reduced to a simple call on a stored procedure. Within the stored procedure, this wiki page provides an overview of what is happening. Of course, I plan to put that discussion in abbreviated form in the commentary in the stored procedures themselves.

[imagico]

I think you misunderstood my statements - i did not mean to pass judgement on the suitability of your approach for what you are trying to accomplish (for that i have not looked even remotely close enough at your code). I just tried to answer the question by @1ec5 regarding the suitability of your code for this style based on the requirements previously stated in this issue.

And i disagree that _relies on slim tables and is incompatible with minutely updates_ is too strong, it is an accurate description of the current situation as far as i can see. If your code can be modified to allow minutely updates in a way that works reasonably well on a world wide database i don't know.

[kennykb]

@imagico: Is there an approach to minutely updates in which osm2pgsql does not rely on slim tables? Can you give me a link to a discussion of such a thing? I see on the OSM wiki:

Some important features (including incremental updates (planet diffs, the initial load to populate the track tables, and proper evaluation of multipolygons) only work in slim mode.

There's very likely something of which I'm unaware here. Otherwise, it seems rather odd that osm2pgsql may depend on slim tables to perform incremental updates and apply planet diffs to 'roads', 'line', 'point' and 'polygon', but I may not depend on them for incremental update of the added tables that support route concurrences. That's why I was careful to clarify that I do _not_ depend on the slim tables at render time, only to perform updates.

[imagico]

Note i have made no connection between the requirement to work without slim tables (as stated by @pnorman) and the requirement to allow minutely updates (which implicitly stems from the use on the OSMF servers).

The requirement to work without slim tables is based on two main motivations

• the ability to use the style on platforms where osm2pgsql is used without them.
• to not depend on osm2pgsql not changing the structure of the slim tables. These tables are for internal use by osm2pgsql only and can therefore change any time - both in their structure and in the way they are used - which could break anything outside osm2pgsql using them.

Neither of these has anything to do with the question if the slim tables are used during rendering or not.

[kennykb]

I'm not going to comment further here. I think it's doable - but not done. I don't see any architectural issues blocking it.

I'm sure that I won't have time to do it quickly - anyone who's sufficiciently familiar with the code for osm2pgsql or Mapnik, or that is familiar with tile expiry mechanisms, I'd like to pick your brains about how best to integrate changes.

I've opened a project to track this.

[kennykb]

I suppose I should make a progress report. The code in osm-shields now handles ref=* tags on either a route relation or directly on a way. (The ones on ways are intentionally not processed if the way participates in a group relation.)

See the corner of New Hampshire, Vermont and Massachusetts for a transition from "refs mostly on route relations rendered with pictorial shields" to "refs mostly on the way itself, rendered in classic OSM style"

Concurrencies among route relations are handled with the GroupSymbolizer.
Multiple ref=* values on a single way are handled by stacking them in the box as seen near Kingston, NH.

Let this serve as a proof of concept that having both representations in the same map isn't a show-stopper.

[skylerbunny]

If it helps to do some spot checking, I did some fixes in the last couple hours to the state of Georgia, at US:US, US:GA, and their sublevels like counties and modifiers, to try to make them consistent; that is, modifiers exist where applicable, and networks reflect 'a county or the same modifier that appears in the modifier tag' at the third network level. Some of them were out of sync with this pattern, had GA or US or a modifier in the ref, and so forth.

Georgia seems to be pretty mature in terms of 'most routes at the state and US level have relations, and counties have a few too.' I wouldn't claim I found every error in the above, but I do think I got a lot of them. I found a couple dozen.

[pnorman]

If you want to take a stab at implementing it, the requirements are

In addition to the requirements I listed, there are the global ones for the stylesheet

• That it is written in CartoCSS
• That it doesn't use views, extra tables, or anything which is not created by the current install instructions
• That it meets our code style guidelines

To solve this in osm2pgsql, implement openstreetmap/osm2pgsql#230. This is a big job because it in turn requires changes to osm2pgsql's Lua API.

---

It is possible to implement this meeting all of the criteria except SQL readability and performance. I did so in 2014 but the SQL was too messy for me.

If I were to tackle it today I'd start by cleaning up the roads SQL. If that were cleaner, it'd be easier to work with, and new complexity would be more tolerable. I'm not sure there's a direct connection on the SQL side, but cutting back the roads MSS would certainly help.

Then comes the tough question of how to implement. The requirements of working with osm2pgsql as we call it (no slim tables) and understandable SQL are hard requirements. I'd see if one of the requirements about features needed could be weakened.

If none of them can, then I don't believe it's possible to solve, given the constraints.

_Edit: Fixed issue link_

[kennykb]

Given the constraint that you express that 'the schema as implemented with a minimalistic osm2pgsql import today cannot be expanded in any way in the future', I think you are right that the problem cannot be solved.

If you are indeed that adamant, I would also assume that any change to osm2pgsql to create auxiliary tables to track membership in route relations would also be summarily rejected. (I further assume that this would be the case even if enabled only on request by a command-line option.)

The schema limitations are the one constraint that I find hobbles me. I can live with all the others. Well, maybe not with the lack of GroupSymbolizer, but I think I could duplicate its function in the pipeline that generates graphics, and then use prerendered groups in Mapnik. It would probably be easier to implement support for GroupSymbolizer in CartoCSS, but I'm presuming that an attempt at _that_ would also be rejected out of hand.

If I'm correct that you are unalterably opposed to the ideas, I'd rather find out sooner or later, since I don't want to find myself in the position of maintaining a fork. I'd rather spend the effort that I'd put into osm2pgsql on retooling to some other piece of software.

If you're still reading this, I quite agree with you about the 'roads' table. I understand that compatibility will require retaining it, but I hardly use it. If I create indices using statements like:

CREATE INDEX idx_line_primary ON planet_osm_line USING GIST(way) WHERE highway IN ('motorway', 'trunk', 'primary')

I find that I hardly miss it. I deal with routes using the tables that track membership of the individual ways in route relations.

By the way, those tables are tiny by OSM standards - for North America, there are about fifty thousand route=road comprising about a million and a half ways. If I were to replace the 'role' column with a simple flag indicating whether a way is or is not 'role=link', each row in the 1.5-million-row table would be 25 bytes (8 for osm_id of the relation, 8 for osm_id of the way, 4 for position of the way within the relation, 1 for flags). Joining through it is cheap, since PostgreSQL tends just to keep in in memory while rendering is going on. And it's surely not something that would add hours to an import.

[gravitystorm]

Given the constraint that you express that 'the schema as implemented with a minimalistic osm2pgsql import today cannot be expanded in any way in the future', I think you are right that the problem cannot be solved.

I think there's some miscommunication here. Paul was saying that anything like schema changes or extra tables needs to be upstream in osm2pgsql, not done here in this style. ( His link should have been to the relevant issue in the osm2pgsql repo, namely https://github.com/openstreetmap/osm2pgsql/issues/230 )

What he then explained was that it is in fact possible to solve the relation symbolizers without changing how osm2pgsql works, but that it leads to very messy SQL. So it's again highlighting that solving that osm2pgsql issue is the way forward. Please don't assume that fixes provided to osm2psql will be rejected.

It would probably be easier to implement support for GroupSymbolizer in CartoCSS, but I'm presuming that an attempt at that would also be rejected out of hand.

I can say as a CartoCSS maintainer that GroupSymbolizer support would be welcomed.

If I'm correct that you are unalterably opposed to the ideas, I'd rather find out sooner or later, since I don't want to find myself in the position of maintaining a fork. I'd rather spend the effort that I'd put into osm2pgsql on retooling to some other piece of software.

There are hundreds of downstream users of osm2pgsql that would appreciate better route relation handling! So please do consider discussing your ideas on that tracker, and I hope to see your efforts there.

[kennykb]

I will concede I got confused by the fact that @pnorman wears multiple hats - and misinterpreted him as speaking in his rôle as lead developer of osm2pgsql, not strictly as a developer of the OSM Carto style.

[kennykb]

Bolstered by the encouragement from @gravitystorm, I'm floating a formal proposal to develop the necessary _osm2pgsql_ infrastructure to address the problem.

Consider this to be a request, nay, rather, a plea for comments from those who understand the issues. The proposal is most emphatically not in its final form - in particular, there is an Open issues section that describes some requirements that I'm fairly sure exist, but that I don't understand.

I hope that writing up this portion of the issue, as I understand it, demonstrates a good-faith attempt to "do the homework" and will not be dismissed out of hand as yet another ignorant oversimplification of the problem. I recognize that I'm still at an early stage in understanding, but I'm reluctant to move forward without at least some feedback from wiser heads. I'll make every effort to insure that your help will not be a waste of time.

(Oh, quick note - it might be easier to consolidate replies in this ticket.)

[jeisenbe]

The issue openstreetmap/osm2pgsql#230 is now fixed by osm2pgsql PR1037, so it should be possible to implement this.

Is anyone interested in submitting a PR to render the ref from the route relation in addition?

[SomeoneElseOSM]

Is anyone interested in submitting a PR to render the ref from the route relation in addition?

Presumably it'd need more than a change to this map style - it'd also need the way that osm2pgsql is used on osm.org to be invoked differently?

[kennykb]

@jeisenbe I'm following the development of the 'flex' backend eagerly, but my time is severely limited, possibly for the entirety of 2021 (we'll see how things start sorting out later in the year.

I'm considering the rendering of route relations on the main server to be Out Of Scope for me at the present time, owing to the fact that performance is unproven. Paul and Sarah are rightly concerned that reconstruction of concurrencies at render time might be an unacceptable cost. I am surely not tooled to assess the performance at production scale, so without a champion on the team, I'd rather be blocked.

I will confess that I suspended further development of my own pictorial-shield rendering because the performance worries extended to an indication that the functionality, the way I use it, would never be workable using a stock 'osm2pgsql'. I had no desire to maintain a fork, and frankly lacked energy to assess a switch to ImpOSM or another database pipeline.

Could @jeisenbe or another developer of the 'flex' backend revisit the schema I'm using and verify that it would be feasible to

• populate the tables - as specified - on initial import
• update the tables when processing diffs
• generate some sort of log of diffs to drive tile expiry (I'm willing to accept this in any form that's convenient. Processing updates is less time-critical than rendering tiles; I can reconstruct the geometry to determine the affected tiles myself.)

The key structural differences are:

• planet_osm_route lacks a geometry. It has only an OSM ID and tags.
• planet_osm_route_way is a new table that has one row per relation membership. (Ideally, we should cluster this one on planet_osm_route.) It, too, has no geometry. Unlike other tables in the older schema, its rows aren't tied to a single OSM ID; instead, it represents the association between relation and member.

I'm at a point where I critically need a second pair of eyeballs on most of the route rendering stuff. Paul and Sarah have indicated that they consider my current design unacceptable, but have offered only sparse guidance as to what requirements it fails to address or how to move it forward. (I don't expect anything else. They're busy individuals, and I'm not paying them for the work.) I have the sense that something is badly wrong with the ideas, but I've no clue what it is!

[jeisenbe]

We should check if release 1.4.0 of osm2pgsql provides the proper functionality: https://github.com/openstreetmap/osm2pgsql/releases/tag/1.4.0